Connect to import external data

The main benefit of connecting to external data is that you can periodically analyze this data in Microsoft Office Excel without repeatedly copying the data, which is an operation that can be time-consuming and error-prone. After connecting to external data, you can also automatically refresh (or update) your Excel workbooks from the original data source whenever the data source is updated with new information.

Important: Connections to external data may 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. For more information, see Add, remove, or view a trusted publisher, Trusted documents, and View my options and settings in the Microsoft Office Trust Center.

To open the Existing Connections dialog box, select Data > Existing Connections. You can display all the connections available to you and Excel tables in your workbook. You can open a connection or table from the list and then use the Import Data dialog box to decide how you want to import the data. 

Note: You can also open the Existing Connections dialog box from the Change PivotTable Data Source dialog box. For more information about changing the data source for a PivotTable, see Change the source data for a PivotTable.

1. In the Show drop-down list, do one of the following:

   All    To display all connections, click All Connections. This is selected by default.
   
   Workbook    To display only the recently used list of connections, click Connections in this Workbook.
   
   This list is created from connections that you have already defined, that you have created by using the Select Data Source dialog box of the Data Connection Wizard, or that you have previously selected as a connection from this dialog box.
   
   Computer    To display only the connections that are available on your computer, select Connection files on this computer.
   
   This list is created from the My Data Sources folder that is usually stored in the My Documents folder on your computer.
   
   Network    To display only the connections that are available from a connection file that is accessible from the network, select Connection files on the Network. This list is created from an Excel Data Connection Library (DCL) on a Microsoft SharePoint Services site. A DCL is a document library in a SharePoint Services site that contains a collection of Office Data Connection (ODC) files (.odc). Typically, a DCL is set up by a site administrator, who can also configure the SharePoint site to display ODC files from this DCL in the External Connections dialog box. For more information, see SharePoint Services Central Administration Help.

2. Select the connection that you want, and then select Open. The Import Data dialog box appears.

3. Decide how you want to import the data, and then select OK. For more information about using this dialog box, select the question mark (?).

Tip: If you do not see the connection that you want, you can create a connection. Click Browse for More, and then in the Select Data Source dialog box, click New Source to start the Data Connection Wizard so that you can select the data source that you want to connect to.

Note: If you choose a connection from the Connection files on the network or Connection files on this computer categories, the connection file is copied into the workbook as a new workbook connection, and is then used as the new connection information.

1. In the Show drop-down list, do one of the following:

   All    To display all connections, click Workbook Tables. This is selected by default.
   
   Connections    To display tables defined only by a connection select Connections Tables.
   
   This list is created from connections that you have already defined, that you have created by using the Select Data Source dialog box of the Data Connection Wizard, or that you have previously selected as a connection from this dialog box.
   
   Workbook    To display a table in the workbook select Workbook Tables.

2. Select the connection that you want, and then select Open. The Import Data dialog box appears.

3. Decide how you want to import the data, and then select OK. For more information about using this dialog box, select the question mark (?).

See Also

Power Query for Excel Help