Excel Pivot Table: How to Find the Original Source Data

To find an Excel PivotTable’s source, click any cell in the PivotTable, then choose PivotTable Analyze > Change Data Source. The dialog usually shows the source table name or cell range; if the PivotTable uses a connection or the Data Model, follow the connection or query instead because there may not be one simple worksheet range.

Find the source range or table

In Excel for Microsoft 365 on Windows, select a cell inside the PivotTable so its contextual ribbon tabs appear. Open PivotTable Analyze, then select Change Data Source. In the dialog, inspect the Table/Range box. It may contain a worksheet reference such as Sales!$A$1:$F$500, a reference to a named range, or the name of an Excel table such as SalesData. The dialog is a way to identify the PivotTable’s configured source; you do not need to change anything to read it. Select Cancel when you are finished.

Click inside the PivotTable, not just somewhere on its worksheet.
Choose PivotTable Analyze > Change Data Source.
Read the entry in Table/Range, then cancel the dialog if you only wanted to inspect it.

If the ribbon tab is not visible, check that the selection is actually within the PivotTable. A cell beside the report, even one that looks like part of it, does not activate the PivotTable tools. The worksheet name in a range reference identifies where the source cells are located; the dollar signs indicate fixed row or column references and do not mean the source is a separate file.

What the source entry tells you

A worksheet range identifies specific cells. For example, Orders!$A$1:$E$800 means the source includes columns A through E and rows 1 through 800 on the Orders sheet. The first row commonly contains field names, while the following rows contain records. The range tells you where Excel reads the records, but it does not necessarily tell you who created the data or whether it was imported from another system.

An Excel table name, such as SalesData, means the PivotTable is based on a structured table rather than a fixed address. To locate it, look for a table with that name in the workbook. Select a cell in a suspected table and open Table Design; the table name appears in the Table Name box. You can also use the Name Box to go to a named range, though a table name and a defined name are different kinds of workbook objects.

Names and ranges can be less obvious than they look. A named range might point to a range on another sheet, while a range address may refer to a sheet that is hidden. If you cannot see the named sheet, right-click a sheet tab and choose Unhide if that command is available. A workbook can also contain hidden rows or columns within the source; those records may still be included in the PivotTable.

When the PivotTable uses a query or external connection

Some PivotTables are built from data brought in through Power Query, a database connection, or another external source. In those cases, the Change Data Source dialog may present connection-related options instead of a straightforward worksheet range, or the usual range field may not be editable. The PivotTable’s source may be a query result loaded to a worksheet, a connection-only query, or data retrieved directly from an external system.

On Windows, choose Data > Queries & Connections to open the pane and look for a query or connection with a relevant name. If the PivotTable is based on a query, right-click the query and choose Edit to inspect its steps in Power Query Editor. The first step is often called Source, though the sequence and wording vary by connector. It may identify a workbook file, folder, database, or other origin. A query can also combine or transform data, so its final output may differ substantially from the original source.

For a workbook connection, inspect the workbook’s connection list through Data > Queries & Connections or the related connection commands available in the workbook. Connection properties may expose a file path, server, database, or connection string, depending on the connection type and permissions. Treat that information as a lead, not a guarantee that the source is accessible: a network location may require a VPN, credentials, or access rights.

PivotTables built from the Data Model

A PivotTable based on the Excel Data Model may draw from multiple tables connected by relationships. In that situation, asking for “the source range” can be misleading: the report might combine a sales table with product, date, and customer tables. The Change Data Source command may be unavailable or may not reveal the whole model as one cell range because the PivotTable is not based on a single worksheet selection.

On Windows, if the Power Pivot add-in is available and enabled, choose Power Pivot > Manage to inspect the model’s tables and relationships. You can also review queries and connections in the Data tab. Check the PivotTable’s field list for table names, then look for those names in the model or query list. A field named Product, for instance, could come from a Products table even if the report’s values come from a separate Sales table.

Data Model access depends on Excel edition, platform, and workbook setup. Excel for Mac does not provide the same Power Pivot management tools as Excel for Windows, and the Excel web app has a more limited set of PivotTable and connection-management features. If a command is missing, open the workbook in desktop Excel for Windows or ask the workbook owner to inspect the model and its connections.

Why Change Data Source may be unavailable

A disabled command does not automatically mean the PivotTable is broken. Common explanations include a PivotTable created from an external connection, an OLAP source, or the Data Model; a protected worksheet; or a selection that is not inside the PivotTable. Some workbook designs restrict changes intentionally, and a connection may be controlled by an administrator or another system.

Confirm the selection: click a value or label inside the PivotTable and check that the contextual tab appears.
Check protection: if the sheet is protected, you may need permission or the password before changing the source.
Look for a connection: review Data > Queries & Connections and, where available, the Data Model.
Check the platform: open the file in desktop Excel for Windows if the web or Mac interface does not expose the needed controls.
Ask about permissions: a workbook can display results even when you cannot access or edit the original external data.

Do not assume that the source data is embedded in the workbook just because the PivotTable displays records. A PivotTable can retain cached values from an earlier refresh. Those values may remain visible even if the original file has moved or a server is unavailable, but they do not prove that the live source can currently be reached.

Finding the original records on a worksheet

Once you have a range or table name, go to its worksheet and inspect the source headers and records. If the PivotTable uses a range reference, the sheet name in the address is usually the quickest route. If it uses a table, use Table Design to confirm the table name and then inspect its boundaries. Compare the source columns with the fields shown in the PivotTable field list; matching headers help verify that you found the right dataset.

You can also use a PivotTable value to trace contributing records. Double-click a numeric value in the PivotTable, or right-click it and choose Show Details when available. Excel creates a new worksheet containing the underlying records that contribute to that value. This is useful for investigating a total, but it is not a substitute for identifying the configured source: the extracted rows may represent only one group, and the command may be unavailable for some connection-based PivotTables.

For example, if a PivotTable shows total revenue for one region and month, Show Details can return the transactions behind that displayed total. It does not necessarily return every row in the full source table. Use the Change Data Source dialog, query list, or Data Model tools to identify the broader source; use Show Details to examine records behind a particular result.

Changing the source and refreshing safely

Finding a source and updating it are separate tasks. Refresh asks the PivotTable to read again from its existing source. It does not usually expand a fixed range to include new rows added outside that range. If the source is a normal range and new records fall beyond its last row or column, use PivotTable Analyze > Change Data Source to select the correct expanded range, then refresh. Check that the first row still contains the intended field names and that there are no blank header cells.

If the source is an Excel table, adding records directly below the table normally expands the table, and a refreshed PivotTable can include those records. That is one reason tables are often more convenient than fixed ranges for data that grows over time. However, confirm that new rows actually became part of the table; pasted data separated from the table by a blank row or column may not be included.

Before changing a source, consider whether other PivotTables share the same cache or connection. A source change can affect the report’s available fields or cause refresh errors if the new data has different headers or types. Keep a copy of the workbook before making significant changes, and verify totals and filters afterward. For a query-based or Data Model PivotTable, change the query or model source using the relevant connection tools rather than trying to force a worksheet range into the PivotTable.

Frequently Asked Questions

Can I see the PivotTable source without changing it?
Yes. Open PivotTable Analyze > Change Data Source, read the source shown in the dialog, and select Cancel. Do not edit the entry unless you intend to change the source.

How do I find the source sheet from a PivotTable?
Check the range shown in the Change Data Source dialog. A reference such as Orders!$A$1:$E$800 names the worksheet as well as the source cells. If the entry is a table name, locate that table and check its worksheet.

Why can’t I click Change Data Source?
The PivotTable may use an external connection or the Data Model, the worksheet may be protected, or your selection may be outside the PivotTable. Check the selection first, then inspect Queries & Connections and the workbook’s model if available.

Does Refresh include new rows automatically?
It depends on the source. An Excel table generally expands as records are added directly beneath it, while a fixed cell range may not include rows outside its original boundary. Refresh reads the current source; it does not necessarily redefine a fixed range.

Can I recover the original source file from a PivotTable?
Sometimes. A query or connection may show a file path or server details, but a PivotTable cache alone may not preserve a usable link to the original file. If the source is inaccessible, ask the workbook owner or data administrator for the current location and permissions.

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.