Work with external data in Excel Services (SharePoint Server)

 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 Office 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 (SharePoint library: A library is a special type of SharePoint list that stores files as well as information about files. You can control how files are viewed, tracked, managed, and created in libraries.).

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.

Top of Page Top of Page

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).

Top of Page Top of Page

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

Top of Page Top of Page

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:

  • SQL Server table
  • SQL Server Analysis Services cube
  • OLE DB or ODBC data connection
  • Data model
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

Top of Page Top of Page

 
 
Applies to:
Excel Online, SharePoint Server 2013 Enterprise