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.

In this article

What is external data?

Data sources that are supported in Excel Services

Excel Web App and Excel Services

Determining whether Excel Services or Excel Web App is used to render a workbook

External data sources that are supported in Excel Services and Excel Web App

Refreshing data and editing workbooks in a browser window

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

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

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

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

Applies To: Excel Online, SharePoint Server 2013 Enterprise



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language