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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
When you create a PivotTable based on data that contains lots of text, you may be surprised to find that your text is ...
Discover MoreYou can format PivotTables using either manual formatting or automatic formatting. You need to be careful, however, as ...
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 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 © 2019 Sharon Parq Associates, Inc.
Comments