Click any cell in a pivot table and go to PivotTable Analyze → Refresh → Refresh All, or press Ctrl + Alt + F5. This refreshes every pivot table in the workbook, along with any queries and data connections. Data → Refresh All does the same from any sheet.
To refresh only the pivot table you are in, press Alt + F5 or right-click it and choose Refresh. Pivot tables built from the same source data usually share a cache, so they refresh together.
To refresh every time the file opens, right-click a pivot table, choose PivotTable Options → Data and tick Refresh data when opening the file. The setting belongs to the data source, so set it once for each source, and expect large files to open more slowly.
For a one-click macro, refresh each pivot cache in the workbook. Use ThisWorkbook.RefreshAll instead if Power Query or other connections feed the pivot tables.
Sub RefreshAllPivots()
Dim pc As PivotCache
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc
End Sub
If a pivot table misses new rows after a refresh, its source is a fixed range. Convert the data to a table with Ctrl + T and point the pivot table at it through PivotTable Analyze → Change Data Source.
If a Power Query feeds the pivot table and Refresh All leaves it one step behind, right-click the query in Data → Queries & Connections, choose Properties and untick Enable background refresh, so the query finishes before the pivot table refreshes.
If you refresh all the pivot tables at once, you can save a lot of time. Yes, that’s right. Just think like this.
You have ten pivot tables, and it takes one second to refresh a single pivot table:
10 Pivots = 10 Seconds
Just like you can automate the creation of a pivot table, there are a few ways to refresh it automatically.
In this post, I’m going to share with you a few simple ways that you can use to refresh all the pivot tables and save your time.
NOTE: Pivot Tables are one of the INTERMEDIATE EXCEL SKILLS.
Alert: Please note that these steps might vary slightly depending on your version of Excel.
Use the “Refresh All” Button to Update all the Pivot Tables in the Workbook
The “Refresh All” button is a simple and easy way to refresh all the pivot tables in a workbook with a single click.

- Select a Pivot Table: Navigate to one of the pivot tables in your workbook by clicking on any cell within it. This selection tells Excel that your next actions will be related to pivot tables.
- Go to the Pivot Table Analyze Tab: Look toward the top of your Excel window, where you’ll see a ribbon of different tabs. Click on the “PivotTable Analyze” tab. This tab only appears when a pivot table is selected.
- Find the Refresh Drop-Down: Within the “PivotTable Analyze” tab, look for the “Data” group. Here, you’ll find a button labeled “Refresh” (click the small arrow under it to open the drop-down).
- Choose to Refresh All: After clicking on “Refresh,” a drop-down menu will appear. This menu allows you to refresh the selected pivot table or all pivot tables in the workbook. To refresh all pivot tables, click on “Refresh All”.
Apart from this, you can also use the “Refresh All” button from Data Tab ➜ Queries & Connections ➜ Refresh All (Data Tab ➜ Connections ➜ Refresh All in Excel 2013 and earlier). It also refreshes all the queries and data connections in the workbook.
Automatically Refresh All the Pivots When You Open a Workbook
If you want to refresh all pivot tables when opening a workbook, you can use the following steps to make a one-time setup.
Below are the steps to auto-refresh all pivot tables while opening a workbook.
- To start, select any of the pivot tables from the workbook. You can choose a single cell within the pivot or select the entire pivot.
- The next step is to open the Pivot Table Options, which you can do by right-clicking and then clicking “Pivot Table Options…”.

- This will open a dialog box, and you need to go to the “Data” tab. There, tick the checkbox that says “Refresh data when opening the file” and click OK.

This option belongs to the pivot cache, so it applies to all the pivot tables that share the same source data. If you have pivot tables built on different sources, repeat these steps for one pivot table of each source.
Refresh Pivot Tables with a Keyboard Shortcut
Once your Excel workbook is open, you can refresh all pivot tables by pressing Ctrl + Alt + F5. This shortcut is the same as Refresh All, so it refreshes all the pivot tables (and the queries) in the workbook. To refresh only the selected pivot table, press Alt + F5.
When you press the keyboard shortcut, Excel will refresh pivot tables in the workbook. This may take a few moments, depending on the size of your pivot tables and your computer’s processing power.
VBA Code to Update All the Pivot Tables in a Single Click
Yes, you can use VBA to refresh all pivot tables as well.
All you have to do is use the below-mentioned code.
Sub RefreshAllPivotTables() Dim ws As Worksheet Dim pt As PivotTable For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables pt.RefreshTable Next pt Next ws End Sub
- For Each ws In ActiveWorkbook.Worksheets: This line loops through every worksheet in the active workbook, one by one.
- For Each pt In ws.PivotTables: This inner loop goes through each pivot table on that sheet, and pt.RefreshTable refreshes it. (You can’t use RefreshTable on the whole PivotTables collection at once; that gives the “Object doesn’t support this property or method” error.)
- Next pt / Next ws: These lines move the loops to the next pivot table and the next worksheet.
You can also assign this macro to a button for use with a single click. And if you simply want to refresh everything in the workbook, including queries, one line does it: ActiveWorkbook.RefreshAll.
Refresh Only Specific Pivot Tables with VBA
If you want to update some specific pivot tables, then you can use the VBA code below.
Sub RefreshCustomPivotTable() With ActiveSheet .PivotTables("PivotTable1").RefreshTable .PivotTables("PivotTable2").RefreshTable .PivotTables("PivotTable3").RefreshTable .PivotTables("PivotTable4").RefreshTable .PivotTables("PivotTable5").RefreshTable End With End Sub
Change the names of the pivot tables according to your workbook.
If you want to update these custom pivot tables every time you open a workbook, change the name of the macro to auto_open and keep it in a standard module. As the code uses ActiveSheet, make sure the workbook opens on the sheet with these pivot tables, or replace ActiveSheet with the sheet name, like Worksheets(“Sheet1”).
Sub auto_open() With ActiveSheet .PivotTables("PivotTable1").RefreshTable .PivotTables("PivotTable2").RefreshTable .PivotTables("PivotTable3").RefreshTable .PivotTables("PivotTable4").RefreshTable .PivotTables("PivotTable5").RefreshTable End With End Sub
Understanding the Source Data in a Pivot Table
The source data in a pivot table refers to the original data on which the pivot table is based. This could be a range of cells, a named range, or an Excel table. The source data in the pivot table includes all the rows and columns you want to analyze.
Refreshing only reads the same source again. If your source is a fixed range like A1:D100 and you add rows below it, refresh won’t pick them up. Convert the source data to an Excel table (Ctrl + T) and use the table as the source, so new rows are included every time you refresh. To check or change the source, go to PivotTable Analyze ➜ Change Data Source.
Refresh a Pivot Table When the Source Data Changes
If you want the pivot table to update as soon as you change the data, you can use a worksheet event. Right-click the tab of the sheet with the source data, click View Code, and paste the code below. Change “Report” to the name of the sheet with the pivot table.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pt As PivotTable
For Each pt In Worksheets("Report").PivotTables
pt.RefreshTable
Next pt
End Sub
Just keep in mind that this runs after every change on the sheet, so with large data, it can slow down your typing. And for pivot tables built on a query or an external connection, you can also set a timer: Data ➜ Queries & Connections, right-click the connection ➜ Properties ➜ Refresh every N minutes.
Frequently Asked Questions
Why does my pivot table not show new data after refresh?
The new rows are outside the source range. Change the source to include them, or better, use an Excel table as the source so it grows with your data.
Why does refresh change my column widths?
By default, Excel autofits the columns on every refresh. Right-click the pivot table ➜ PivotTable Options ➜ Layout & Format, and untick “Autofit column widths on update”.
Can I refresh a pivot table on a protected sheet?
No, Excel doesn’t refresh pivot tables on a protected sheet. Unprotect the sheet first, or use a macro that unprotects the sheet, refreshes the pivot tables and protects it again.
thanks! xx
I use CTRL+ALT+F5
Dear Paneet,
I’m using Excel to monitor data with Exaquantum, to refresh data I must Select all then Delete then Undo.
Could you please provide me with a simple trick to refresh my data easily.
Many thanks
Regards
Imad
not working this method
Comments:
If ‘separate’ pivot tables then simple – data refresh all is simplest
If pivot tables created ‘linked’ using same data then refresh on one pivot table sufficient to enable all associated pivot tables to be updated. (good practice)
Option to have refresh set when opening file although it appears simple solution it can and ‘does’ result in a delay in opening files, hence first option ‘data refresh all’ avoids this problem.
Actually I found it useful to add the following code added to the Worksheet Activate event for every sheet containing pivot table/s:
>>>>>>>>>>>
For Each pt In ActiveSheet.PivotTables
pt.PivotCache.refresh
Next pt
>>>>>>>>>>>>>>>>>>>>>>>>>
This is also my usual method. Means you never look at a pivot table that is out of step with the data. Pity Excel does not give you that option natively, like it does at “open” time.
I’ve had refresh issues with power query and a pivot table whereby the details of the pivot table was cleared out with your suggestions. The only option was to use power pivot. I am guessing it is a sequence thing. Have you had any experience with this?
Not Really. Which version of Excel you are using?
Tanks alot.
@Beth
There’s just a minor typo in it. “PC” needs to be on the next line, so it reads “PC.Refresh”
Sub RefreshAllPivots()
Dim PC As PivotCache
For Each PC In ActiveWorkbook.PivotCaches
PC.Refresh
Next PC
End Sub
This code:
Sub RefreshAllPivots()
Dim PC As PivotCache
For Each PC In ActiveWorkbook.PivotCachesPC
.Refresh
Next PC
End Sub
Returns “Compile error: Invalid or unqualified reference” on the .Refresh
Please check the link to “30 Pivot Table Tricks”: Page not found.
Carlos
Refresh All is just wonderful. Thanks.
I’m so glad you liked it. 🙂