Home | SkillForge Blog | Automatically Refresh Excel Pivot Tables

Automatically Refresh Excel Pivot Tables

Microsoft Excel, Microsoft Office, PowerPivot, VBA

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:

1. You manually request an update via the Refresh button on the PivotTable Tools ribbon

auto_refresh_pivot_1

2. You set the pivot table to automatically update upon file open via the Pivot Table Options like this:

auto_refresh_pivot_2

auto_refresh_pivot_3

Auto Refresh Pivot Tables

If you would like your pivot tables to refresh automatically when you change your data source, perform the following steps:

1. Save your workbook as a Macro Enabled Workbook with a .xlsm file extension.

2. Right-click on the sheet tab containing your data and select View Code

auto_refresh_pivot_4

3. In the code window, paste the following lines of text then press the Save button in the VB Editor:

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:

auto_refresh_pivot_5

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.