Find and Fix Pivot Table Source Data

After you create a pivot table, you might add or change records in the source data. Sometimes the pivot table does not update correctly, to show the new data. Or if you've inherited the pivot table file from someone else, you might not even be able to find the source data, in a large workbook.

To help with these problems, I've added a new page on my Contextures site, with tips for finding and fixing the source data for a pivot table.

On the new page, you'll see how to:

  • locate the pivot table data source.
  • check the data source, to make sure it includes all the rows and columns that you need.
  • adjust the data source, to include new rows or columns.

For a long term solution to source data problems, the tutorial shows how to:

  • create a named Excel table, as a dynamic data source. It will adjust automatically, if new rows are added.
  • change the pivot table data source, so it is based on the new named Excel table.

There is a sample file that you can download, to follow along with the tutorial.

Watch the Video

If you'd rather see how it's done, instead of reading the instructions, watch this short video.


alt text: Find and fix pivot table data source problems


You may also like...

2 Responses

  1. AlexJ says:

    Excellent post. Let me add a couple of ‘helpers’.

    1. I’ve added the [PivotTable and PivotChart Wizard] icon to the QAT, since its not on the ribbon. This gives one-click access to the pivot table data source dialogs. I use this often.

    2. (I think you’ve posted this before) The Clear Filters button on the [Data] tab, [Sort & Filter] section clears all the pivottable filters when the pivot is selected (I also have this in my QAT).

    3. Based on something you posted a couple of years ago, I built a VBA utility to list all the pivottables in a workbook with their sheet names, ranges, pivotcache numbers, source data types and names. Helps to find source data and straighten out things pretty well. (I also built a simiar utility for slicers and tables (list objects). Now that I think of it, I may do one for workbook queries as well!)

Leave a Reply

Your email address will not be published. Required fields are marked *