It would be nice if a pivot table automatically updated, whenever its source data changes, but unfortunately, that doesn’t happen.
If you add new records, or delete records, or edit the existing data, the pivot table doesn’t show the revised data right away.
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.
Refresh With Programming
The final way to update the pivot table is with programming. You can use Excel VBA to automatically update a pivot table when its worksheet is activated.
There are instructions on my Contextures website, for copying VBA code to your own files.
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
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