Depending on the type of change in the Source Data used for Pivot Table, you may find the need to either Change Pivot Table Data Source or simply Change Pivot Table Range.
Change Pivot Table Data Source and Range
In most cases, it is common to add more Rows in the Source Data to accommodate new records or entries.
Similarly, you may also find the need to totally reorganize the Source Data by adding new Data Rows and New Data Columns.
If this happens, you will have to change Pivot Table Data Range to include the newly added columns and rows in the Source Data.
However, if the Source Data arrives in a new worksheet, you will find the need to change Pivot Table Data source from old to New Spreadsheet.
1. Change Pivot Table Data Range
Whenever new Rows or Columns are added to Source Data, you can follow the steps below to Change Pivot Table Data Range.
1. Click on any Cell in the Pivot Table and this will bring up “Analyze” and “Design” Tabs in the top menu bar.
2. Next, click on Analyze tab > Change Data Source > Change Data Source… option in the drop-down menu.
3. In Change Pivot Table Data Source dialogue box that appears, click in Table/Range box and select the entire Data Range (including new Rows & Columns) that you want to include.
3. Click on OK to save the changes.
2. Change Pivot Table Data Source Worksheet
If the Source Data for Pivot Table has arrived in a new worksheet, you can follow the steps below to change Pivot Table Data Source.
1. Click on any Cell in the Pivot Table and this will bring up “Design” and “Analyze” tabs in the top menu bar.
2. Click on Analyze > Change Data Source > Change Data Source option in the drop-down menu.
3. In Pivot Table Data Source dialogue box that appears, click in Table/Range box and click on the Worksheet containing new Source Data.
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.
After changing Data Source, make sure that Data Range includes all the rows and columns that need to be incorporated in the Pivot Table.
4. Click on OK to save the changes.
