Image: Microsoft
Data wonks, rejoice! Pivot tables now automatically refresh themselves in a new beta version of Microsoft Excel.
You might expect that pivot tables—which can be used to summarize rows and columns of data into a digestible “number”—would automatically refresh when the data within changes. Charts already do that, for example. But, until now, a pivot table can still require a manual click to update if changes are made… and you might not always remember to make that click, either.
Microsoft says that’s changing. Within the Beta Channel (Version 2506 Build 19008.2000 or higher), Microsoft is adding an “Auto Refresh” function for pivot tables. “With Auto Refresh enabled, any updates to the source range—like new sales entries or corrections—are instantly reflected in the PivotTable, keeping it updated in real time,” Microsoft said.
Auto Refresh is now on by default, Microsoft said in a blog post this week. To turn it off (for whatever reason), you can navigate to the PivotTable Analyze tab and select Auto Refresh.
Microsoft
There are some caveats: the new Auto Refresh capability is a per-data source property, and you won’t be able to see updates for changes to external data sources that are connected to the workbook. Microsoft says that’s to ensure your data remains consistent.
Excel currently allows you to send an Excel file to someone and have that file auto-update/refresh when it’s opened, but that’s not quite as good as being able to work within the file itself—especially when rivals like Google Sheets perform automatic updates if configured to allow them.
It’s doubtful that Excel will automatically sense and update a pivot table if you add new cells outside of the current table’s range. That’s a weakness that Sheets has, too. Nevertheless, Excel users have to celebrate this new change. You’ll just have to wait a while for it, or otherwise upgrade to Microsoft’s beta versions to receive it right now.
Author: Mark Hachman, Senior Editor, PCWorld
Mark has written for PCWorld for the last decade, with 30 years of experience covering technology. He has authored over 3,500 articles for PCWorld alone, covering PC microprocessors, peripherals, and Microsoft Windows, among other topics. Mark has written for publications including PC Magazine, Byte, eWEEK, Popular Science and Electronic Buyers’ News, where he shared a Jesse H. Neal Award for breaking news. He recently handed over a collection of several dozen Thunderbolt docks and USB-C hubs because his office simply has no more room.