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.

10000+ Copies Already Downloaded

How To Refresh All Pivot Tables At Once Using Different Methods

Refresh All Pivot Tables At Once

Pivot Tables are one of the best tools to make sense of your data. Last week when I was in a conversation with John Michaloudis about his Most Comprehensive Pivot Table Course. He told me about his Million Dollar Advice for pivot table users.

“Put Your Data In An Excel Table”, John Michaloudis

I hope you are doing well with your Pivot Tables. In this tutorial, I’ll show you some quick methods to refresh all pivot tables at once in your worksheet. It happen sometimes when you create more than one Pivot Table from a single source or from different sources, you have to refresh them manually one by one. But there are some simple ways to Refresh All Pivot Tables in a single click.

Here I have listed some methods to Refresh All Pivots.

Refresh All Pivot Tables Manually At Once

To refresh all the pivot tables in your workbook at once.

  • Go to Data Tab → Connections → Refresh All.

Refresh All Pivot Tables Manually With Single Click

  • Or you can use the Shortcut Key Alt + F5 to refresh all pivot tables in excel.

Important Note: Using this method will also update all the external links.

Refresh All Pivot Tables Everytime When You Open A File

If you want to refresh all pivot tables on opening a workbook, you can use following steps to make one time setting for that.

Refresh All Pivot Tables On Opening A File

  • Select any of the pivot tables from your workbook.
  • Right click on it & select “PivotTable Options“.
  • Go To Data Tab → Tick Mark “Refresh Data When Opening A File”
  • Click OK.

Refresh All Pivot Tables Using VBA

Yes, you can use VBA as well to refresh all pivot tables. All you have to do is just use below mentioned VBA code.

Sub RefreshAllPivots()
 Dim PC As PivotCache
 For Each PC In ActiveWorkbook.PivotCaches
 PC.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

If you have some specific pivot tables which you want to update you can use below VBA code to do that.

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. This macro will run every time when your workbook.

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

Now you know about different ways to Refresh All Pivot Tables at once. You can use any of above-mentioned methods which you feel convenient for you.

If you have any other crazy method to refresh all pivot tables in excel. Please Share that method with me in the comment box.

And, if you like this tip please share it with your buddies on Facebook | Twitter |  Google+ | LinkedIn

Some Other Tips To Play Smart With Your Pivot Tables

Back To Blog