How to Refresh All Pivot Tables in Excel: 3 Ways

Method 1 – Applying the Refresh All Command to Refresh All Pivot Tables in Excel

Step 1:

• Look at the pivot tables; the Grand Total of the Revenue Earned and the Grand Total sum of Quantity is $13,30,000 and 39.

• Insert Sales Quantity and Revenue Earned by Tom in Row 13 in the main worksheet.

• Insert data in the original worksheet, pivot tables remain the same.

Step 2:

• To update pivot tables, from Data Tab, go to,

Data → Queries & Connections → Refresh All

• Clicking on the Refresh All menu, you’ll be able to refresh all pivot tables to the entire worksheet like on the screenshot below.

Method 2 – Clicking on Mouse to Refresh All Pivot Tables in Excel

Steps:

• Insert Sales Quantity and Revenue Earned by John in Row 13 in the main worksheet.

• Insert data in the original worksheet, the data in pivot tables would not change.

• After that, press Right-Click on your Mouse. After performing the Right-Click on your Mouse, a window pops up.

• Click on Refresh and you will be able to refresh the pivot tables like in the screenshot below.

Method 3 – Running a VBA Code to Refresh All Pivot Tables in Excel

Steps:

• Open the VBA window by Pressing the Alt+F11 key simultaneously.

• A window named Microsoft Visual Basic Applications will appear in front of you.

• From the Microsoft Visual Basic Applications window, go to,

Insert → Module

• A new module pops up. Type the below VBA Code in the window. We provided the code here, you can just copy-paste the code and use it in your worksheet.

Sub RefreshAllPivotTables()
ActiveWorkbook.RefreshAll
End Sub

• Go back to your worksheet, and our job is done!

Things to Remember

You can use Ctrl + Alt + F5 in lieu of the Refresh All command to refresh all pivot tables

If the Developer ribbon does not show in the Menu Bar, then go to,

Insert → Right-Click on Mouse → Developer → Ok

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Frequently Asked Questions

How do I refresh a pivot table every 5 minutes?

To refresh a Pivot Table every 5 minutes in Excel:

1. Open your Excel workbook.
2. Right-click on the Pivot Table.
3. Select “PivotTable Options.”
4. Under the “Data” tab, set the “Refresh data when opening the file” option.
5. Go to the “Advanced” tab and set the “Refresh data every” option to 5 minutes.
6. Click “OK” to apply the changes.

How do I refresh all Pivot Tables in Excel simultaneously?

To refresh all Pivot Tables at once, go to the “Data” tab, and click on “Refresh All” in the “Connections” group. Alternatively, you can use the keyboard shortcut “Alt + Ctrl + F5.”

Can I refresh Pivot Tables automatically when opening an Excel file?

Yes, you can. Right-click on a Pivot Table, select “PivotTable Options,” go to the “Data” tab, and check the “Refresh data when opening the file” option.

Related Articles

• How to Auto Refresh Pivot Table in Excel
• How to Update Pivot Table Range
• How to Refresh All Pivot Tables with VBA