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):
- Click anywhere into your Pivot Table so that the two Pivot Table ribbons ‘Analyze’ and ‘Design’ are shown.
- Go to the Analyze ribbon.
- Click on ‘Change Data Source’ in the middle of the Analyze ribbon.
- 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:
- Pivotable: How to Prepare Data for Creating Pivot Tables in Excel. This is a comprehensive step-by-step guide of how to prepare your data – which is as so often not in a pivottable shape – for inserting a PivotTable.
- How to Avoid Changing the Column Width With Each Pivot Table Update: Each time you refresh the data of your PivotTable it changes the column width? Here is how you stop that.
- Pivot Tables: How to Create a Pivot Table in Excel: How to create a PivotTable.
- How to Work With Large Data and PowerPivot: If you got a large amount of data it might be time to try the free add-in PowerPivot.
