PivotTables are one of the best features in Microsoft Excel, giving you a better way to organize and calculate large sets of data. They’re about to get even better, as Microsoft is preparing to fix the greatest drawback with PivotTables: having to manually refresh the data.
Microsoft announced a new PivotTable Auto Refresh feature coming to Excel for Windows and Mac. It automatically refreshes a PivotTable when there are any updates to the source data range, just like how charts and regular functions already work in Excel and other spreadsheet applications.
The company said in a blog post, “PivotTables are a powerful tool for calculating, summarizing, and analyzing data, but one drawback many of you have shared with us is that whenever new data is added to the PivotTable’s data source, you have to manually refresh it. PivotTable Auto Refresh has been one of the most highly requested PivotTable features across forums, feedback channels, and communities. Today, we’re thrilled to bring you a way to keep your PivotTables up-to-date automatically.”
Auto Refresh will be automatically enabled for new PivotTables, and you can toggle it by selecting a PivotTable, navigating to the PivotTable Analyze tab, and clicking Auto Refresh. It’s also in the Options menu.
There are some catches, as you might expect. It only works for data within the current workbook, not external or asynchronous data sources. The feature also might become unavailable when working on the same document with other people using older versions of Excel, or when the source data is using some functions like RAND() or NOW().
Still, this is a great upgrade over having to manually refresh your PivotTables every time you updated a number somewhere in your document. It’s not clear how performance might be affected with larger data sets, but if you’re making a lot of changes to individual cells in one sitting, you can always pause the Auto Refresh for a while.
Microsoft also said, “When Auto Refresh is off or unable to be triggered, an indicator is added in the bottom-left corner of the window to let you know that one or more PivotTables are using outdated source data. Clicking this indicator will refresh all stale PivotTables.”
Auto Refresh is rolling out in the Microsoft 365 Beta Channel, starting with Excel version 2506 (Build 19008.2000) or later on Windows, and 16.99 (Build 250616106) or later on Mac. It could roll out to the stable channel of Microsoft 365 within a few weeks, depending on how many bugs need to be fixed. If you have one of the one-time purchase versions of Excel, like Office 2019 or Office 2024, you won’t get the new functionality. There’s no word on when this might roll out to the web or mobile versions of Excel.
