How to Change the Data Source for Pivot Table in Excel

puneet-gogia-excel-champs-09-06-23

- by Puneet

In this post, we will discuss changing the data source or locating the data source for a given Pivot Table. This is extremely helpful when your data keeps on changing constantly.

You can use the below steps:

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

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 Pivot Table will update the data range.