You can use Excel to create and edit connections to external data sources that are stored in a workbook or in a connection file. You can easily manage these connections, including creating, editing, and deleting them using the current Queries & Connections pane or the Workbook Connections dialog box (available in previous versions).
: Connections to external data may be currently 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. For more information, see Add, remove, or modify a trusted location for your files, Add, remove, or view a trusted publisher, and View my options and settings in the Trust Center.
Data in an Excel workbook can come from two different locations. The data may be stored directly in the workbook, or it may be stored in an external data source, such as a text file, a database, or an Online Analytical Processing (OLAP) cube. The external data source is connected to the workbook through a data connection, which is a set of information that describes how to locate, log in, query, and access the external data source.
When you are connected to an external data source, you can also perform a refresh operation to retrieve the updated data. Each time that you refresh data, you see the most recent version of the data, including any changes that were made to the data since it was last refreshed.
Connection information can either be stored in the workbook or in a connection file, such as an Office Data Connection (ODC) file (.odc) or a Universal Data Connection (UDC) file (.udcx). Connection files are particularly useful for sharing connections on a consistent basis and for facilitating data source administration.
If you use a connection file to connect to a data source, Excel copies the connection information from the connection file into the Excel workbook. When you make changes by using the Connection Properties dialog box, you are editing the data connection information that is stored in the current Excel workbook, and not the original data connection file that may have been used to create the connection, indicated by the file name that is displayed in the Connection File property. Once you edit the connection information (with the exception of the Connection Name and Connection Description properties), the link to the connection file is removed and the Connection File property is cleared.
By using the Connection Properties dialog box or the Data Connection Wizard, you can use Excel to create an Office Data Connection (ODC) file (.odc). For more information, see Connection properties and Share data with ODC.
-
Do one of the following:
-
Create a new connection to the data source. For more information, see Move data from Excel to Access, Import or export text files, or Connect to SQL Server Analysis Services Database (Import).
-
Use an existing connection. For more information, see Connect to (Import) external data.
-
-
Save the connection information to a connection file by clicking Export Connection File on the Definition tab of the Connection Properties dialog box to display the File Save dialog box, and then save the current connection information to an ODC file.
Note The Queries & Connections pane is available in Microsoft Office 365 for Excel and Excel stand-alone version 2019 or later. It replaced the Workbook Connections dialog box which is available in desktop versions of Excel.
The Queries & Connections pane (Select Data > Queries & Connections) In one location, you can get to all the information and commands you need to work with your external data. This pane has two tabs:
-
Queries Displays all the queries in the workbook. Right click a query to see available commands. For more information, see Manage queries.
-
Connections Displays all the connections in the workbook. Right click a connection to see available commands. For more information, see Connection properties.
Note The Workbook Connections dialog box is available in desktop versions of Excel but was replaced in Microsoft Office 365 for Excel and Excel 2019 with the Queries & Connections pane.
The Workbook Connections dialog box (Select Data > Connections) helps you manage one or more connections to external data sources in your workbook.
You can use this dialog box to do the following:
-
Create, edit, refresh, and delete connections that are in use in the workbook.
-
Verify where external data is coming from, because, for example, the connection was defined by another user.
-
Show where each connection is used in the current workbook.
-
Diagnose an error message about connections to external data.
-
Redirect a connection to a different server or data source, or replace the connection file for an existing connection.
-
Display the Existing Connections dialog box to create new connections. For more information, see Connect to (Import) external data.
-
Display the Connection Properties dialog box to modify data connection properties, edit queries, and change parameters. For more information, see Connection properties.
-
Make it easy to create and share connection files with users.
To manage the connections in the current workbook, do one or more of the following:
Identify a connection
In the top portion of the dialog box, all connections in the workbook are displayed automatically with the following information:
|
Column |
Comment |
|---|---|
|
Name |
The name of the connection, defined in the Connection Properties dialog box. |
|
Description |
An optional description of the connection, defined in the Connection Properties dialog box. |
|
Last refreshed |
The date and time that the connection was last successfully refreshed. If blank, then the connection has never been refreshed. |
Add a connection
-
Click Add to display the Existing Connections dialog box. For more information, see Connect to (Import) external data.
Display connection information
-
Select a connection, and then click Properties to display the Connection Properties dialog box. For more information, see Connection properties.
Refresh the external data
-
Click the arrow next to Refresh, and then do one of the following:
-
To refresh specific connections, select one or more connections, and then click Refresh.
-
To refresh all connections in the workbook, clear all connections, and then click Refresh All.
-
To get status information about a refresh operation, select one or more connections, and then click Refresh Status.
-
To stop the current refresh operation, click Cancel Refresh.
-
For more information, see Refresh an external data connection in Excel.
Remove one or more connections
-
Select one or more connections to be removed from the workbook, and then click Remove.
:
-
This button is disabled when the workbook is protected or an object, such as a PivotTable report, that uses the connection is protected.
-
Removing a connection only removes the connection and does not remove any object or data from the workbook.
-
: Removing a connection breaks the connection to the data source and may cause unintended consequences, such as different formula results and possible problems with other Excel features.
Display the locations of one or more connections in the workbook
-
Select one or more connections, and then under Locations where connections are used in this workbook, click the link Click here to see where the selected connections are used.
The following information is displayed.
|
Column |
Comment |
|---|---|
|
Sheet |
The worksheet where the connection is used. |
|
Name |
The Excel query name. |
|
Location |
The reference to a cell, range, or object. |
|
Value |
The value of a cell, or blank for a range of cells. |
|
Formula |
The formula of a cell, or for a range of cells. |
Selecting another connection at the top of the dialog box clears the display of the current information.
