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.


Download Practice Workbook

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


3 Suitable Ways to Refresh All Pivot Tables in Excel

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.

Refresh All Pivot Tables


1. Apply 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 Grand Total the sum of Quantity is $13,30,000 and 39 respectively.

Apply the Refresh All Command to Refresh All Pivot Tables in Excel

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

Apply the Refresh All Command to Refresh All Pivot Tables in Excel

Step 2:

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

Data → Queries & Connections → Refresh All

Apply the Refresh All Command to Refresh All Pivot Tables in Excel

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

Apply the Refresh All Command to Refresh All Pivot Tables in Excel

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


2. Use the 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.

Use the Mouse to Refresh All Pivot Tables in Excel

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

Use the Mouse to Refresh All Pivot Tables in Excel

  • 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. Run 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.

Run a VBA Code to Refresh All Pivot Tables in Excel

Run a VBA Code to Refresh All Pivot Tables in Excel

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

Insert → Module

Run a VBA Code to Refresh All Pivot Tables in Excel

  • 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


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

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo