By refreshing all the pivot tables at once you can save a lot of time.
Yes, that’s right and here’s the thing.
When you have multiple pivot tables and you have to spend one second to refresh each pivot table.
Top 3 Ways to Refresh all the Pivot Tables Automatically
- Using “Refresh All” Button
- While Opening a Workbook
- By using a VBA Code
…so without any further ado let’s learn these methods.
Get Better at Excel in 2018
A bundle of E-Books you need to thrive in Excel this year.
(1). With "Refresh All" Button
"Refresh All" button is a simple and easy way refresh all the pivots tables in a workbook with single click.
All you need to do it is Go to Data Tab ➜ Connections ➜ Refresh All.
(2). While Opening a File
If you want to refresh all pivot tables on opening a workbook, you can use following steps to make one time to set up for that.
Here are the steps:
- Select any of the pivot tables from your workbook.
- Right click on it and select “PivotTable Options".
- Go To Data Tab → Tick Mark “Refresh Data When Opening A File”.
- Click OK.
(3). Using a VBA Code
Yes, you can use VBA as well to refresh all pivot tables. All you have to do is just use below-mentioned code.
Dim PC As PivotCache
For Each PC In ActiveWorkbook.PivotCachesPC
Above code will refresh all the pivot tables from your active worksheet.You can also assign this macro to a button to use in a single click.
(3.1). Refresh Only Specific Pivot Tables With VBA
If you have some specific pivot tables which you want to update then you can use below VBA code.
Change the name of the pivot tables as per your workbook.
And, if you want to update these custom pivot tables every time when you open a workbook, change the name of the macro to auto_open.
Refreshing all the pivot tables at the same time can save you a ton of time so, you don’t need to go each pivot and refresh it.
From all the methods which we have above, you can anyone which you think is perfect for you.
And with the VBA code, which I have shared above, you can also refresh selected pivot tables in one go.
I hope you found this tip useful but now you need to tell me one thing.
Do you know any other method for this?
You must Read these Next
- Connect a Single Slicer with Multiple Pivot Tables: Let’s say you are working on a dashboard where you are using multiple pivot tables...
- Automatically Update a Pivot Table Range: Every time when you add new data in the source sheet you need to update the source range...
- Group Dates in a Pivot Table : Let’s say you want to group all the dates as months, so instead of adding a different column...
- Pivot Table Timeline: With a timeline filter is you don’t need to open the filter again and again, you just do it with a...
- Pivot Table using Multiple Files: Sometimes we receive or capture our data in different workbooks and in that case...
- Ranks in a Pivot Table: One of the common practices for analyzing data is by ranking them and in a pivot table...
- Running Total in a Pivot Table: One of the things which I like about a pivot is we can easily add running total into it...