Auto Refresh PivotTables & Queries - without VBA!

Auto Refresh PivotTables isn’t on by default, and the process differs depending on whether your data is loaded to the data model or not.

0:26 Auto refreshing data loaded to Power Pivot via Power Query
1:36 Auto refreshing data loaded direct from Power Query to regular PivotTables/Charts
2:37 Auto refreshing data from Power Query to a Table to a PivotTable
3:41 The problem with building PivotTables from query tables
4:19 Auto refreshing data loaded direct to Power Pivot
5:25 Auto refreshing regular PivotTables with external source data
7:02 Auto refreshing regular PivotTables from data in current file with VBA

[vijayarjunwadkar]

Thanks Mynda for this very useful video! You always bring something innovative to help us solve real world problems!

[wayneedmondson]

Hi Mynda. Great tips for updating Pivot Tables and Queries! Thanks for sharing :)) Thumbs up!!

[wilsona]

It's very useful, indeed! Thank you Mynda!

[CalBeMe]

Thank you!! I have been wrestling with the "PQ to Table to PT" scenario and wondering why the refreshes are not dependable or require multiple refreshes. First time to hear this information. 👍

[successcentre]

Excellent practical tutorial as always

[darrylmorgan]

Hi Mynda!Really Helpful Tutorial..Thank You :)

[teoxengineer]

Mynda, thank you for the great explanation.
Even though the automatic update feature with VBA is excellent, it should not be forgotten that the operations made in VBA cannot be undone. For example, after a change in the source table, the automatic update (with VBA) in the pivot table causes a previously entered data to not be recalled, so the best and correct solution is "right click, update" ;))

[CeliaAlvesSolveExcel]

Hi, Mynda! Thank you for demonstrating and detailing all these options. I learned a couple of things and helped me revisit a few concepts.
That issue (around min 4:00) of having to click refresh all twice to get the pivot table updated after the query table, has an easy solution: on the query properties unchecked "enable background refresh". Having this check mark put on can cause a couple of issues; and having to click refresh all twice is one of them.

[SyedHussain-lexg]

nice, I was looking for it ... was badly stuck with one of my sheet some time back... thanks... starting watching now...!!!

[victor]

Thanks for sharing your knowledge....amazing

[learnspreadsheets]

Cool! I didn’t know those properties tricks! I do wish though that it was built in auto refresh like google sheets has, where it just feels like a formula that refreshes on demand

[SimantovP]

Amazing vba code for instant updating pivot tables according to source. Thank you

[careyjonker]

Your tutorials have changed my life! THANK YOU! Especially the project management dashboard! Please help on the VBA - I used the last method in this tutorial. To auto refresh the pivot table. It worked once, and then kept giving me a Run Time Error "1004" thereafter. Id be so grateful for some guidance to resolve this.

[MlguelM]

Excelente contenido Mynda! no sabía estas propiedades, muchas gracias!

[Bforzajuve]

Great video as usual Mynda. Thank you for the tips. Unrelated to this I have a question about Power Query. Can you please make a video about lookup with wildcards in Power Query. Should I use the fuzzy matching or there is another way?

[jimfitch]

Hi, Mynda. Good topic. PT refresh is so frustrating. I almost always use VBA. Mostly b/c my workbooks tend to be “Excel apps” with lots of worksheets, tables, PQs, DAs, PTs, & buttons. “RefreshAll” usually is non-starter b/c we only need to refresh what’s shown on selected worksheet & that worksheet’s hidden helper worksheet (if it exists). So, a button with an assigned macro that refreshes only the PQs & PTs that need to be refreshed does the trick. (I love DAs & use them when appropriate instead of PTs.) All of that said, I like your VBA approach. Very clever. I’ll will be able to use it. Thank you.

Off topic comment/question: Would you describe how you use the worksheet & ThisWorkbook code modules? I have used them only for their event procedures. In Your example, I would have used the worksheet module to house the Worksheet_Deactivate event, but I would have put the RefreshAll sub in a standard code module. My projects tend to have lots of code modules. I wonder if your approach would improve my code storage. What are your rules for using the worksheet & workbook modules v. standard code modules?

[rajkumarrajan]

That is very helpful. i fetch data from a OLAP cube !!! I want to wait until a power query finished refreshes!!!
Often facing the issue that file's refresh is stopped in between. Please povide a suggestion

[RArcher]

Thank you for the video. Extremely easy to follow and useful. Is there a VBA that allows the same "auto refresh" when the sheets are protected? In my scenario. Both the Source Data Table and PivotTable(s) reside in different worksheets in the same workbook. PivotTables are linked to charts in other protected sheets in the same workbook. The entire workbook is protected but specific cells in the other worksheets are unprotected to allow other users to enter data. I've found some answers online that indicate it is possible but the VBA does not fit our scenario. I'm hoping you can help.

[FlatEarthTheory]

Using in my workbook combination of PowerQuery + PivotTabe + function GETPIVOTDATA was definetly a big mistake. Thanks for the explenation.

[NareshSen]

Wonderful. I spent 2 days exploring autorefresh the table