How to Refresh All Pivot Tables in Excel (3 Ways)

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. Today, In this article, we’ll learn three ways including VBA Macros to refresh all pivot tables in Excel with appropriate illustrations.

When creating a new Pivot Table, you are asked if you want it to be based on another table. If you choose no, this Pivot Table will have its own cache and the original data will be doubled in size. If you choose yes, your WorkBook will be smaller, but you will be adding to a group of Pivot Tables that share a single cache. When you refresh any single Pivot Table in a collection, it refreshes the entire collection. Here’s an overview of the dataset for today’s task.

1. Applying the Refresh All Command to Refresh All Pivot Tables in Excel

While inserting new data in the original worksheet, pivot tables can not change that data. Please follow the steps below.

Step 1:

• First of all, 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 respectively.

• Now, we’ll insert Sales Quantity and Revenue Earned by Tom in Row 13 in the main worksheet.

• As we insert data in the original worksheet, pivot tables remain the same.

Step 2:

• At this time, to update pivot tables, from Data Tab, go to,

Data → Queries & Connections → Refresh All

• While clicking on the Refresh All menu, you’ll be able to refresh all pivot tables to the entire worksheet that has been given below screenshot.

Read More: How to Automatically Update a Pivot Table When Source Data Changes

2. Clicking on Mouse to Refresh All Pivot Tables in Excel

In this method, we’ll learn how to refresh all pivot tables in Excel by using the Mouse. Let’s follow the instructions below to learn!

Steps:

• First, insert Sales Quantity and Revenue Earned by John in Row 13 in the main worksheet.

• Though we 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.

• Now, from that window, simply click on Refresh and you will be able to refresh the pivot tables which have been given in the below screenshot.

Read More: Pivot Table Not Refreshing (5 Issues & Solutions)

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

Obviously, refreshing all pivot tables by using the VBA(Visual Basic applications) Macros is the easiest way. To run a VBA code for refreshing all pivot tables, follow the instructions below.

Steps:

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

• Now, a window named Microsoft Visual Basic Applications will appear in front of you.

• Now, from the Microsoft Visual Basic Applications window, go to,

Insert → Module

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

Sub RefreshAllPivotTables()
ActiveWorkbook.RefreshAll
End Sub

• After that, go back to your worksheet, and our job is done!

Read More: How to Auto Refresh Pivot Table without VBA in Excel (3 Smart Methods)

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.

Conclusion

I hope all of the suitable methods mentioned above to refresh all pivot tables will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Further Readings

• How to Refresh Excel Sheet Automatically (3 Suitable Methods)
• [Fixed!] Excel Cells Not Updating Unless Double Click (5 Solutions)
• How to Disable Background Refresh in Excel (2 Handy Methods)
• Excel VBA: Turn Off Screen Update
• [Solved]: Excel Formulas Not Updating Until Save (6 Possible Solutions)
• How to Refresh Chart in Excel (2 Effective Ways)