Use external data in workbooks in SharePoint Online

 Note    The information in this article applies to SharePoint Online (part of Office 365) only. If you’re looking for information about SharePoint Server 2013 (on premises), see Work with external data in Excel Services (SharePoint Server).

Suppose that you want to share a workbook that brings in data that resides somewhere else, such as on a server. You want to be able to view and refresh the data in the workbook in a browser window, and you want other people to be able to do those things as well. You can easily create and share workbooks that people can view and refresh in a browser window. Just make sure the workbook contains a Data Model that uses data sources that are supported in SharePoint Online.

Use data sources that are supported in SharePoint Online

Some—but not all—of the data sources that you can use in Excel are supported in SharePoint Online. When a data source is supported, it means that you can typically refresh the data in the workbook in a browser window. You can use the following kinds of data sources in workbooks in SharePoint Online:

  • Microsoft Azure SQL Database tables
  • SharePoint lists that are available as OData feeds
  • Microsoft Azure Marketplace data
  • External data that is available through Business Connectivity Services with Secure Store Service (contact an administrator for help with this)
  • SQL Server data that is available in the Power BI Admin Center (this requires a subscription to Power BI for Office 365 and an administrator to configure the connection)

Work with a Data Model

In Excel, you can connect to an external data source and use that data connection to create charts, tables, and views. You can use external data in a Data Model—or not. But, if you want to share the workbook with other people in Office 365, make sure to add the data to the Data Model in Excel. That way, you’ll have an efficient workbook that contains a data source that you can use to create all kinds of views, including Power View sheets. For more information, see Create a Data Model in Excel.

Get more details

More detailed documentation is available on TechNet. See TechNet Article: Data sources supported for workbooks in SharePoint Online

 
 
Applies to:
Excel Online, SharePoint admin center, SharePoint Online Enterprise (E1), SharePoint Online Enterprise (E3 & E4), SharePoint Online Midsized Business, SharePoint Online operated by 21Vianet - Enterprise (E3 & E4), SharePoint Online Small Business