Details
Pivot tables are dynamic and change frequently when data is updated. If you created conditional formatting rules based on “selected cells” only, you may may find that the conditional formatting is lost or not applied to all data when the pivot table is changed, or when data is refreshed. The best option is to set up the the rule correctly from the start. Select any cell in the data you wish to format and then choose “New rule” from the conditional formatting menu on the Home tab of the ribbon. At the top of the window, you will see setting for which cells to apply conditional formatting to. For the example shown, we want: “All cells showing sum of “sales values” for name and “date”
Note: selecting “All cells showing sum of “sales values” will include grand total rows and columns as well, which you ordinarily don’t want.
Editing existing rules to fix broken formatting
If you already have a rule set up that is not correctly formatting all values as needed, edit the rule and change the cell selection option if needed. You can access existing rules at Home > Conditional Formatting > Manage Rules. In the example shown, the rule manager displays two rules like this:
To edit a rule, select the rule and click the “Edit Rule” button. Then adjust settings in the “Apply rule to” section. Note: conditional formatting is lost when you remove the target field from a pivot table. Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
