It is quite common in most workplace situations to find additional or revised data being received or suggested for inclusion in a Pivot Table. In such cases you can incorporate the New or Revised data into the Pivot Table by changing the “Data Source” or by changing “Data Range”.
- Change Data Range: If the New information is limited, it can be easily incorporated into the Pivot Table by modifying the Source Data File (Adding the information to new Columns and Rows).
- Change Data Source: This method is suitable; if the new information has arrived in the form of a new Source Data File or if the changes are so widespread that it is better to use a New File.
Related: How to Replace Blank Cells with Zeros in Pivot Table.
1. Change Pivot Table Data Range
If you have added new Rows or Columns to the Source Data File, you can change the “Data Range” for the Pivot Table by using the following steps.
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 > Change Data Source and select Change Data Source… option in the drop-down menu to bring up the “Change Pivot Table Data Source” dialogue box.
3. In “Change Pivot Table Data Source” dialogue box, click in Table/Range box and select the entire Data Range (including new Rows & Columns) that you want to include.
4. Click on OK to save the changes.
2. Change Pivot Table Data Source Worksheet
If the Source Data for Pivot Table has arrived in the form of a new worksheet, you can change the “Data Source” for the Pivot Table by using the following steps.
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 > select Change Data Source option in the drop-down menu.
3. In Pivot Table Data Source dialogue box, 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.
4. After changing Data Source, make sure that Data Range includes all the rows and columns that need to be incorporated in the Pivot Table and click on OK to save the changes.