Written by Allen Wyatt (last updated February 13, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003
David is wondering if it is possible to apply conditional formatting to PivotTables. He would like to use conditional formatting so that when a table is refreshed the conditional formatting is applied to the new data in the PivotTable.
There is nothing that we have found that stops conditional formatting from being applied to a PivotTable. After creating the PivotTable you can apply the conditional formatting and it should work just fine. The problem, however, comes when you refresh the PivotTable and the refreshing results in the PivotTable being enlarged. In that case Excel expands the PivotTable into cells not previously used by the table. These cells also have no conditional formatting applied, so Excel doesn't know that it should apply it.
You can get around this problem by making sure that you apply conditional formatting to more than just the cells presently visible in the PivotTable. You should select a larger range of cells (including those outside the current PivotTable) and apply conditional formatting to that expanded range. In this way Excel can "retain" that formatting as it expands the PivotTable during a refresh.
The obvious problem with this approach, however, is that the flexibility of the conditional formatting is seriously hampered. If you have several conditional formats applied to the PivotTable, and those formats are not the same as each other, then how can you effectively apply those formats to an expanded cell range? Unfortunately there is no good answer to this possibility.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7002) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
When you update a PivotTable, Excel can take liberties with any formatting you previously applied to the PivotTable. ...
Discover MorePivotTables are often used to aggregate lots of information, and they do it beautifully. What do you do if Excel starts ...
Discover MoreOne of the ways you can use PivotTables is to generate counts of various items in a data table. This is a great technique ...
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 menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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