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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Create a complex PivotTable and you may find that your system slows to a crawl. The reason for this may be due to the way ...
Discover MoreWhen you create a PivotTable based on data that contains lots of text, you may be surprised to find that your text is ...
Discover MoreIf you create a PivotTable in Excel 2007, you may have problems editing or updating that PivotTable in Excel 2003. The ...
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 © 2023 Sharon Parq Associates, Inc.
Comments