In this post, we will discuss changing the data source or locating the data source for a given PivotTable. This is extremely helpful when your data keeps on changing constantly.
Use the following steps:
- First of all, click anywhere on the PivotTable to activate the analyze tab.
- After this, go to Analyze.
- Here you need to click on the “Change Data Source” option under Data.
- Now you will get a dialogue box “Change PivotTable Data Source” as below.
- Here in this example, the data range for the PivotTable is Sheet1!$A$1:$F$8 which means that the rows A9 till A13 are not included in the PivotTable.
- Now either select the complete data i.e. Sheet1!$A$1:$F$13 or use the table name range to update your PivotTable/data source.
- In the end, click ok to update the data source.
Apart from this, you can also use the shortcut key Alt ⇢ J ⇢ T ⇢ I ⇢ D to open the “Change PivotTable Data Source” dialog box directly.
Quick Tip: Always convert the source data into an Excel Table by pressing the keyboard shortcut Ctrl + T. By doing this, there will be no need to update your data source again and again. Just refreshing the PivotTable will update the data range.
More on Pivot Table
Sort a Pivot Table | Refresh a Pivot Table | Pivot Table Keyboard Shortcuts | Pivot Table Formatting |Move a Pivot Table | Filter a Pivot Table | Count Unique Values in a Pivot Table | Add or Remove Grand Total in a Pivot Table | Add Ranks in Pivot Table | Insert Calculation in Pivot Table | Refresh All Pivot Tables | Automatically Update a Pivot Table | Running Total in a Pivot Table | Conditional Formatting to a Pivot Table | Pivot Table from Multiple Worksheets | Group Dates in a Pivot Table | Connect a Slicer with Multiple Pivot Tables | Pivot Table Timeline