Ed. Note: Click on any picture to view full size
There are relatively few things as aggravating as spending hours trying to track down why the calculations in your PivotTable do not match your data than to finally realize that you forgot to refresh your PivotTable…
Here are a few tips & tricks for automatically refreshing your PivotTables.
Automatically Refresh PivotTables Upon Opening Excel Workbook
This first trick shows you how to set your PivotTable to refresh all calculations automatically as soon as you open your workbook. The obvious drawback is, if you change any of your data after you open the workbook, you will still have to refresh the PivotTables to reflect the new data.
To enable this feature, right-click anywhere on your PivotTable & select “PivotTable Options”
Next, select the “Data” tab and then select the “Refresh data when opening the file” option. Click “OK” to save your selection.
Write a Macro
This second option is very easy to implement and will refresh your PivotTable every time you select the worksheet tab that contains the PivotTable. Unfortunately, I have not figured out how to make your PivotTable refresh every time you change data in your data source (if anyone has a better macro, please share in the comments below!).
The drawback to this trick is, you have to leave the PivotTable tab and then return to it to force a refresh. Still – if you have your data on a different tab & you’re going back and forth anyways to check your calculations than this should help prevent our original aggravating scenario…
Right click on the sheet name where you placed the PivotTable and select “View code”
Copy the following code into the resultant code window (Note: if you copy & paste, you may have an error because the double-quote symbol does not always paste correctly into Excel – if you have a problem, try replacing all the double-quotes with new ones in the code editor.)
Private Sub Worksheet_Activate()
You will need to change this code to match your specific information as follows:
- “PivotTable” is the name of the worksheet tab where your PivotTable resides
- “PivotTable1” is the name of your PivotTable
Click the “Save” button. (Highlighted red in the picture below)
To find the name of your PivotTable, simply click anywhere on your PivotTable and then look at the name listed in the PivotTable section on the “Options” Tab on the special “PivotTable Tools” area of the ribbon. (In my example picture below, the PivotTable name is PivotTable4).
Questions or related tips/tricks? Let me know below!