Refresh All Pivot Tables at once [Quick Tip]

Posted on September 19th, 2011 in Learn Excel , VBA Macros - 29 comments

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:

Refresh All Pivot Tables at Once

  • 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).RefreshAll to 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.

More Quick Tips:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

29 Responses to “Refresh All Pivot Tables at once [Quick Tip]”

  1. Cyril Z. says:

    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.

  2. Lianna says:

    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.

    • Brij Arora says:

      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

  3. Steve says:

    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.

    • Michael says:

      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.

      • John Chi says:

        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?

  4. Kapil says:

    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…

  5. Lianna says:

    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

  6. Chandoo says:

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

  7. dan l says:

    @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

  8. Ulas says:

    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

  9. Ulas says:

    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”

  10. SomeintPhia says:

    Thank you for this tipp, untill now, I refreshed with this command: Selection.QueryTable.Refresh BackgroundQuery:=False

    Now, it is a lot easier!

  11. Maria says:

    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.

  12. Ben says:

    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

  13. Brij Arora says:

    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.

  14. dan l says:

    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

  15. Mawdo81 says:

    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

  16. Lianna says:

    @Mawdo81 and dan l – Thanks! I’ll try both ideas out and see which one sticks.

  17. Ola S says:

    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.

  18. Steve says:

    Thanks all for the feedback on changing the data source

  19. azrul says:

    Hi, anyone got bond optimiser template. perhaps assist me on how to use efficient frontier via excel

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

  21. DudleyB says:

    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!

  22. divya says:

    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.

  23. Steve says:

    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.

  24. Jevi says:

    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.

  25. Ali says:

    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

Leave a Reply