Editing and removing conditional formatting rules
The layout and use of a workbook are rarely static, so the conditional formatting that you apply to your sheet may have to be edited at some point.
For instance, the previous highlight cells rules we set… Maybe it’s time to change them.
Luckily that’s fairly easy.
You can edit any conditional formatting rule from the conditional formatting rules manager.
Edit the conditional formatting rule and formatting style
1. Click ‘Conditional Formatting’ from the Home tab and select ‘Manage Rules’.
After you click ‘Manage Rules…’ you’ll see a ‘Conditional Formatting Rules Manager’ dialog box.
Change the following 3 points as you see them on in the manager.
2. Set ‘Show formatting rules for:’ to ‘This Worksheet’ to show all conditional formatting rules for the entire sheet.
3. Find the rule you want to edit (there might be several on the list).
4. Click the ‘Edit Rule’ button.
Another dialog box appears. From here you can change anything about the rule.
Below we’ll go through the different options shown in the screenshot above.
Circle 1) Here you can change the ‘Rule Type’. If you don’t want to apply conditional formatting when “something” happens in a cell, you can change it to apply on all cells and fill them with color based on their value compared to the average of the data.
Circle 2) If you keep the ‘Rule Type’ you can change the “trigger” of the formatting. The trigger depends entirely on which type of conditional formatting rule you choose in circle 1. In this case, you can change the custom formula.
Circle 3) From this little (familiar) box you can change the formatting that you applied in the first place. If you don’t want your cells to turn yellow when it drops below cell value: 100, you can have them turn purple instead.
As you see there are more than enough options.
But wait – there’s more!
Edit the conditional formatting rules range
All of the above is very nice, but if you accidentally selected the wrong area when you made the conditional formatting rule – then it doesn’t help to change the ‘Rule Type’ or whether the cells should be red or blue.
And if you spent 5 minutes entering a unique formula to “trigger” the formatting, then it sucks to delete it and do it all over.
To change the range of cells that the conditional formatting applies to, you don’t need to go to the ‘Edit Rule’ box above.
You just need to click in the ‘Applies to’ column at the rule you want to change in the ‘Conditional Formatting Rules Manager’ box.
If you can’t remember the range that the rule should apply to, click the tiny red arrow to select the range manually.
That’s how you edit a conditional formatting rule✏️
So if you screw one up?
You don’t have to delete it – simply just edit it.