Change Data Source for Pivot Table

HomePivot TableChange 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.
    1-go-to-analyze-tab
  3. Here you need to click on the “Change Data Source” option under Data.
    2-change-data-source-option
  4. Now you will get a dialogue box “Change PivotTable Data Source” as below.
    3-change-pivot-table-data-source
  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