Excel Pivot Table Refresh

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

Introduction

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:

• manually
• automatically when file opens
• automatically on a timer (for connections)
• automatically with a macro

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.

Manually Refresh

A quick and easy way to refresh a pivot table after the data changes is to manually update it:

• Right-click any cell in the pivot table, then click on Refresh.

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:

• Manually: If you know the password, manually unprotect the worksheet, refresh the pivot table, and then protect the sheet again.
• Macros: If you're trying to refresh the pivot table with a macro, add code to unprotect the worksheet, refresh the pivot table, and then protect the sheet again. There are examples on the Pivot Table Protection sheet.

Warning Message

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:

• That command cannot be performed while a protected sheet contains another PivotTable report based on the same source data.
  To remove protection from the sheet that has the other report, click the sheet tab, and then click Unprotect Sheet (Review tab, Changes group). Then try the command again.

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:

• Right-click a cell in the pivot table, and choose PivotTable Options.
• In the PivotTable Options dialog box, click the Data tab
• Under PivotTable Data options, add a check mark to "Refresh data when opening the file"
• Click OK, to save the option setting

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:

• On the Excel Ribbon, click the Data tab
• Click Queries & Connections
• In the Queries & Connections pane, click the Connections tab
• Right-click on WorksheetConnection, and click Properties
  • 
• On the Usage tab, add a check mark for "Refresh every x minutes"
• Type a number in the minutes box, to set the timer
  • 
• Tip: If your source data changes frequently, and the data set isn't too big, use a low number. For other workbooks, use a higher number, so your work isn't delayed while you wait for the connection to refresh
• Click OK, to save the settings

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.

Below, you can see a pivot table refresh macro, with 2 lines of code added:

• one line before the pivot table refresh, to change the ManualUpdate setting to True
  • turns off automatic updating
• one line after the pivot table refresh, to change the ManualUpdate setting to False
  • turns on automatic updating

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:

• On the Ribbon, click the Data tab
• In the Connections group, click the upper section of the Refresh All command

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.

Esc Key

To stop a long refresh, press the Esc key on the keyboard.

Status Bar

If a refresh is running as a background query, use these steps to stop the refresh:

• Click the Refresh indicator on the status bar

• In the External Data Refresh Status dialog box, select a query from the list.
• Then, click the Stop Refresh button, and click Close, to close the dialog box.

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:

• We couldn't get data from the Data Model. Here's the error message we got:
• An unexpected error occurred (file 'pcminorobjcoll.inl', line 109, function PCMinorObjectCollection < class PCProperty, class NameHashSupport > ::SetNameAndUpdateCollection')

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:

• In the source data table, change the heading back to its original case – I changed Day back to DAY
• Then, refresh the pivot table
• The extra field should disappear from the PivotTable Field List

Avoid the Problem

To avoid this error, if you want to change the case of a field heading:

• In the source data table, change the heading case
• On the Excel Ribbon's Data tab, click the Manage Data Model command
• In the Power Pivot for Excel window, on the Home tab, click the Design View command
• In the diagram, right-click on the field name that you changed, and click Rename
  • 
• Type the field name, the way that you entered it in the source data table
• Close the Power Pivot Window

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 Sample File

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.

More Links

Plan and Set Up a Pivot Table

Pivot Table Errors

Pivot Cache Macros