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!
Changing the data source PivotTables go to can be a bit tricky. This tip explains what can happen when you re-point your ...
Discover MoreWhen you create a PivotTable, Excel automatically sums the data that you place into the Data Items area of the table. ...
Discover MoreIf you modify the data on which a PivotTable is based, you'll need to refresh the table so it reflects the modified data. ...
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 © 2024 Sharon Parq Associates, Inc.
Comments