Excel PivotTables are one of the greatest tools in the spreadsheet user’s toolkit. However, there is one tiny bit of functionality that appears to be missing: the ability of pivot tables to automatically update when information in the source data changes. Most user’s see this as a glaring lack of functionality. There is, however a very good reason why pivot tables do not automatically update.
Suppose you are working in a file with dozens or even hundreds of pivot tables (yes; there are people out there who work on such things). If every time you changed a piece of data all of the pivot tables updated, it could bring the system to a standstill with all of the updates. Excel elects to wait and perform the updates when you are finished with the changes and give the “all clear” signal.
Most users do not work in this world; we may only have at most one, two, or five pivot tables in a file.
PivotTables are designed to only refresh when one of two events occurs:
- You manually request an update via the Refresh button on the PivotTable Tools ribbon
- You set the pivot table to automatically update upon file open via the Pivot Table Options
If you would like your pivot tables to refresh automatically when you change your data source, perform the following steps:
1. Right-click on the sheet tab containing your data and select View Code
2. In the code window, paste the following lines of text:
Private Sub Worksheet_Deactivate()
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub
EXAMPLE:
All of your pivot tables will refresh when you click off of the sheet holding the data. The Worksheet_Deactivate macro code will be executed whenever the sheet holding the code loses focus, i.e. you switch to a different sheet.