How to locate and change the source data for an Excel pivot table. For missing source data, steps to try to recreate it. Change options, to save source data with pivot table file.
In this video, you'll see how to locate the pivot table data source, then check the data source, to make sure it includes all the rows and columns that you need. If necessary, adjust the data source, to include new rows or columns.
NOTE: For a long term solution, use a named Excel table, as a data source. It will adjust automatically, if new rows are added.
The written instructions are below the video, and you can download the sample file to follow along with the video.
After a pivot table has been created, you might want to add new records in the source data, or change the existing records. In a large workbook, it can be difficult to locate the exact source for the pivot table, if there are several tables or lists.
NOTE: If you've bought my Pivot Power Premium add-in, click Pivot Table Info, then click Go to Source Data.
Follow these steps, to find the source data for a pivot table:
The Change PivotTable Data Source dialog box opens, and you can see the the source table or range in the Table/Range box. This may be a reference to a sheet and range of cells, such as
or a table name, such as
Behind the dialog box, you can see the source range on the worksheet, surrounded by a moving border.
In the screen shot below, the source data range ends at row 9, and a new record has been added in row 10. That record will not appear in the pivot table, unless the Data Source is adjusted.
If a pivot table's source data is a static reference to a specific sheet and range, it does not adjust automatically when new data is added. In the screen shot above, the Data Source range refers to Orders!$A$1:$H$9, and it can be manually adjusted, to include row 10.
To manually adjust the static source range:
You can adjust the source range when necessary, so it includes any new data, but a better solution is to create a dynamic source, and base the pivot table on that. Instructions are below.
Occasionally, you might need to change a pivot table, so it uses a different data source. In this example, a new table has been created, with only data for the East region. The pivot table will be changed to use that source, and then it can be send to a manager in the East region, without revealing the West region's data.
NOTE: After changing to a different source table, you might need to rename some fields, or add different fields to the pivot table.
To change the source data for an Excel pivot table, follow these steps:
Instead of using a static range as the pivot table's data source, a better solution is to create a dynamic range, that will adjust in size automatically.
Instead of using a static range as the pivot table's data source, a better solution is to create a dynamic range, based on a named Excel table. There are written instructions here, or watch the video below.
Make note of the table name, so you can use it as the pivot table source
If you can't use an Excel Table as the source data, you create a dynamic named range, based on a an INDEX or OFFSET formula. There are written instructions here, or watch the video below.
The video shows how to create a dynamic range with the OFFSET function, in Excel 2007, and the steps are similar in later versions.
NOTE: Remember the range name, so you can use it as the pivot table source
If you have an Excel version that supports the new functions, such as SORT, or UNIQUE, you can use those functions to create dynamic arrays. This technique uses formulas only - no macros.
In the Download section, get the Filtered Source Data sample file. It shows how to set up a named range with only the visible rows from a named Excel table.
Here is the filtered data, on a different sheet, with only the 2 reps, and 3 categories from the visible rows.
Then, you can create a pivot table based on that filtered data only.
After you create a dynamic source that contains the pivot table data, follow these steps, to use that source data:
When you create a pivot table in your workbook, the source data records are saved in a special memory area -- a pivot cache. Then, when you close the file, Excel can save the source data in this pivot cache, or clear that memory.
There are advantages and disadvantages to both options:
To turn the Save the Source Data setting on or off:
If you choose to turn off the Save Source Data with File option, you should turn on the Refresh Data When Opening the File option. That setting is just below the Save Source Data check box.
Otherwise, you'll see a message when you try to filter the data, or make any other layout changes.
If you see that message, click OK, then manually refresh the pivot table.
And to avoid the annoying message, turn on the Refresh Data When Opening the File option.
If you accidentally delete the worksheet that has the source data for your pivot table, or if you received a file without the pivot table's data, you might be able to use the pivot table's Show Details feature to recreate it.
NOTE: This will not work for all pivot tables, but is worth trying, to recover the source data.
To try to recreate the source data for a pivot table, follow these steps to use the Show Details feature:
If you recover the source data, you might need to make some additional changes, to restore it to its original condition.
If you rename the table that was created during the Show Details process, and use the same name as the table that originally held the source data, the pivot table might automatically connect to the new source data.
If not, you can connect to the recreated source data -- follow the steps in the section above: Use Dynamic Source For Pivot Table -- use the table name that you gave to the new table.
Pivot Source Example: To follow along with the tutorials, you can download the DateAmt.zip file. The zipped file is in xlsx format, and does not contain macros.
Filtered Source Data Example: This example is for Excel versions that have the new functions, such as SORT and UNIQUE. Download this sample file to see a pivot table is created from the visible rows only, in a filtreed Excel table. The zipped file is in xlsx format, and does not contain macros.
To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:
and much more!
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Last updated: March 2, 2021 8:58 PM
Contextures RSS Feed