• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Auto Refresh PivotTables

You are here: Home / Excel PivotTables / Auto Refresh PivotTables
July 22, 2021 by Mynda Treacy

In this tutorial we’re going to look at the options we have available to auto refresh PivotTables in Excel without using VBA. The process differs depending on whether you use Power Query to get the data or not. I’ll also show you the VBA method required for regular PivotTables. Beware because not all PivotTables based on Power Query data will auto refresh.

Watch the Video

Subscribe YouTube

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsm file please ensure your browser doesn't change the file extension on download.

1. Power Query Data Loaded to Data Model

PivotTables created from data loaded to the Power Pivot Data Model via Power Query can be automatically refreshed. The automatic refresh settings are applied in the Query settings, which is also available via the Queries & Connections pane:

Auto Refresh PivotTables Loaded to Data Model via Power Query

2. Power Query Data Loaded to Pivot Cache

Regular PivotTables created based on data you get with Power Query and then load to a PivotTable or Pivot Chart can also be automatically refreshed. You must select either PivotTable Report or PivotChart in the Import Data dialog box:

Load query to PivotTable Report or Chart

Note: these options are not available in earlier versions of Excel.

And then set the refresh frequency in the Query Properties as per the previous example.

3. Power Query Data Loaded to Table

When you load data from Power Query to a Table and then create a PivotTable from said table you cannot use the automatic refresh settings. This is because the PivotTable will refresh before the query has time to finish loading the updated data to the Table that the PivotTable is connected to.

PivotTable based on query table

If you intend to build a PivotTable from the query data, avoid loading the query to a Table. Instead, load it direct to the PivotTable or Pivot Chart as shown in the previous example.

If you must load it to a Table first, then you can use the VBA technique described in example 6.

Now strictly speaking, best practice is to use Power Query to get your data and load it to the Power Pivot Data Model or to the Pivot Cache, however because you're able to bypass Power Query, we'll look at those options next.

4. Data Loaded Direct to Data Model

Power Pivot PivotTables created by loading your data to the Power Pivot model either via the Add to Data Model button on the Power Pivot tab:

Load to Data Model

Or by checking the Add to data model button when creating a new PivotTable:

Add data to Data Model

Can be automatically refreshed via the settings in the Connection Properties. There you can set the refresh to as often as every minute or refresh data when opening the file:

Auto Refresh PivotTables Loaded to Data Model

5. Data from External Source

If you create a regular PivotTable by connecting to an external source, whether that’s another Excel file, text or CSV files, or a database, you can set the auto refresh frequency in the Connection Properties as we saw in the first example.

PivotTable from external source

Auto Refresh PivotTable Warning

Careful, each time the auto refresh triggers, Excel becomes temporarily unresponsive and takes focus away from the cell or object you’re working on. This can be super annoying if you’re entering data or making other changes to your file when the refresh triggers, so don’t get carried away setting the refresh frequency too often.

6. Auto Refresh PivotTables with VBA

If you’ve built a regular PivotTable that’s based on data stored in your current file, then the only option for automatic refresh is to use VBA. This is useful if users are entering data into the table that your PivotTable is based on.

It relies on two key elements:

  1. Your source data must be on a separate sheet to that of your PivotTables
  2. Moving from the source data sheet to another sheet in the file triggers the VBA to refresh the PivotTable.
There are two VBA components:  
  1. The code that triggers the VBA to execute when the source data sheet is deselected:

VBA to trigger PivotTable refresh

This goes into the module in the VB editor for the sheet(s) containing your source data.

  1. The code that refreshes all PivotTables in the file:

VBA to refresh All PivotTables

This goes into the module for “ThisWorkbook” in the VB editor.

Auto Refresh PivotTable Notes

The file must be open for refresh to occur, so keep this in mind if you’re referencing the PivotTable from another file.

More Excel PivotTables Posts

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting

Excel Slicer Formatting

Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you the tricks to make Excel Slicers small.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.

Excel PivotTable Percentage Change

Excel PivotTable Percentage Change calculation is dead easy with Show Values As. Add conditional formatting, and Slicers for interactivity.


Category: Excel PivotTables
Previous Post:power pivot running totalPower Pivot Running Total
Next Post:VLOOKUP in Power Query Using List Functionsvlookup in power query using list functions

Reader Interactions

Comments

  1. Fran Cammock

    September 16, 2021 at 7:17 pm

    Thank you very much for the VBA code to refresh pivot tables and for this website, it is so helpful.

    Reply
    • Mynda Treacy

      September 16, 2021 at 8:49 pm

      Great to hear, Fran!

      Reply
  2. Lawrence Heltzer

    July 23, 2021 at 6:25 am

    Thank you so much for your YouTube videos. So very help and insightful!

    Question regarding the VBA to auto update pivot tables. I am curious why you use the worksheet deactivate event rather than worksheet calculate on the worksheet module. I have not tested your method and whether it works with multiple sheets open in the same workbook or it’s just plain the better method.

    Thanks again,

    Lawrence

    Reply
    • Philip Treacy

      July 23, 2021 at 11:23 am

      Hi Lawrence,

      There are a couple of reasons. If you manually change some data on the source data sheet like changing 2017 to 2016 on a line, Worksheet_Calculate isn’t triggered so your PT’s wouldn’t be updated.

      If you did have calculations on your source data sheet, if they were changed that would fire Worksheet_Calculate potentially causing your PT’s to be updated multiple times (every time a calculation is made), which is unnecessary.

      Using Worksheet_Deactivate fires the PT update macro when you leave the sheet – and the assumption here is that you have finished changing the source data. In this approach the PT’s are only updated once.

      Yes, the macro does refresh all PT’s in all sheets of the active workbook.

      Regards

      Phil

      Reply
  3. Ken McMillan

    July 23, 2021 at 2:18 am

    Thanks

    Reply
    • Philip Treacy

      July 23, 2021 at 11:07 am

      You’re welcome.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.