Refresh Pivot Tables Automatically When Source Data Changes

Bottom Line: Learn how to use a simple macro to refresh pivot tables automatically whenever changes are made to the source data.  I also share a non-macro solution to update the pivot tables when the file is opened.  Includes video tutorial and Excel file download.

Skill Level: Intermediate

Download the Excel File

If you learn best by doing it on your own, you can download the file I'm using in the video to follow along.  Here is the Excel file that contains the VBA code.

Update Pivot Tables Automatically

Can your pivot tables be updated immediately and automatically when their source data changes?

Absolutely.  It requires the use of a really simple macro that I will show you how to create below.

If you’re not too familiar with macros and VBA, I suggest checking out my free 3-part video series on getting started with Macros & VBA.

Also, if you are new to pivot tables, I have a series to walk you through what they are and how to use them.  Watch the first video in that series on Pivot Tables & Dashboards

To automatically update our pivot tables, we are going to write a macro with one simple instruction.  That instruction basically says: when I make a change to my worksheet, refresh all the pivot tables and data connections.  Here are the steps to create the macro.

1. Open the Visual Basic Editor.

You can do this by clicking the Visual Basic button on the Developer tab of the ribbon.

The keyboard shortcut for opening the Visual Basic editor is Alt+F11.

If you don’t see the Developer tab, you can make it visible using the instructions here.  You only have to do this once, and then the Developer tab will always be visible every time you open Excel in the future.

2. Open the Sheet Module that contains your source data.

In the Project Explorer window of the Visual Basic editor, locate the workbook that you want to change.  Under that workbook are listed the sheets within the workbook.  Select the sheet that contains the source data.  Then double-click on it.

If you don’t see the Project Explorer window you can enable it from the View menu (keyboard shortcut: Ctrl+R).

3. Add a new event for worksheet changes.

Double-clicking on the sheet opens up the code module for that object.  Within the code module, we want to create an event macro.  To do so, choose Worksheet in the Object drop-down box on the left.

That will add a Worksheet_SelectionChange event to the module, which we don’t actually want, so we will delete it in just a moment.  Before we do, let’s go to the Procedure drop-down menu on the right and choose Change.

This adds a new event at the top called Worksheet_Change.  Now we will highlight and delete the unnecessary code below it.

The Worksheet_Change event macro will run any time a change is made to cells in that worksheet. We can add VBA code to the Worksheet_Change event to perform actions when the user edits cells.

Note: The SelectionChange event that is added by default will run any time the user selects a cell in the sheet. Since we only want the code to run when the user edits/changes cells , we use the Change event. Checkout my article on VBA Code Modules & How to Run Macros Based on User Events to learn more about the sheet modules and events.

4. Add the VBA code to refresh all pivot tables.

Next, just below the Worksheet_Change line, type in this instruction:

ThisWorkbook.RefreshAll

The RefreshAll method will refresh all the pivot tables, queries, and data connections in the workbook. This action is the same as if you manually click the Refresh button on the Data tab.

Add this line of code to the Worksheet_Change event will refresh the workbook whenever a change is made to the worksheet that the code is in.

Pivot Table & Source Data on Same Sheet

Aleksandrs asked a great question on the YouTube video comments.  If your pivot table and source data are on the same sheet then you will need to add code to disable events.

The refresh puts the event in a recursive loop, and can end up crashing Excel.  Here is the code to prevent that.

Application.EnableEvents = False
  ThisWorkbook.RefreshAll
Application.EnableEvents = True

Checking to Ensure Your Macro Is Running

One way to check if the macro is working is to test it.  Make a change to the source data and see if it is reflected in your pivot table.  If your change isn’t easy to spot because you have too much data, or for some other reason, there’s another way to see if your macro is firing.

In the VB editor, you can click on the gray column just to the left of your Worksheet_Change macro.  This will make a red circle appear.  It also highlights that line of code in red.

This is called a stop or breakpoint.

The keyboard shortcut to toggle a breakpoint on/off is: F9

Now whenever an action occurs that triggers the macro, Excel will jump to the VB Editor and pause the macro so that you can check the code.  In our case, that action is any change being made in the worksheet.

You can then press F8 to step through each line, or press F5 to run to the end (or next breakpoint).

If you make a change to the worksheet and Excel doesn’t pull you into the VB Editor, you know there is a problem with the macro not running.  If this is the case, it’s likely that you haven’t saved the file as a macro-enabled workbook (.xlsm), and/or enabled macros. You might need to save & close the file, then re-open it and enable macros.

To remove the breakpoint that you’ve placed on the macro, just click on the red circle to make it disappear (keyboard shortcut: F9).

The keyboard shortcut to clear all breakpoints is: Ctrl+Shift+F9

Refreshing Pivot Tables Without a Macro

One disadvantage to using this macro to refresh your pivot tables is that any Undo history is lost each time the macro runs.  In other words, when you click the Undo button (or press Ctrl+Z), Excel doesn’t remember the last thing you did, so it can’t undo it.  Consequently, nothing will happen, and your last change will not be undone.

There is an alternative that allows you to keep your Undo history.  However, this alternative only refreshes your pivot table when the workbook is opened, not every time a change is made.  Here is how you can use that option.

Starting from any cell in your pivot table:

1. Go to the Analyze tab in the ribbon.
2. Choose the Options button.
3. Go to the Data tab in the new window that opens.
4. Check the box that says, “Refresh data when opening the file.”

After clicking OK, you might get the get the following warning message if you have multiple pivot tables created from the same source data range.  Just click OK to get through it.

Again, just by way of comparison, if you use this option you retain Undo history, but it only refreshes the pivot table when the workbook is closed and reopened.  If you use the macro option, you lose Undo history, but the pivot table automatically updates whenever any change is made in the workbook.

Variations for Refreshing Pivot Tables

The macro we looked at will not only refresh your pivot tables, but will also refresh any queries as well.  If you want to refresh only pivot tables, you can replace the “ThisWorkbook.RefreshAll” command with this code instead:

Sub Refresh_All_Pivot_Table_Caches()
'Refresh all pivot caches in the workbook.
'Pivot tables are automatically refreshed when cache is refreshed.


Dim pc As PivotCache

  'Refresh all pivot tables
  For Each pc In ThisWorkbook.PivotCaches
    pc.Refresh
  Next pc
  
End Sub

Each pivot table is connected to an underlying pivot cache, which is connected to the source data. The code loops through all pivot caches in the workbook and refreshes each one. Pivot tables from the same source range can share pivot caches, so this method is faster than looping through all pivot tables.

Similarly, let's say you only want to refresh one particular pivot table.  In that case, you can swap out the “ThisWorkbook.RefreshAll” code with the code below.

And finally, if you are using Power Query and want to disable the background refresh so that queries are refreshed BEFORE pivot tables, I have written an article to explain how to do that by disabling the background refresh on the queries.

Use the Deactivate Event Instead

Another option is to use the Worksheet_Deactivate event instead of Worksheet_Change.  The Worksheet_Deactivate event will run every time the user leaves the sheet and selects a different sheet.  This allows the user to make all the changes to the source data, then the pivot table will be automatically refreshed when they go to any other sheet, including the sheets that contain the pivot table.

Private Sub Worksheet_Deactivate()
  ThisWorkbook.RefreshAll
End Sub

This code would still be placed in the sheet module that contains the source data.  This is a good option if your pivot tables or data connections take a few seconds or longer to update, and you don't want to wait every time a change is made to the source data.

The only time you might not want to use this is if your pivot table and source data are on the same sheet.  That will usually be a rare case, and something I generally don't recommend.

Thanks to the suggestion from Ted on this one.

Save Time & Embarrassment

I hope this article helps save you time and makes it easier for users of your files.  It can also help prevent embarrassment when you forget to refresh pivot tables before sending out reports.  Believe me, I've made this mistake more times than I'd like to admit… 🙂

Please leave a comment below with questions or suggestions.  Thank you!