How to Refresh All Pivot Tables at Once in Excel

​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

  1. Using “Refresh All” Button
  2. While Opening a Workbook
  3. 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.

Refresh All Pivot Tables Manually With Single Click

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

Refresh All Pivot Tables On Opening A File

​Here are the steps:

  1. Select any of the pivot tables from your workbook.
  2. Right click on it and select “PivotTable Options".
  3. Go To Data Tab → Tick Mark “Refresh Data When Opening A File”.
  4. 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.

Sub RefreshAllPivots()
Dim PC As PivotCache
For Each PC In ActiveWorkbook.PivotCachesPC
.Refresh
Next PC
End Sub

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.

Sub RefreshCustomPivotTable()
With ActiveSheet
.PivotTables("PivotTable1").RefreshTable
.PivotTables("PivotTable2").RefreshTable
.PivotTables("PivotTable3").RefreshTable
.PivotTables("PivotTable4").RefreshTable
.PivotTables("PivotTable5").RefreshTable
End With
End Sub

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

Sub auto_open()
With ActiveSheet
.PivotTables("PivotTable1").RefreshTable
.PivotTables("PivotTable2").RefreshTable
.PivotTables("PivotTable3").RefreshTable
.PivotTables("PivotTable4").RefreshTable
.PivotTables("PivotTable5").RefreshTable
End With
End Sub

Conclusion

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. 

D​o you know any other method for this?​

You must Read these Next

  1. Connect a Single Slicer with Multiple Pivot Tables​: Let’s say you are working on a dashboard where you are using multiple pivot tables​...
  2. Automatically Update a Pivot Table Range​: Every time when you add new data in the source sheet you need to update the source range...
  3. 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...
  4. 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...
  5. Pivot Table ​using Multiple Files: Sometimes we receive or capture our data in different workbooks and in that case​...
  6. Ranks in a Pivot Table: One of the common practices for analyzing data is by ranking them and ​in a pivot table...
  7. 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​...

2018-07-13T09:45:06+00:00
  • Carlos Mario Castaño says:

    Please check the link to “30 Pivot Table Tricks”: Page not found.

    Carlos

  • Raman Girdhar says:

    Refresh All is just wonderful. Thanks.

    • Puneet Gogia says:

      I’m so glad you liked it. 🙂

  • >