If you have gone ahead to learn how to apply conditional formatting preset conditions in Excel in my earlier post, then you will need to also know how to edit and remove conditional formatting in excel when you no longer need them.
Edit Conditional Formatting Rule in Excel Worksheet
Here is a worksheet with different conditional formatting rules applied to it.
Currently, the Top/Bottom rule is applied to highlight the Top 5 highest values in Column C. But now, we want to change the rule to highlight the Top 10 highest values.
To edit the rule,
1. Go to the Home tab, click Conditional Formatting and select ‘Manage Rules’ from the options.
2. In the ‘Conditional Formatting Rules Manager’ dialog box, click on the dropdown arrow to select the worksheet which is ‘This Worksheet’.
This will display a list of all the conditional formatting rules that is applied to the worksheet.
3. Scroll down to select the rule you want to edit.
In this example, we want to edit the Top 5 rule to highlight the Top 10 highest values in that column instead of the Top 5 values.
4. Click on ‘Edit Rule’ above and a new dialog box will pop up. This is where you will adjust the condition of the rule. Enter the number 10 in the field and click ok.
5. Excel will direct you back to the ‘Conditional Formatting Rules Manager’ dialog box. Then click ok to save and apply the changes.
Now, more cells are highlighted to show the Top 10 values in that column.
Remove Conditional Formatting Rule in Excel
If you no longer need a conditional format, then you will have to permanently remove conditional formatting rule from the excel worksheet.
To remove conditional formatting rule from an Excel worksheet, there are only a few steps required.
1. Click on Conditional Formatting -> Manage Rules
This will take you to the ‘Conditional Formatting Rules Manager’ dialog box.
2. Select the rule you want to delete and click on ‘Delete Rule’.
In this case, we want to remove the colour scales rule. So we select it and click on ‘Delete Rule’.
Once you click on Delete Rule, the rule is removed from the list.
3. Click Ok to apply the changes.
This is what the worksheet looks like now. You can see that the colour scale format has been deleted from the worksheet.