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 error, "couldn't get data from the Data Model"
Author: Debra Dalgleish
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:
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.
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:
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:
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.
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
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
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.
Below, you can see a pivot table refresh macro, with 2 lines of code added:
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
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
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:
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.
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.
To fix this problem, if you see this error message:
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: May 1, 2022 2:32 PM