Refreshing Change for PivotTables

Whilst you have been able to refresh a PivotTable upon opening a file for years in Excel, refreshing the PivotTable when the source data changes has been more problematic.  Not yet announced in the Excel Updates, Microsoft Excel now provides you with the ability to update your PivotTable automatically whenever you make changes to the data source.  There is no need any more for manual refreshes VBA macros or Office Scripts.  You could use PIVOTBY, I suppose, but perhaps I’m being a little churlish and that won’t assist you with any old PivotTables you may have lying around yet constantly use.

‘Auto Refresh’ is found on the Ribbon in the Data section of the context specific 'PivotTable Analyze’ tab (i.e. you need to be inside a PivotTable):

It can also be accessed from the Data tab of the ‘PivotTable Options’ dialog (again, this can be accessed when the cursor is inside a PivotTable or via File -> Options):

It should be noted that Auto Refresh is what is known as a “per PivotCache property”.  This means the setting controls multiple PivotTables bound to that source range only (not all PivotTables if there are multiple sources).  Furthermore, Manual and Partial calculation modes in Excel will also prevent automatic updates.

This is going to make life so much easier for regular users of PivotTables, saving time and reducing errors by eliminating the manual refresh step.

Just one catch: it’s only available in Insiders presently – and then only to a random few.  But it’s coming!!