Excel Pivot Table Refresh Steps and Fixes
How to refresh Excel pivot table, manually or automatically, or stop a refresh, with steps for normal pivot tables or data model pivot tables. How to fix refresh error, "couldn't get data from the Data Model"
When you change the information in a pivot table's source data, the pivot table doesn't automatically show the latest information. You need to refresh the pivot table, manually or automatically, and then the latest data will appear.
There are steps below that show how to refresh a pivot table:
NOTE: When you refresh a pivot table, its pivot cache is refreshed. ALL pivot tables that use the same pivot cache will also be refreshed.
A quick and easy way to refresh a pivot table after the data changes is to manually update it:
Pivot Table on Protected Sheet
When a worksheet is protected, you can't refresh the pivot tables on that sheet. When you right-click on the pivot table, the Refresh command is dimmed out.
Here's how to work around that problem:
If other pivot tables use the same pivot cache, you might see a warning message if one or more of those other pivot tables are on protected sheets. The warning message says:
As the message says, you will need to unprotect those other sheets too, before you can refresh any of the pivot tables that use the same pivot cache.
If you're refreshing with macros, this macro shows how to unprotect all the worksheets, refresh the pivot tables, and then protect the sheets again.
Refresh When File Opens
In some workbooks, you might want to refresh a pivot table as soon as the workbook opens, just in case someone made changes to the source data.
To do that, set a pivot table option to refresh the pivot table automatically:
Refresh On a Timer
For OLAP-based pivot tables, you can also refresh based on a timer in its connection.
NOTE: When you created a pivot table, if you added its data to the Data Model, your pivot table is OLAP-based.
To set the connection timer to refresh automatically, follow these steps:
Refresh With Macros
Another way to update a pivot table is with programming. You can use an Excel macro to automatically refresh a pivot table when the pivot table's worksheet is activated.
Add the following macro to the pivot table sheet's code module. See the instructions for copying VBA code to your own files.
NOTE: If you're trying to refresh pivot tables on protected sheets, there are examples macros on the Pivot Table Protection sheet.
Macro for One Pivot Table
Put the following code on the worksheet module, if there is only 1 pivot table on the sheet. This will automatically refresh the pivot table, as soon as you go to its worksheet.
Private Sub Worksheet_Activate() Application.EnableEvents = False Me.PivotTables(1).RefreshTable Application.EnableEvents = True End Sub
Macro for Multiple Pivot Tables
If there are multiple pivot tables on the sheet, with different source data, use this code, pasted onto the worksheet module. This will automatically refresh all the pivot tables on the sheet:
Private Sub Worksheet_Activate() Dim pt As PivotTable Application.EnableEvents = False For Each pt In Me.PivotTables pt.RefreshTable Next pt Application.EnableEvents = True End Sub
Faster Pivot Table Macros
If you run a pivot table macro, and the code takes a long time to run, try adding code that turns automatic updating on or off for the pivot table.
This short video shows that a pivot table macro might run faster, if the ManualUpdate setting is turned on, before refreshing the pivot table. There are written notes below the video.
Below, you can see a pivot table refresh macro, with 2 lines of code added:
Faster Macro Code
Here is the pivot table refresh macro, with 2 lines that change the ManualUpdate setting (shown in bold text).
Note: You could make a similar change to other macros, where a pivot table is being refreshed
Private Sub Worksheet_Activate() Application.EnableEvents = False With Me.PivotTables(1) .ManualUpdate = True .RefreshTable .ManualUpdate = False End With Application.EnableEvents = True End Sub
Refresh Multiple Pivot Caches
If there are two or more pivot tables in your workbook, based on different data sources, they won’t all update when you refresh one of the pivot tables.
Instead, you can use the Refresh All button.
Note: Using the Refresh All command also refreshes all external data ranges in the active workbook, and it affects both visible and hidden worksheets in the active workbook.
To refresh all the pivot tables, and external data ranges, in the active workbook at the same time:
TIP: You can add the Refresh All button to your Quick Access Toolbar, so it’s easier to use
Stop a Refresh
Usually, a Refresh goes quickly, but occasionally one can take a long time to run. If you want to stop a refresh, use one of these methods.
To stop a long refresh, press the Esc key on the keyboard.
If a refresh is running as a background query, use these steps to stop the refresh:
Couldn't Get Data Error
A simple data change can cause a strange pivot table refresh error, if you added the pivot table data to the Data Model. Thanks to UniMord, for letting me know about this error, and how to prevent it.
Here's an edited version of that message, with some of the text moved, so you can read all of it.
And here's a text version of the message, in case anyone is searching for help with this error message:
NOTE: For other types of pivot table error messages, go to the Pivot Table Errors page.
When Did This Error Message Appear?
This error message appeared after one of the source data headings was changed from UPPER case to Proper case. That created a second instance of the field in the data model, which could be seen in the PivotTable Field List.
Fix the Problem
To fix this problem, if you see this error message:
Avoid the Problem
To avoid this error, if you want to change the case of a field heading:
NOTE: This was a brief description of the error and its fix. For the longer version, with more screen shots and details, see the article on my Contextures Excel Blog.
Download the Pivot Table Refresh sample file, to follow along with the instructions. The zipped file is in xlsx format, and there are no macros in the workbook. There are 2 pivot tables, Data Model and Normal, so you will see a connection alert message, when you open the file.
Last updated: November 19, 2022 2:41 PM