Connect external data to your workbook

Applies To: Excel 2016 , Excel 2013

Sometimes, the data you want isn't in your Excel workbook – it might be in a database, an XML file, on the web, in a text file, or in one of many other places. It would be great to work with that data right in your workbook, but you'd rather not have to import it again and again to make sure it's current. Creating a permanent connection from your workbook to that data source makes it easy to ensure the data you work with is always up to date.

Important: Connections to external data might be disabled on your computer. To connect to data when you open a workbook, you must enable data connections by using the Trust Center bar, or by putting the workbook in a trusted location.

In the Get External Data group on the Data tab, you can create connections with various data sources:

From Access

Connect an Access database to your workbook to use data from its tables and queries in your workbook.

From Web

Create a connection to a webpage to use data from its tables in your workbook.

  1. On the Data tab, click From Web.
  2. In the Address box, enter the web address of the site with the data you want to connect to.
  3. An arrow indicator like this means there's data nearby you can import.
  4. Click the arrow indicator, click the Import button, and then click OK.

Web connections you create will be named "Connection," "Connection1," and so on. It's a good idea to rename each connection to a name that better describes it. To do this:

  1. On the Data tab, click Connections.
  2. Choose the connection you want to rename, and click Properties.
  3. In the Connection name box, enter the name you want, and click OK.

From Text

Create a connection to a text file and you can always work with its latest data. The Text Import Wizard helps you make sure all the data ends up in the right columns.

  1. On the Data tab, click From Text.
  2. Browse to your text file, and click Open.
  3. Follow the steps in the Text Import Wizard, and click Finish.

From Other Sources

Other data sources you can connect to include:

From SQL Server

Connect a SQL Server database to your workbook to import data into Excel as a table, PivotTable report, or PivotChart.

From Analysis Services

Create a connection to a SQL Server Analysis Services cube to import data into Excel as a table, PivotTable report, or PivotChart.

  1. On the Data tab, click From Other SourcesFrom Analysis Services.

  1. In the Data Connection Wizard, enter the server name and logon information.
  2. Choose the database you want to work with, and then choose a cube, perspective, or table.
  3. Click Finish, and then choose where you want to import the data.

From Microsoft Azure Marketplace

Connect a Microsoft Azure DataMarket feed to your workbook

From OData Data Feed

Create a connection to an Open Data Protocol (OData) data feed to import data into Excel as a table, PivotTable report, or PivotChart.

  1. On the Data tab, click From Other SourcesFrom OData Data Feed.
  2. Enter the link or file that contains the data feed you want, and any logon credentials that the feed requires.
  3. Follow the directions in the wizard to finish.

From XML Data Import

  1. On the Data tab, click From Other SourcesFrom XML Import.
  2. Browse to your XML file, and click Open.

If the XML file doesn't refer to a schema, Excel offers to create a schema for you.

  1. Click OK.
  2. Choose where you want to import the data.

From Data Connection Wizard

The Data Connection Wizard has additional data sources you can connect to:

On the Data tab, click Get External DataFrom Other SourcesData Connection Wizard.

  • Choose ODBC DSN and follow the steps in the wizard to connect to a dBASE file, another Excel workbook, an Access database, or a Visio database sample. See Connect data in another workbook to your workbook.
  • Choose Microsoft Data Access – OLE DB Provider for Oracle to connect to an Oracle Database server. Enter the server name and logon credentials to connect to the database. See Connect OLE DB data to your workbook.
  • Choose Other/Advanced to connect to a number of different OLE DB provider types, including Microsoft Jet, Office 12.0 Access Database Engine, Analysis Services, Indexing Services, ODBC drivers, and more. Enter the connection properties, such as the server name or data source, and any required logon credentials.

From Microsoft Query

Use the Query Wizard to create queries to connect to data sources such as tables and queries in database files, Excel files, or Visio database samples. Choose your data source and then follow the steps in the wizard.