fbpx
Search
Close this search box.

Refresh All Pivot Tables at once [Quick Tip]

Share

Facebook
Twitter
LinkedIn

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:

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

41 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

  26. James says:

    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

  27. mayur says:

    how to add data if the report on daily basis and i need pivot to update on every friday

  28. Kim Kempf says:

    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.

  29. Brij Arora says:

    @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

  30. raj says:

    Superb and simple solutions. Very helpful.

  31. Maria says:

    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.

  32. Ashok says:

    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

  33. David Betts says:

    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

Leave a Reply