Whenever the data used to create Pivot Table changes or gets reorganized, you will have to make changes to data source reference in Pivot Table. You will find below the steps to change Pivot Table Data Source and Pivot Table Data Range.
Change Pivot Table Data Source and Range
After creating Pivot Table, you may come across the need to add more Rows in the Source Data Worksheet to accommodate new records or to create totally reorganize the Source Data Worksheet data by adding new Data Rows and Columns.
If additional Rows or Columns have been added to Source Data Worksheet, you will have to change Pivot Table Data Range to include the newly added Rows and Columns.
In certain work environments, data collection and data reporting are done by different teams. In such cases, it is a standard practice to change Pivot Table Data Source from old to New Spreadsheet, whenever new or revised data is received.
Change Pivot Table Data Range
When you add new rows are columns to the worksheet containing the data for your Pivot Table, the data range reference in the Pivot Table does not automatically change to incorporate the changes.
In such a case, you will have to manually adjust or change Data Source Range to include the newly added rows and columns.
1. Click on any Cell in the Pivot Table and this will bring up the “Analyze” and “Design” Tabs in the top menu bar. Next, click on Analyze > Change Data Source and then click on Change Data Source… option in the drop-down menu.
2. Once you click on Change Data Source, you will see “Change Pivot Table Data Source” dialogue box opening in the Source Data worksheet. Click in Table/Range box and change the Data source range to accommodate the newly added data rows.
3. Click on OK to save the changes.
Change Pivot Table Data Source Worksheet
Follow the steps below to change Pivot Table Data Source from old to a new Source Data Worksheet.
1. Click on any Cell in the Pivot Table and this will bring up the “Design” and “Analyze” tabs in the top menu bar.
2. Click on Analyze > Change Data Source and then click on Change Data Source option in the drop-down menu.
3. In the Pivot Table Data Source dialogue box, click in Table/Range box > click on the Tab containing the new Source Data Worksheet and make sure that data range reference in Pivot Table refers to all the required Rows and Columns in the new Source Data Worksheet.
As you can see in above image, the “Table/Range” field refers to “Sales Jan” worksheet and clicking on “Sales Feb” will change Data Source for Pivot Table to the new worksheet.
4. Click on OK to save the changes.