Data Source of PivotTables: How to See and Change It

You receive an Excel workbook with a PivotTable and don’t know what data it is referring to? Or you have added a column or row to your data and want to adapt the Pivot Table? Unfortunately the function for seeing and changing the data source of a Pivot Table is a little bit hidden. Luckily, once you know where to find the button, it’s quite to see and change the source of the Pivot Table simple.

Steps for seeing and changing the data source of a Pivot Table

For seeing or changing the source data of a Pivot Table you have to click on the ‘Change Data Source’ button on the Analyze ribbon. Therefore (and if you haven’t found it yet) follow these steps (the numbers in the list below are corresponding to the picture):

pivot, table, pivottable, data, source, excel
Steps for seeing and changing the data source of a PivotTable
  1. Click anywhere into your Pivot Table so that the two Pivot Table ribbons ‘Analyze’ and ‘Design’ are shown.
  2. Go to the Analyze ribbon.
  3. Click on ‘Change Data Source’ in the middle of the Analyze ribbon.
  4. Now you can see and change the source of your Pivot Table. You can also see, if the PivotTable is referring to another file.

For large data sets (>500,000 rows) refreshing the data might take some time.

Further reading

If you are interested: We got a lot more articles about PivotTables in Excel. Here is some selection:

Professor Excel is a fictional person with one passion: Microsoft Excel. He is devoted to pass on his vast experience and help all future generations to excel in Excel!

Leave a comment

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