Pivot Tables are an integral part of Excel based reports. So it is no surprise that many of create multiple pivot reports in one Excel workbook.
So when the underlying data changes, we often spend time refreshing individual pivot tables. Today, I want to share a quick tip about how to refresh all pivot tables at once.
Refreshing All Pivot Tables in One Click:
- Go to Data Ribbon
- Click on Refresh All button (or press CTRL+ALT+F5)
- That is all!
This simple step can update all pivot tables and data connections in one go. What a time saver this is.
One line Macro to Refresh All Pivot Tables
If you want to use VBA (Macros) to refresh your pivot tables, the code is equally silly. Just add this line:
ActiveWorkbook.RefreshAll- or use
Workbooks(1).RefreshAllto refresh Pivots & Connections in first workbook
Do you use RefreshAll?
Ever since I learned about Refresh All, I have been using it in all my reports & dashboards. In fact, I even use the ActiveWorkbook.RefreshAll statement in my macro-enabled reports to automatically refresh all pivot tables during report generation.
What about you? Do you use Refresh All feature? What other Pivot Table Tricks you know? Please share your experience & tips using comments.
41 Responses to “Refresh All Pivot Tables at once [Quick Tip]”
Hello Chandoo,
I found some cases were RefreshAll (either via VBA or Excel Menu) is unapplicable. We manage some excel books containing about 20 pivots, each with loads of data.
Refresh time is way toooooo long in that case with older PC.
Instead, we use a VBA macro that refresh all pivots in the sheet beeing activated, using the OnActivate event function.
Regards.
Anyone know a Pivot Table trick for defaulting new values fields to "Sum Of" instead of "Count Of"? It's a pain to adjust the Summarize Data By every time I add a field.
Pivot choose count of when there is null values in data and sum of when column have numeric data (no null values).
Alternatively you replace your null values with 0 to get desired sum of
How about changing the data source in one step? I frequently have multiple pivot tables off of the same set of data. If new data is added to the file, I have to change the source data for each table. I'd prefer not to inlude the entire column when setting the data source.
Name the range, then use the "name" in the pivot table instead of the range. As long as the named range expands to include all of your data, you will never have to change the source.
What if my data source is MS Access Queries?
Currently, whenever I added new fields in my query, I need to go to excel to add the new fields one by one manually. Are there any VBA can automate that?
Dude, any shortcut in Excel 2003 for the same ? in my office, there are a lot of pivots floating around. Unfortunately, we cannot migrate to 2007 even !! 🙁 pls help i need it badly... @Lianna : I am looking for the same here...
Steve - easiest way is to change your data source to a data table (Insert > Table on the ribbon). Then the data source becomes "Table1" instead of A1:AA10000000 and automatically adds new rows when you click refresh
@Steve... Tables are your best for data source updation troubles, as Lianna suggested.
@Lianna.. you can write a simple macro to fix this anomaly.
@Kapil... I don't think this feature is available in Excel 2003.
@Liana:
It's VBA, but some of the easiest VBA you'll encounter:
1. Start up your macro recorder
2. Change the aggregate
3. Snag the generated code.
4. Hack.
-------
With ActiveSheet.PivotTables("PivotTableName").PivotFields("CurrentFieldName")
.Caption = "New Field Name"
.Function = NewFunction
--------
Literally, you can reuse that same chunk of code over and over again.
@Steve:
I tend to create one pivot table (creating one pivot cache) and link all subsequent tables to use that cache. Then, just like everybody else says: leave the source to the pivot table as a dynamic range or a table. This will almost always require the occasional refresh of the pivot table. This will deal with shrinking or growing data sources. If you're trying to swap out a data source entirely, it might be a different solution.
@Kapil: try this
http://www.ozgrid.com/VBA/pivot-table-refresh.htm
Also I'm using refresh All Pivot tables in Excel.
But some time I need refresh a specific pivot table in VBA
below code a good example for this.
Worksheets("Day").PivotTables("PivotTable1").PivotCache.Refresh
Hello Steve:
if you use import data method for creating a pivot table, you don't have to change data range in the pivot tables. When your source excel file change, your pivot table changes
in excel 2003.
Data / Import External Data / Import Data
"Chose your data file" and click "Create a pivot table report"
Thank you for this tipp, untill now, I refreshed with this command: Selection.QueryTable.Refresh BackgroundQuery:=False
Now, it is a lot easier!
When i had to refresh a pivot's source data through VBA, i would actually delete the columns containing the pivot and then redo the pivot through code.
Does anyone know a quick way or the vba required to change the month in all pivot tables in a spreadsheet?
Would be really helpful to do this at the start of the month. Thanks
Dear Chandoo,
this (CTRL+ALT+F5) or Refresh all data is very close to my heart. I start using it for a 1 and a half year back with external data. My company's all reporting structure is based on this. Earlier I used to connect external data to a sheet and sheet would be named as base data and concerned pivot would be made on the basis of the same. But when you Press the Refresh all data button it will only refresh the external data in base data sheet but not the pivot table for the same either we have to refresh the pivot table by right clicking on it or Pressing refresh data button one more time. (this occurs in MS-Office 2007 and 2010 as well.)
PS : I try to mention to refresh pivot generated through external data sheet requires pressing Refresh All data twice.
Ben:
If Month is selected by filter, you can just use VBA to change the filter as a regular cell reference. Easiest way would be with a list of months in a drop down box. Add a macro to the drop down box that changes the actual cell where the filter is contained to the desired value.
For an example, check out this post: http://chandoo.org/wp/2011/08/10/mlb-pitching-stats-dashboard/
The jerk who made it used named ranges instead of just A1, B1, C1 cell references so it's obscured through the names, but you'll get the idea:
Sub DropDown2_Change()
[PvtTeamsYEARfltr].Value = [usyear].Value
end sub
Hi All,
I love Refresh All but bear in mind that it refreshes data connections as well, not just the pivots. As such it isn't always appropriate.
@Lianna :
Excel reads yoyur data, if it is all numeric it defaults to Sum, if not it defaults to Count. Change blank cells to zero value in your data source and you'll never have that problem again, but I know that this isn't always possible.
@Steve:
Tables are great if your excel supports them, but if not use dynamic named formula using offset(Start Cell, Offset Rows, Offset Columns, width, height). By using Counta() for the width & height you can get the range to dynamically expand & contract as required.
Hope that helps.
M
@Mawdo81 and dan l - Thanks! I'll try both ideas out and see which one sticks.
The Refresh All button is unsuitable with a Table linked to a Database, when Offline.
The 'Refresh all' button gets "stuck" when it tries to 'Refresh all Pivottables and Datatables'.
Basically I'd like a Refresh All button with 3 alternatives:
a) Refresh all Datatables, b) Refresh all Pivottables, c) Refresh all Data-and Pivot-tables.
Thanks all for the feedback on changing the data source
Hi, anyone got bond optimiser template. perhaps assist me on how to use efficient frontier via excel
How do you refresh pivot table using excel 2003?. It would be nice if when you post tips you do so for Excel 2003 and 2007.
Thanks for this Tip. I had never heard about it before.
I run 2003, and did a little digging. Yes! The 'Refresh All' command is in 2003! There is a toolbar button to do this.
Go into Tools-Customize. Select the Data category (Commands tab). Scroll to the bottom of the Commands. 'Refresh All' is two below the 'Refresh' command. Drag Icon to the menu or toolbar of your choosing.
The keyboard shortcut (Ctl+Alt+F5) does not work in 2003. I'm happy, nevertheless, that I have this "new" tool! Thanks!
Brilliant !
Thanks a lot. Big help with this
Hi,
is there anyway to update all pivot tables to include range for one source sheet, at one?
@DudleyB - Thanks for the refresh all tip.
I suggest formatting your underlying data as a Table. Select all of your data and go to Insert-->Table. Then make your pivot table(s) from that. All of your tables will show the source as "Table1" instead of a range. If you add more data to your file or add more columns, it will automatically pick it up and include it as part of Table1.
This tip is great...I had problem with updating all the pivots table..but rename the range or use Table as a format...is super :). Thank you guys for sharing:). I love this tip and eagerly waiting to try it tomorrow.
I am trying to refresh a pivot table and then select all of the data, minus the (blank). I can refresh it with the code I have now, and get rid of the blanks, but I cannot select all of the data before doing so (unless manually). An error 1004 pops up that it is "unable to get the pivotitems property of the PivotField class". Here is the code I have below:
Range("B7").Select
ActiveWorkbook.RefreshAll
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Brand")
.PivotItems("(All)").Visible = True
.PivotItems("(blank)").Visible = False
End With
End Sub
Hi,
Is there a way to do the exact opposite ?
Prevent the user from using the RefreshAll command ... because of very sensitive Pivot Tables ?
Thanks
how to add data if the report on daily basis and i need pivot to update on every friday
@Mayur
You can do the same by ticking the option "Refresh while opening workbook" at Data Connection Tab.
I have an excel report that is connected to a database and a pivot table depdendant on that report. What order does refresh all refresh? If the pivot is dependant on the report connected to a database, and the pivot is refreshed first, the data in the pivot will not be accurate.
@Kim
in order to achieve the same start recording macro.
1. press Ctrl+shift+F5 (this will refresh data connections)
2. right click on pivot and choose refresh button.
stop recording macro.
assign this to any button.
whola now you are done.
Brij
@ Brij Arora. Wonderful solution. Thank you!!!
@Kim Kempf
alternatively you can select pivot table instead of data table .
choose pivot table option which can be refreshed by right clicking on the same.
Brij
Ctrl+alt+F5 is not working in excel 2013
Superb and simple solutions. Very helpful.
I have the same question James had - is there a setting to cancel automatic Refresh ALL ? My file has a tab with data + several tabs with pivot tables based on this data. When I manually refresh pivot table in one tab, it automatically refreshes pivot tables in all tabs in this file.
How can I change the setting? I think I remember choosing this as a setting when I created these pivot tables, but I don't see a simple way to cancel it.
Hi Chandu,
Quick question: so if I use Refresh All, would it:
1) Refresh all the pivots only in current tab or across the workbook
2) Will it Refresh all pivots with their source coming from different data sets or will it refresh all pivots coming from the same source?
I have 3 tabs in a file and several pivots coming out of those 3 tabs data. I want to know if Refresh All will refresh all the pivots across the work book from all 3 tabs data.
Thanks!! Ashok
On a powerpivot worksheet with an external data connection I have to press refresh all twice. Because it would seem that it refreshes the pivot tables before the external data connection. So i then use vba to refresh all twice. No luck. So i then refresh the external connection first prior to refresh all. And i still have to run it twice. Any ideas? Thanks. Dave