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: Automatically Update a Pivot Table When Source Data Changes in Excel
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
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
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.
Frequently Asked Questions
How do I refresh a pivot table every 5 minutes?
To refresh a Pivot Table every 5 minutes in Excel:
- Open your Excel workbook.
- Right-click on the Pivot Table.
- Select “PivotTable Options.”
- Under the “Data” tab, set the “Refresh data when opening the file” option.
- Go to the “Advanced” tab and set the “Refresh data every” option to 5 minutes.
- 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
