Written by Allen Wyatt (last updated June 3, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
When Guy refreshes a PivotTable, he wonders if there is any way to highlight the values that changed as a result of the refreshing.
There is no capability to do this within Excel itself. Since a PivotTable is an aggregation of source data, it is subject to change as that source data changes. In this way it is similar in behavior to a regular formula within Excel—change the data on which the formula is based, and the results of the formula change. The difference, of course, is that the formula changes automatically as the source data changes, and the PivotTable must be updated through user action. In neither case, however, is a modified result indicated in the workbook.
The obvious way around this is to save your data before doing an update. That way you'll have your original, pre-update values to compare with your after-update values. With a PivotTable, you can select the entire table, press Ctrl+C, and paste the values into a new worksheet. You can then refresh the PivotTable and, on the pasted data, use conditional formats to compare the values to what is now in the PivotTable.
The only drawback to this approach, of course, is that the comparison becomes more complex if the refreshing modifies the structure of the PivotTable, such as adding or removing rows or columns.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13348) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Tired of your data being sorted when you create a PivotTable? Excel allows you to specify how the sorting should occur by ...
Discover MoreChanging the data source PivotTables go to can be a bit tricky. This tip explains what can happen when you re-point your ...
Discover MoreAre you attached to the classic PivotTable layout? Looking for a way to make that layout the default for new PivotTables? ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments