Events
Microsoft 365 Community Conference
May 6, 2 PM - May 9, 12 AM
Skill up for the era of AI at the ultimate community-led Microsoft 365 event, May 6-8 in Las Vegas.
Learn moreThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
APPLIES TO: 2013
2016
2019
Subscription Edition
SharePoint in Microsoft 365
You can bring data into Excel using lots of data sources and connections. Many, but not all, of the data connections that you can use in Excel are supported in Excel Services. In addition, if your organization is using Office Web Apps Server, some data sources that you can use in Excel are not supported in Excel Web App.
At a high level, the following table summarizes the Excel data sources that are supported in Excel Services (as part of SharePoint Server 2013) and Excel Web App (as part of Office Web Apps Server).
Table: Summary of Excel data sources that are supported in Excel Services and Excel Web App
Excel data source | Supported in Excel Services? | Supported in Excel Web App? |
---|---|---|
SQL Server tables |
Yes |
Yes, if the environment is configured to use Secure Store Service or an unattended service account. |
SQL Server Analysis Services cubes |
Yes |
Yes, if the environment is configured to use Secure Store Service or an unattended service account. |
OLE DB or ODBC data source |
Yes, as long as the connection string contains a user name and password for the connection. |
Yes, as long as the connection string contains a user name and password for the connection. |
Data model that was created by using Excel |
Yes, provided Excel Services is configured to use an instance of SQL Server Analysis Services for data models. |
No |
Azure Marketplace data |
No |
No |
OData data |
No |
No |
XML data |
No |
No |
Access data |
No |
No |
Data from a text file |
No |
No |
The following sections contain more detailed information about how to work with data in Excel and Excel Services.
In this article
In Excel 2016, you can connect to lots of data sources. These include the following:
SQL Server tables
SQL Server Analysis Services cubes
Azure Marketplace data
OData data
XML data
Access data
Text file data
To bring data into Excel, you can use existing connections, or you can create your own connections. Existing connections can be stored on your computer or in a central location, such as a SharePoint site.
Ideally, you'll have access to a set of existing data connections in a site, such as a Business Intelligence Center site, that you can use to work with data in Excel. This is especially helpful if you plan to publish a workbook to a documents library in a Business Intelligence Center site. Typically, a SharePoint admin configures such locations as trusted locations in Excel Services. This makes it possible for you and others to refresh data in your workbooks to view the most current information.
To use an existing data connection in Excel
In Excel, on the Data tab, choose Existing Connections.
Choose Browse for More to open the Select a Data Source dialog.
Specify the location of the data source that you want to use, select the data source, and then choose Open.
On the Import Data page, choose how you want to view the data, and then choose OK.
The options that are available depend on the particular data source that you are using. For example, you might choose to create a data table, a PivotChart report, a PivotTable report, or a Power View sheet.
If you do not have an existing connection or you want to create a new connection, you can easily do this in Excel. You'll typically have to know the name of the location (such as a server or a website) where the data is stored and what authentication method that you should use to connect to the data. For example, to create a connection to a table that is stored in SQL Server, you must know the name of the server, the database, and table that you want to use, and what credentials are used to connect to the data.
To create and use a new data connection in Excel
Choose From Access to use data that is stored in an Access database.
Choose From Web to use data from an internal or external website.
Choose From Text to use data that is stored in a text file.
Choose From Other Sources to use data that is available in SQL Server, SQL Server Analysis Services, Azure Marketplace, OData, an XML file, or data that is available through a custom provider.
The Data Connection Wizard opens.
Specify the information that is required for each step of the Data Connection Wizard, and then click Finish.
On the Import Data page, choose how you want to view the data, and then choose OK.
The options that are available depend on the particular data source that you are using. For example, you might choose to create a data table, a PivotChart report, a PivotTable report, a Power View sheet, or just the data connection.
For more detailed information about how to create data connections, see Share data connections by using Excel and Excel Services (SharePoint Server 2013).
After you have created an Excel workbook that uses external data and views, such as PivotChart reports, PivotTable reports, or Power View views, you can share that workbook with others by using a site such as a SharePoint site. Depending on your environment, the external data connections that are used in your workbook might not be supported. Whether an external data connection is supported determines whether data refresh is available for the workbook and whether the workbook is viewable in a browser window.
Most, but not all, of the data connections that you can use in Excel are supported in Excel Services. These include connections to the following data sources:
SQL Server tables
SQL Server Analysis Services cubes
OLE DB and ODBC data sources
When a data connection is supported, it means that people can refresh data in Excel Services that use that data connection, as long as Excel Services is configured correctly.
Data sources that you can connect to in Excel that are not supported in Excel Services include the Access databases, website content, XML files, Azure Marketplace data, and text files. If you plan to use these kinds of data sources in workbooks that you'll publish to SharePoint Server 2013, consider importing data into Excel and using the data as native data. For more information, see Working with native data in Excel Services.
Office Web Apps Server is the online companion to Office Word, Excel, PowerPoint, and OneNote applications. If your organization is using SharePoint Server 2013 together with Office Web Apps Server, then your organization is using either Excel Services or Excel Web App (part of Office Web Apps Server) to display workbooks in a browser window. This decision affects which data sources are supported for workbooks rendered in a browser window.
Excel Web App supports some, but not all, kinds of secure external data connections. Data connections to Microsoft Access databases, website content, XML files, Azure Marketplace data, and text files are not supported in Excel Web App. If you plan to use these kinds of data sources in workbooks that you'll share using Excel Web App, consider importing data into Excel and using the data as native data.
A data model is a dataset that consists of multiple tables. Data models are useful for bringing together data from different databases to create a single database that can serve as a data source for views, such as PivotChart reports, PivotTable reports, and Power View views. Data models can be created by using external data or native data. For more information about data models, see PowerPivot: Powerful data analysis and data modeling in Excel.
To view or use a workbook that contains a data model in Excel Services, Excel Services must be configured to support data models. For more information, Manage Excel Services data model settings (SharePoint Server 2013). Currently, Office Web Apps Server does not support data models.
Native data is data that is imported into Excel and does not keep connections to external databases. Native data is also known as worksheet data or sheet data, and it is either static data or data that is updated manually. Working with native data in Excel workbooks offers certain advantages:
You can publish workbooks that use data from sources that are not supported in Excel Services or Excel Web App.
You and others can view and interact with workbooks that use native data in a browser window, whether the workbook is rendered by either Office Web Apps Server or SharePoint Server 2013. Note that data models are not supported in Office Web Apps Server, but PivotChart reports and PivotTable reports that use native data are supported.
If a workbook does not consume more than 10 MB of disk space, you can share or view the workbook on Microsoft 365. This makes it possible to share information "in the cloud."
Share data connections by using Excel and Excel Services (SharePoint Server 2013)
Business intelligence capabilities in Excel Service (SharePoint Server 2013)
Events
Microsoft 365 Community Conference
May 6, 2 PM - May 9, 12 AM
Skill up for the era of AI at the ultimate community-led Microsoft 365 event, May 6-8 in Las Vegas.
Learn moreTraining
Module
Transcend data analysis together with Power BI and Excel - Training
This module introduces potential ways that people can use Excel and Power BI together. Power BI is a great tool for telling a story through visuals; occasionally, it's beneficial to use Excel for analysis. Excel can make a connection to a Power BI dataset, enabling you to analyze the data by using PivotTables.
Certification
Microsoft Office Specialist: Excel Associate (Microsoft 365 Apps) - Certifications
Demonstrate that you have the skills needed to get the most out of Excel (Microsoft 365 Apps) by earning a Microsoft Office Specialist (MOS) certification.