It would be nice if Excel would automatically refresh a pivot table, whenever its source data changes, but unfortunately, there isn’t a built-in way to make that happen. Here are 3 ways that you can refresh a pivot table, after you make changes to the source data
No Built-In Way to Automatically Refresh a Pivot Table
If you add new records, or delete records, or edit the existing data, the pivot table doesn’t show the revised data right away. There isn’t any setting the you can change, to make Excel automatically refresh a pivot table.
To keep the pivot table up-to-date, you have 3 choices:
- Manually refresh the pivot table
- Use programming to update it
- Change a pivot table setting, to get some automatic updates
A quick and easy way to refresh the pivot table after the data changes is to manually update it:
- Right-click any cell in the pivot table, then click on Refresh.
Refresh When File Opens
Another way to update is to set the pivot table to refresh when you open the file that it’s in. With this method, you’ll get partial automation, without having to add macros to the file. Any time you close then re-open the file, the pivot table will be refreshed.
To set this up:
- Right-click any cell in the pivot table
- Click PivotTable Options
- In the PivotTable Options window, click the Data tab
- In the PivotTable Data section, add a check mark to Refresh Data When Opening the File
- Click OK to close the dialog box.
Automatically Refresh With Programming
The final way to update the pivot table is with programming. You can an Excel macro to automatically refresh a pivot table when its worksheet is activated. There are instructions on my Contextures website, for copying VBA code to your own files.
Note: See this article for help with pivot tables on a protected sheet.
Macro for One Pivot Table
Put the following code on the worksheet module, if there is one pivot table on the sheet:
Private Sub Worksheet_Activate() Me.PivotTables(1).RefreshTable End Sub
Macro for Multiple Pivot Tables
If there are multiple pivot tables on the sheet, with different source data, use this code, pasted onto the worksheet module:
Private Sub Worksheet_Activate() Dim pt As PivotTable For Each pt In Me.PivotTables pt.RefreshTable Next pt End Sub
Pivot Table Tools
To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:
- copy the formatting from one pivot table, and apply it to another pivot table.
- change all the values from Count to Sum
- remove the “Sum of” from all the headings
and much more!