Power Pivot Connection Settings: Where to Find Them

In Excel for Microsoft 365 on Windows, find Power Pivot connections by selecting Power Pivot > Manage, then, in the Power Pivot window, choosing Home > Existing Connections. To change a workbook connection’s properties or refresh behavior, open Data > Queries & Connections, select the Connections tab, right-click the connection, and choose Properties; credentials for many Power Query sources are managed separately under Data > Get Data > Data Source Settings.

What a Power Pivot connection controls

A connection tells Excel how to reach data used by a workbook or its Data Model. Depending on the source, it can identify a database, file, server, or other provider; specify what data to retrieve; and define how Excel handles refreshes. A connection is not the same thing as a relationship: a connection brings data into the workbook, while a relationship links tables that are already in the Data Model.

Power Pivot is Excel’s data-modeling feature. It can hold tables from one or more sources, relate those tables, and support PivotTables and other reports. The connection settings matter when the source moves, access requirements change, a refresh fails, or the workbook should refresh at a particular time. They can also affect whether a workbook asks for credentials or attempts to access a source automatically.

Not every workbook exposes every setting. What you can edit depends on the connection type, the source provider, how the data was imported, and your access to the source. A connection created for the built-in workbook Data Model, for example, may have properties that are restricted or not useful to edit directly.

Open and inspect existing connections

To view the connections associated with the model, open the Power Pivot window from the Power Pivot tab in Excel and select Manage. In that window, use Home > Existing Connections to see connections available to the workbook. This is a useful place to inspect an existing source or choose a connection when adding data to the model.

For workbook-level connection properties, open Data > Queries & Connections. In the pane, select Connections, then right-click the connection you want to inspect and choose Properties. Depending on the Excel ribbon layout, you may also find a Connections command on the Data tab that opens the Workbook Connections dialog. From there, select a connection and choose Properties.

Before editing, identify the connection by its name and source. A workbook may contain several connections, including ones used by separate model tables, PivotTables, queries, or external data ranges. Changing a connection that looks similar to another can affect a different report than intended. If you are unsure, note the connection name and inspect its properties before making changes.

Understand the Connection Properties tabs

The Connection Properties dialog commonly has Usage and Definition tabs. The Usage tab contains options related to when and how Excel refreshes the connection. Depending on the connection, you may see options to refresh data when the workbook opens, refresh at a set interval, allow background refresh, or remove data from the external data range before saving. Some options appear only for compatible connection types.

The Definition tab describes the connection itself. It may show a connection file, connection string, command type, or command text. A connection string can contain technical details such as a server and database name. Command text may define a query or object to retrieve. These fields are provider-dependent, and Excel may disable editing for some connections or show only part of the definition.

Do not change unfamiliar connection strings or command text just to try to fix a refresh. A typo in a server name, database, table, or query can break the connection; changing a command can also change which records are loaded. If the source is maintained by an administrator, obtain the correct source details from that person rather than guessing.

Change refresh behavior safely

Refresh settings determine when Excel requests updated data. If the workbook should retrieve new data when someone opens it, enable the relevant refresh-on-open option in the connection’s Usage properties, if that option is available. For a workbook that remains open while source data changes, a timed refresh may be appropriate. Use an interval that suits the source and reporting need rather than refreshing so often that it creates unnecessary server traffic.

Background refresh can let Excel continue responding while a query runs, but it can also mean that a report is viewed before its refresh has finished. For a process that must wait for fresh data before proceeding, background refresh may not be suitable. The available behavior varies by source and connection type, so check how the workbook behaves after changing the option.

A refresh of a model connection is different from refreshing the visible PivotTable layout. A PivotTable can display cached model data even when its source connection has not recently refreshed. Conversely, refreshing a connection may update the model without changing the report’s formatting or field arrangement. When accuracy matters, refresh the source data and then check that the PivotTable reflects the updated model.

To refresh manually, use Data > Refresh All to refresh workbook connections that support it. You can also right-click a PivotTable and choose Refresh to update that report. Refresh All is broader, while the PivotTable command targets the selected report. In Excel for Mac, the Data tab and refresh commands are available, but Power Pivot model authoring and its connection-management window are not available in the same way as in Excel for Windows.

Manage credentials and data-source permissions

Connection properties and saved credentials are related, but they are not always managed in the same place. For many sources created through Power Query, select Data > Get Data > Data Source Settings. The dialog can list sources used in the current workbook and, in some cases, global sources used by Excel. Select the relevant source to review or edit permissions, or clear permissions so Excel can prompt again the next time it connects.

Changing a password in a connection string is not a good substitute for updating source permissions. Modern authentication methods, organizational policies, and provider-specific sign-in flows may store or request credentials outside the visible connection definition. If a source uses organizational sign-in, follow the sign-in prompt or your organization’s approved access process. Avoid embedding passwords in a workbook connection: anyone able to inspect the workbook may be able to expose sensitive connection details.

Data Source Settings primarily applies to Power Query data sources. Older connections or other provider types may instead use the connection’s own properties or a separate authentication mechanism. If a source is not listed there, that does not necessarily mean the workbook has no connection; inspect the connection in Queries & Connections and consult the source provider’s instructions.

Power Query, Power Pivot, and connection settings

Power Query and Power Pivot work together, but they have different jobs. Power Query connects to sources and can clean, reshape, and load data. Power Pivot stores and relates tables in the Data Model and supports calculations and analysis. A query loaded to the Data Model may therefore have both query settings and model-related connection behavior.

For example, suppose a workbook imports a sales table from a database through Power Query and loads it to the Data Model. To change how the query connects or how its source credentials are handled, inspect the query and Data Source Settings. To inspect a workbook connection’s refresh properties, use Queries & Connections and its Properties dialog. To examine the model and its tables, use Power Pivot > Manage. These are connected parts of one workflow, not interchangeable control panels.

When troubleshooting, start at the layer that matches the symptom:

If the source path, server, or sign-in is wrong, inspect the source connection or its permissions.
If the data imports but needs a different shape, inspect the Power Query steps.
If the tables load but do not filter or summarize as expected, inspect model relationships and PivotTable fields.
If the report shows old values, verify that the relevant connection refreshed and then refresh or check the report.

Common connection problems and practical checks

A common failure occurs after a file or database is moved. The workbook may still point to the original path or server. Check the connection’s source details, update them to the new approved location, and refresh. For a file-based source, confirm that the person opening the workbook can access the path; a local path that works on one computer may not work for colleagues.

Another frequent problem is an authentication prompt or access-denied message. Confirm that the signed-in account has permission to the source, then update or clear the relevant source permissions if appropriate. A workbook owner’s access does not guarantee that every recipient has access. Sharing the workbook does not automatically share database permissions or credentials.

If refresh fails after a source change, check the exact error before editing settings. It may indicate that the source is unavailable, a table or column was renamed, a query no longer matches the source, or a credential expired. A renamed column can break later Power Query steps even when the connection itself succeeds. In that case, the fix belongs in the query, not necessarily in the connection properties.

For a workbook with many connections, make a copy before making substantial changes and record the original settings. Then change one connection at a time and use Refresh to verify the result. This makes it easier to identify which change caused a problem and provides a straightforward way to return to the original workbook if necessary.

Availability and version differences

Power Pivot’s full modeling tools are intended for Excel on Windows and are not provided as an equivalent model-management interface in Excel for the web or Excel for Mac. On Windows, availability can depend on the Microsoft 365 or Office edition and organizational configuration. If the Power Pivot tab is missing, check File > Options > Add-ins. At the bottom, set Manage to COM Add-ins, select Go, and see whether Microsoft Power Pivot for Excel is available to enable. If it is absent or disabled by an administrator, the edition or installation may not provide access.

Excel for the web can display many workbooks and may support some data refresh scenarios, but it does not offer the same Power Pivot window for editing the model and its connections. Mac versions have their own data-import and refresh capabilities, but do not follow the Windows Power Pivot instructions above for model connection management. If a workbook must be maintained on Mac or in a browser, confirm that the specific connection type and required refresh actions are supported there before relying on them.

Frequently Asked Questions

Where are Power Pivot connection settings in Excel?
For model connections, open Power Pivot > Manage, then Home > Existing Connections. For workbook connection properties, open Data > Queries & Connections, select Connections, right-click a connection, and choose Properties.

How do I change a Power Pivot connection’s password?
For many Power Query sources, use Data > Get Data > Data Source Settings and edit or clear the source permissions. Other connection types may use a provider-specific sign-in or connection properties. Do not assume every password is stored in the visible connection string.

Why can’t I edit a connection in Excel?
The source provider or connection type may make some properties read-only, or the connection may be generated for the workbook Data Model. You may also lack permission to change the source. Inspect the query and source settings as well as the connection dialog.

Does refreshing a PivotTable refresh Power Pivot data?
A PivotTable refresh and a source-connection refresh are related but not always identical. Use Data > Refresh All when you need to refresh workbook connections, then verify the PivotTable. A PivotTable’s Refresh command targets the selected report.

Can I edit Power Pivot connections in Excel for Mac or the web?
Excel for Mac and Excel for the web do not provide the same Power Pivot model-management window as Excel for Windows. Some data operations may be possible, but use a supported Windows version when you need the full Power Pivot connection and model tools.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.