Stop a Pivot Table Refresh

After you add new records to a pivot table’s source data, you’ll have to Refresh the pivot table, to see the new data. See the steps for a pivot table refresh, and how to stop a pivot table refresh. Also, see why other pivot table will refresh at the same time, and how to prevent that from happening.

Stop a Refresh

Usually, a Refresh goes quickly, but occasionally one can take a long time to run. If you want to stop it, use one of these methods.

1. To stop a long refresh, press the Esc key on the keyboard.
   OR
2. If a refresh is running as a background query, 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.

Pivot Cache

When you create a pivot table in Excel, a pivot cache is automatically created. The pivot cache is a special memory area where the pivot table records are saved. If there are multiple pivot tables in a workbook, they might use the same pivot cache, or different pivot caches.

When you refresh a pivot table, you’re also refreshing its pivot cache. If the Excel workbook has several pivot tables based on the same pivot cache, all of them will be refreshed, if you update any one of those pivot tables.

Pivot Cache List

To see which pivot cache each pivot table uses, you can run a macro to create a Pivot Cache list in your workbook

Is there a pivot table in that list that you don’t want to update with other pivot tables?

If so, you can use a macro create a separate pivot cache for that pivot table.

Refresh a Pivot Table

To Refresh a pivot table, and all others based on the same pivot cache:

• Right-click a cell in the pivot table
• Click Refresh

NOTE: When you refresh the pivot table, the entire pivot table is affected. You can’t refresh only part of a pivot table, or just add the new data to the pivot cache.

Refresh Multiple Pivot Caches

If there are two or more pivot tables in your workbook, based on different pivot caches, 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

Related Articles

Automatically Refresh a Pivot Table

Pivot Table Refresh

Pivot Cache Macros

___________________