Change Data Source for Pivot Table

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:

  1. First of all, click anywhere on the PivotTable to activate the analyze tab.
  2. After this, go to Analyze.
  3. Here you need to click on the “Change Data Source” option under Data.
  4. Now you will get a dialogue box “Change PivotTable Data Source” as below.
  5. 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.
  6. 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.
  7. 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