Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

Refresh All Pivot Tables

A pivot table is one of the best things that I have ever used. I love the thing that I don’t need to insert it again and again.

It saves a lot of time because once you create a pivot table, you just need to refresh it with a single click when you update it and it’s a one-second job.

The problem is when you have multiple pivot tables. You need to spend one second to refresh each pivot table.

That’s huge.

And, that’s why we should have a method to refresh all the pivot tables at once.

So today, in this post, I’d like to show you 3 simple methods which you can use to refresh all the pivot tables with a single click at the same time.

So let’s get started.

1. With "Refresh All" Button

"Refresh All" button is a simple and easy way refresh all the pivots tables in the workbook with single click.

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

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 your ton of time. You don’t need to go each pivot and then refresh it.

And, with VBA you can also refresh selected pivot tables.

From all the methods which we have above, you can anyone which you think is a perfect for you. Or, you can use all of them according to the situation you have.

I hope tip will help you to get better at Excel.

😊

Now, tell me one thing. Have you ever tried to update all the pivot tables at once? Do you have any other method to do this? Please share your views with me in the comment section, I would love to hear from you.

And, please don’t forget to share this tip with your buddies. I’m sure they will appreciate it.

Must Read Next

  1. Methods to Automatically Update a Pivot Table Range
  2. How to Use Conditional Formatting in a Pivot Table
  3. How to Connect Slicer With Multiple Pivot Tables
  4. 30 Pivot Table Tips
  5. How to Create a Pivot Table from Multiple Worksheets
  • Raman Girdhar

    Refresh All is just wonderful. Thanks.

    • Puneet Gogia

      I’m so glad you liked it. 🙂