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:

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Leave a comment

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