Note:Â This article assumes that you are using the version of Excel that comes with Office Professional Plus. Excel Starter and the version of Excel that comes with the Home and Student editions of Office do not support external data connectivity as described in this article.
The information in this article applies to Excel Services in SharePoint Server 2013 (on premises) only. For information about Microsoft 365 (in the cloud), see Use external data in workbooks in SharePoint Online.
You can work with a wide variety of data sources in Excel, including native data and external data. Some, but not all, of the external data sources that you can use in Excel are supported in Excel Services. Read this article to get an overview of what’s supported in Excel Services when you publish a workbook to a SharePoint library.
In this article
What is external data?
External data is data that is stored someplace else, such as on a server. You can import or display external data in a workbook by using one or more external data connections. Examples of external data sources can include SQL Server tables, SQL Server Analysis Services cubes, Microsoft Azure Marketplace data, and so on. External data connections that are used in a workbook make it possible to send queries and receive data from the databases that are specified in the connections. This enables you to refresh the data to view the most current information in a workbook.
As an alternative to working with external data, you can use native data in Excel. Native data is stored directly in the workbook, and does not require an external data connection to be retained, even if an external connection was used to import data into a workbook. To update native data, you can manually type updates or reimport data into Excel.
Data sources that are supported in Excel Services
Some, but not all, of the data sources that you can use in Excel are supported in Excel Services in SharePoint Server 2013. When a data source is supported in Excel Services, it means that you and other users are able to refresh the data that is displayed in a workbook. Excel Services in SharePoint Server 2013 supports the following kinds of data sources:
-
SQL Server tables
-
SQL Server Analysis Services cubes
-
OLE DB or ODBC data sources
-
Data models, such as those that are created in Excel
For more information, contact a SharePoint administrator or see TechNet Article: Data sources supported in Excel Services (SharePoint Server 2013).
Excel Web App and Excel Services
If your organization is using Office Web Apps alongside SharePoint Server 2013, then either Excel Services (part of SharePoint Server 2013) or Excel Web App (part of Office Web Apps Server) is used to render workbooks in a browser window. This is a decision that SharePoint administrators make, and it can affect which data sources are supported when you are using a workbook in a browser window. For more information, see Comparing Excel Online, Excel Services, and Excel Web App.
Determining whether Excel Services or Excel Web App is used to render a workbook
You might be wondering how to tell if you are using Excel Services or Excel Web App when you view a workbook in a browser window. One way to do this is to examine the website address (URL) for the workbook.
-
Look for xlviewer in the URL. If the URL resembles http://[servername]_layouts/15/xlviewer.aspx?id=/Documents/..., then Excel Services is used to render the workbook.
-
Look for WopiFrame in the URL. If the URL resembles http://[servername]/_layouts/15/WopiFrame2.aspx?sourcedoc=/Documents/..., then Excel Web App is used to render the workbook.
You can also contact your SharePoint administrator for help with information about how your environment is configured.
External data sources that are supported in Excel Services and Excel Web App
The following table summarizes the kinds of data connections that you can use in Excel and which data connections are supported in Excel Services and in Excel Web App.
|
Excel Data Source |
Supported in Excel Services (SharePoint Server)? |
Supported in Excel Web App (Office Web Apps Server)? |
|
SQL Server tables |
Yes |
Yes, if the environment includes SharePoint Server 2013 and it is configured to use Secure Store Service or an unattended service account. Contact a SharePoint administrator for more information. |
|
SQL Server Analysis Services cubes |
Yes |
Yes, if the environment includes SharePoint Server 2013 and it is configured to use Secure Store Service or an unattended service account. Contact a SharePoint administrator for more information. |
|
OLE DB or ODBC data sources |
Yes, if each connection’s string contains a user name and password. Contact a SharePoint administrator for more information. |
Yes, if each connection’s string contains a user name and password. Contact a SharePoint administrator for more information. |
|
Data model that was created by using Excel |
Yes, if Excel Services is configured to support data models. Contact a SharePoint administrator for more information. |
No |
|
Microsoft Azure Marketplace data |
No |
No |
|
OData data |
No |
No |
|
XML data |
No |
No |
|
Microsoft Access data |
No |
No |
|
Data from a text file |
No |
No |
Refreshing data and editing workbooks in a browser window
When you view a workbook in a browser window, whether you can edit the workbook or refresh the data in a browser window depends on how your environment is configured. The following table summarizes which on-premises environments support data refresh and editing capabilities in a browser window.
|
Configuration |
Refresh data in a browser window |
Edit the workbook in a browser window |
|
SharePoint Server 2013 (Excel Services renders workbooks) |
Yes, if you are using one or more of the following kinds of data sources:
|
No. To edit the workbook, you must open it in Excel. |
|
Office Web Apps Server(Excel Web App renders workbooks) |
No, unless you are using an anonymous connection, an OLE DB or ODBC data connection, or the connection string contains a user name and password. |
Yes |
|
SharePoint Server 2013 together with Office Web Apps Server (Excel Services is used to render workbooks) |
It depends on the data sources. See Data sources supported in Excel Services |
Yes |
|
SharePoint Server 2013 together with Office Web Apps Server(Excel Web App is used to render workbooks) |
It depends on the data sources and how the environment is configured. See External data sources supported in Excel Services |
Yes |
