Excel PivotTable Data Source: How to Find It

To see a PivotTable’s data source in Excel for Microsoft 365 on Windows, click anywhere in the PivotTable, then choose PivotTable Analyze > Change Data Source > Change Data Source. The dialog shows the source range or table name when the PivotTable uses worksheet data; for some external or Data Model PivotTables, the source is managed through a connection or query instead.

Find the source with the Change Data Source command

Use this quick sequence to inspect a PivotTable without changing its source:

Click a cell inside the PivotTable. Excel displays the contextual PivotTable tabs on the ribbon.
Choose PivotTable Analyze > Change Data Source > Change Data Source.
Read the entry in the Table/Range box. It may be a worksheet address, such as Sales!$A$1:$F$500, or the name of an Excel Table, such as SalesData.
When you have finished inspecting it, choose Cancel to close the dialog without applying a change.

The command is contextual: if a cell outside the PivotTable is selected, the PivotTable Analyze tab may not appear. If your ribbon is collapsed, expand it or use the ribbon search box to look for Change Data Source. Avoid typing into the dialog just to test what happens; confirming a changed reference can repoint the PivotTable.

What the source dialog tells you

For a PivotTable built from a worksheet range, the dialog usually identifies the sheet and the cells Excel uses as its source. The range includes the header row, so a reference such as Orders!$A$1:$E$240 indicates that Excel is reading five columns and 240 rows from that sheet. Check that the source sheet and boundary make sense before you refresh or edit the PivotTable.

If the source is an Excel Table, the box can show the table’s name rather than a fixed set of cell addresses. That is useful because a table can expand when new rows are added directly beneath it or entered as part of the table. A normal range reference, by contrast, may keep its original boundaries when rows are appended elsewhere on the sheet. Seeing a source reference does not tell you whether every source row is complete or correctly formatted; it tells you what Excel has been directed to use.

The dialog does not display the PivotTable’s entire history or all the transformations that may have produced the data. It identifies the current source reference that Excel exposes for that PivotTable. If that reference is a named table, query, or connection, you may need to inspect that object separately to understand where its records originated.

Understand ranges, Excel Tables, and named sources

A worksheet range is a rectangular block. For example, a PivotTable might use columns for date, region, product, and sales in a range ending at row 500. If records are added in row 501 but the source still ends at row 500, refreshing the PivotTable will not bring in those new records. In that situation, inspect the source reference and extend it deliberately, or convert the data to an Excel Table so that the source can grow with the data.

Tables are often a better source for data that changes regularly, provided the data is organized as one header row followed by consistent records. To check a table’s name, click inside the source table and look for the Table Design tab; the Table Name box identifies it. Do not confuse the table’s name with the PivotTable’s name: they are separate objects, and changing one does not automatically rename the other.

A named range can also be used as a source in some workbooks. If the dialog shows a name you do not recognize, check Formulas > Name Manager to see what cells the name refers to. A defined name may point to a fixed range or, in some workbooks, use a formula to define a changing range. Inspecting the name clarifies the address, but be careful not to edit it unless you intend to affect every workbook feature that uses it.

When the PivotTable uses a query or external connection

Some PivotTables are based on an external database, a workbook connection, a Power Query result, or the Excel Data Model rather than a simple range on a worksheet. In those cases, the Change Data Source control may offer a connection-related option, show information that is not a worksheet address, or be unavailable for editing. That does not necessarily mean the PivotTable has no source; it means its source is controlled through a different object.

For workbook connections and queries, open Data > Queries & Connections. In the pane, inspect the Queries and Connections tabs. A query can often be opened for inspection by right-clicking it and choosing Edit, which opens Power Query Editor. Its steps show how the query obtains and transforms its input; the Source step is a useful starting point, although later steps may filter, combine, or reshape the original records.

For a listed connection, right-click it and choose Properties when that command is available. Connection properties can help identify a file, server, or other connection details. A PivotTable built from the Data Model is not simply a view of one worksheet range: the model may contain multiple tables and relationships. If the Power Pivot tab is available, Power Pivot > Manage opens the model for inspection. The relevant source may be a query feeding a model table rather than the PivotTable itself.

Seeing the source is different from changing it

Opening the Change Data Source dialog is a way to inspect a reference, but the command is also used to repoint a PivotTable. If you enter a different range and confirm, the PivotTable may change what records it uses. Before changing a source, confirm the new range includes all required columns, has a single header row, and does not include unrelated notes or blank title rows. Field names in the source must be usable as PivotTable fields.

There is also a difference between changing a source and refreshing it. A refresh updates the PivotTable from its existing source; it does not automatically repair a wrong reference or guarantee that a fixed range now includes extra rows. To refresh one selected PivotTable, use PivotTable Analyze > Refresh. To refresh workbook data connections and PivotTables, use Data > Refresh All. If you are unsure whether a workbook is connected to an external system, check with its owner before refreshing, since a refresh can retrieve updated data or rely on access you may not have.

Do not mistake Show Details for the source reference

Double-clicking a number in a PivotTable can show the underlying records that contribute to that value, if the PivotTable allows that action. This feature is commonly called Show Details or drill-through. It answers a different question from Change Data Source: it can reveal which rows support a displayed total, but it does not identify or change the source range, query, or connection. For example, double-clicking a regional sales total may create a new sheet containing matching sales records, while the source definition remains unchanged.

Likewise, the PivotTable field list shows which fields are currently arranged in Rows, Columns, Values, and Filters. It helps explain how the summary is built, not where the records originate. To trace a PivotTable fully, inspect both its source and its field layout, and then check any query or connection that sits between the original records and the summary.

Common problems when checking a source

The PivotTable Analyze tab is missing: click inside an actual PivotTable, not beside it. If the object is protected or the workbook is in a restricted editing state, some commands may not be available.
The source looks too short: compare the final row in the reference with the last real record on the source sheet. A refresh alone will not extend a fixed range.
The dialog shows a name instead of cells: check whether it is an Excel Table or a defined name. Table Design identifies table names; Name Manager lists defined names.
The command is disabled or does not show a range: investigate Queries & Connections and the Data Model. External-source PivotTables can be controlled by their connection rather than by a worksheet range.
Old field items remain visible: cached PivotTable items can sometimes remain after source values have changed or disappeared. Their presence is not proof that those records are still in the current source. Inspect the actual source and, where appropriate, the PivotTable’s data-retention settings.

When a workbook has many PivotTables, do not assume they all use the same source. Different PivotTables can be based on different ranges, tables, or connections, even when they are on the same sheet and look alike. Check each PivotTable you plan to troubleshoot. If the workbook is important, save a separate copy before changing a source or connection.

Excel for Mac and Excel for the web

The steps above describe Excel for Microsoft 365 on Windows. Excel for Mac has PivotTable tools too, but ribbon placement and available connection features can differ by release; select the PivotTable and look for the contextual PivotTable Analyze controls. For a workbook that relies on external connections or the Data Model, the Windows desktop version may expose controls that are not available in the same way on Mac.

Excel for the web can display and work with many PivotTables, but its editing and connection-management options are more limited than the desktop application. If you cannot find Change Data Source or need to inspect a query, connection, or model, open the workbook in the desktop Excel app. Do not infer that a source is missing simply because the web ribbon does not expose the same command.

Frequently Asked Questions

Can I see a PivotTable’s source without changing it?
Yes. Open PivotTable Analyze > Change Data Source > Change Data Source, inspect the reference, then choose Cancel. Do not confirm an edited reference unless you mean to change it.

How do I find which sheet a PivotTable uses?
For a worksheet-range source, the Table/Range box includes the sheet name and cell address, such as Orders!$A$1:$E$240. If it shows a table or connection name instead, inspect that table, query, or connection to trace its origin.

Why can’t I change the data source?
The PivotTable may use an external connection or the Data Model, or the workbook may restrict editing. Check Data > Queries & Connections and, if relevant, the Power Pivot model. Some source types cannot be repointed through the worksheet-range dialog.

Does refreshing a PivotTable include newly added rows?
Only if those rows are within its source. A fixed range may stop before new records; an Excel Table is usually more convenient for growing data. Inspect the source, then refresh after correcting or expanding it.

Does double-clicking a PivotTable value show its data source?
No. It may show the underlying records contributing to that value, but it does not reveal the source definition. Use Change Data Source for a worksheet reference, or inspect the relevant query or connection.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.