Use the Connection Properties dialog box to control various settings for connections to external data sources, and to use, reuse, or switch connection files. This dialog box is often titled Query Properties when Power Query has been used to import the external data source.
Important: 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 the following articles: 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.
Connection name and Description boxes These display the connection name and an optional description. To change the name and description, click the text in the box, and then edit the text.
Important Avoid renaming the query or the connection. You risk losing the link to the external ODC file or references to the Data Model table in a query.
The settings on the Usage tab control the way that the connection information is used in the workbook.
Refresh control:
Enable background refresh Select this check box to run the query in the background. Clear this check box to run the query while you wait. Running a query in the background enables you to use Excel while the query runs.
Refresh every n minutes Select this check box to enable automatic external data refresh at regular time intervals, and then enter the number of minutes between each refresh operation. Clear this check box to disable automatic external data refresh.
Refresh data when opening the file Select this check box to automatically refresh external data when you open the workbook. Clear this check box to immediately open the workbook without refreshing external data.
Remove data from the external data range before saving the workbook Select this check box if you want to save the workbook with the query definition but without the external data. Clear this check box to save the query definition and data with the workbook. This check box becomes available only after you select the Refresh data when opening the file check box.
OLAP Server Formatting:
Controls whether the following OLAP server formats are retrieved from the server and are displayed with the data.
Number Format Select or clear this check box to enable or disable number formatting, such as currency, date, or time.
Font Style Select or clear this check box to enable or disable font styles, such as bold, italic, underline, or strike-through.
Fill Color Select or clear this check box to enable or disable fill colors.
Text Color Select or clear this check box to enable or disable text colors.
OLAP Drill Through:
Maximum number of records to retrieve Enter a number from 1 to 10,000 to specify the maximum number of records to retrieve when you expand a level of data in a hierarchy.
Language:
Retrieve data and errors in the Office display language when available Select or clear this check box to enable or disable the retrieval of translated data and errors, if any, from the OLAP server.
The settings on the Definition tab control how the connection information is defined and the source of the connection information, either the workbook or a connection file.
Connection type Displays the type of connection that is used, such as Office Data Connection or Microsoft Access Database.
Connection file Displays the current connection file that is used to store the connection information and enables switching to a revised or new connection file. If this field is blank, a connection file was never used, or it was used and then modified so that the link to the connection file was broken.
To re-establish the link to the connection file, for example because it was updated and you want to use the new version, or to change the current connection and use a different connection file, click Browse, which displays the Select Data Source dialog box. You can then select the revised connection file, a different connection file, or create a new connection file by clicking New Source, which starts the Data Connection Wizard.
Make sure that the new connection file is consistent with the object that has the data connection. You can change the connection, but you cannot switch connections between the following objects:
-
An OLAP PivotTable or PivotChart report
-
A non-OLAP PivotTable or PivotChart report
-
An Excel table
-
A text file
-
An XML table
-
A Web query to a Web page
Always use connection file Select this check box to ensure that the most up-to-date version of the connection file is always used whenever the data is displayed or refreshed. Clear this check box to use the connection information in the Excel workbook.
Important: If the connection file is not available, Excel resorts to the connection information that is saved in the workbook. If you want to ensure that the most up-to-date version of the connection file is always used, make sure that the connection file is accessible and available.
Connection string Displays the current connection information in the form of a connection string. Use a connection string to verify all of the connection information and to edit specific connection information that you cannot change through the Connection Properties dialog box.
Save password Select this check box to save the username and password in the connection file. The saved password is not encrypted. Clear this check box to log on to the data source, if a user name and password are required, the first time that you access it. This check box does not apply to data retrieved from a text file or a Web query.
Important: Avoid saving logon information when connecting to data sources. This information may be stored as plain text, and a malicious user could access the information to compromise the security of the data source.
Command type Select one of the following command types:
-
SQL
-
Table
-
Default
If the connection is to an OLAP data source, Cube is displayed, but you cannot change the command type.
Command text Specifies the data returned based on the command type. For example, if the command type is Table, the table name is displayed. If the command type is SQL, the SQL query used to specify the data returned is displayed. To change the command text, click the text in the box and then edit the text.
Excel Services Click the Authentication Settings button to display the Excel Services Authentication Settings dialog box and to choose a method of authentication when you access the data source that is connected to a workbook and that is displayed in Excel Services. Select one of the following options to log on to the data source:
-
Windows Authentication Select this option to use the Windows user name and password of the current user. This is the most secure method, but it can impact performance when there are many users.
-
SSS Select this option to use Secure Storage Service, and then enter the appropriate identification string in the SSS ID text box. A site administrator can configure a SharePoint site to use a Secure Storage Service database where a user name and password can be stored. This method can be the most efficient when there are many users.
-
None Select this option to use information saved in the connection string for authentication, for example, when you select the Save Password check box.
Important: Avoid saving logon information when connecting to data sources. This information may be stored as plain text, and a malicious user could access the information to compromise the security of the data source.
Note: The authentication setting is used only by Microsoft Excel Services, and not by the Excel desktop program. If you want to ensure that the same data is accessed whether you open the workbook in Excel or Excel Services, make sure that the authentication settings for the connection are the same in Excel and Excel Services.
Edit Query Click this button to change the query that is used to retrieve data from the data source. Depending on the type of data source, displays one of the following:
-
The Power Query Editor for any imported data source.
-
The Data Connection Wizard for an Office Data Connection (ODC) file (.odc) OLEDB connection.
-
Microsoft Query for an ODBC connection.
-
The Edit Web Query dialog box for a Web page.
-
The Text Import Wizard for a text file.
Note: You cannot edit the query if the connection information is currently linked to a connection file.
Parameters Click this button to display the Parameters dialog box and to edit parameter information for a Microsoft Query or Web Query connection.
Export Connection File Click this button to display the File Save dialog box and to save the current connection information to a connection file. For more information, see Create, edit, and manage connections to external data.
Displays information about queries used in the workbook. If the query and connection information comes from an ODC file, nothing is displayed.
Sheet The name of the worksheet that has the query.
Name The name of the query.
Location The cell range of the query.
