Since your Pivot Table is created using the Pivot Cache, when the existing data changes or when you add new rows/columns to the data, the Pivot Cache does not update itself automatically, and hence, the Pivot Table also does not update. You need to force a refresh every time there are changes. Once you force a refresh, the Pivot Cache gets updated, which is reflected in the Pivot Table. This tutorial covers a couple of ways to do this.
Refresh Pivot Table
This option is best suited when there are changes in the existing data source and you want to refresh the pivot table to reflect these changes. Here are the steps to refresh a Pivot Table:
Right-click on any cell in the Pivot Table. Select Refresh.
This will instantly refresh the Pivot Table. You can also by selecting any cell in the Pivot Table and use the keyboard shortcut ALT + F5. Quick Tip: It’s a good practice to convert the data source into an Excel Table, and use this Excel Table to create the Pivot Table. If you do this, you can also use the refresh technique to update the Pivot Table even when new data (rows/columns) are added to the data source (since an Excel Table automatically accounts for new rows/columns that are added).
Update Pivot Table by Changing the Data Source
If you’ve added new rows/columns to the data source, you need to change the data source to make sure new rows/columns are a part of the dataset. To do this:
Select any cell in the Pivot Table. Go to Analyze –> Data –> Change Data Source. This will select the data source that you have used and will open the ‘Change PivotTable Data Source’ dialog box. In the Change PivotTable Data Source dialog box, update the range to include new data. Click OK.
Note that if you change the data source into an Excel Table and then use the Excel table to create the Pivot Table, you don’t need to use the change data source option. You can simply refresh the Pivot Table and it’ll account for the new rows/columns.
Autorefresh Pivot Table Using a VBA Macro
While refreshing a Pivot table is as easy as two clicks, you still need to do this every time there is a change. To make it more efficient and auto-refresh the Pivot Table whenever there is a change in the data source, you can use a simple one-line VBA macro code. Here is the VBA code: Decoding the Code: This is a change event which gets triggered whenever there is a change in the sheet that contains the source data. As soon as there is a change, the code refreshes the Pivot Cache of the Pivot Table with the name PivotTable1. You need to modify this code to make it work for your workbook:
“Sheet1” – change this part of the code with the name of the sheet that has the Pivot Table. “PivotTable1” – change this to the name of your Pivot Table. To know the name, click anywhere in the Pivot Table and click the Analyze Tab. The name would be visible in the left part of the ribbon under the ‘PivotTable Name’ header.
Where to put this VBA code:
Press Alt + F11. It will open the VB Editor window. In the VB Editor, there would be Project explorer on the left (that has the names of all the worksheets). If it’s not there, press Control + R to make it visible. In the Project Explorer, double-click on the sheet name that contains the Pivot Table. In the code window on the right, copy paste the given code. Close the VB Editor.
Now when you change anything in the data source, the Pivot Table would automatically get refreshed. Click here to download the example file. Note: Since there is a macro in the workbook, save this with .xls or .xlsm extension.
How to Group Dates in Pivot Tables in Excel. How to Group Numbers in Pivot Table in Excel. How to Filter Data in a Pivot Table in Excel. Preparing Source Data For Pivot Table. How to Apply Conditional Formatting in a Pivot Table in Excel. How to Add and Use an Excel Pivot Table Calculated Field. How to Replace Blank Cells with Zeros in Excel Pivot Tables. Using Slicers in Excel Pivot Table. Move Pivot Table to Different Worksheet or Workbook
