How to manage conditional formatting rules in Microsoft Excel

Microsoft Excel logo on a green background

If you use conditional formatting in Microsoft Excel, then you probably know that you can use more than one rule at a time. To check these rules as well as edit, delete or duplicate one, you can use the Rule Manager.

Whether you are highlighting a row or looking for duplicate data, you can apply multiple conditional formatting rules to the same set of cells or different cells in the same spreadsheet. In addition, you can set up rules in several sheets in your workbook. With Rules Manager, you have an easy way to view and manage all of your formatting rules.

Open Rule Manager in Excel

Before opening the Rule Manager, you can select a specific sheet to work with if you wish. However, the tool lets you select the spreadsheet that contains the conditional formatting rules you want to manage.

Go to the Home tab, click the Conditional Formatting drop-down menu and select “Manage Rules”.

Under Conditional Formatting, select Manage Rules

When the Conditional Formatting Rules Manager window appears, use the drop-down menu at the top to select the sheet or use the current selection of cells and view the rules.

Choose a location with conditional formatting rules

This allows you to skip between the rules you set up for different spreadsheets in your workbook.

RELATED: How to group worksheets in Excel

Manage conditional formatting rules

At the top of the Rules Manager there are various actions you can take. After performing an action, click “Apply” and then “OK” at the bottom of the tool. And of course, if you change your mind, click “Cancel”.

Conditional Formatting Rules Manager

Create a new rule

Click “New Rule” to configure another in the current sheet or for your selected cells. If you have not already selected cells, simply add them in the “Applies to” column for that rule.

Create a new conditional formatting rule

Edit a rule

Click “Edit Rule” to change the rule type and description. This box is similar to the New Rule window. You can make changes to the rule or the formatting itself. When done, click “OK” to apply the changes.

Edit a conditional formatting rule

If you only want to change the cell range of a rule, just update the cell references in the Applies to that rule column.

Cell range for a rule

Delete a rule

To remove a rule completely, click “Delete Rule”. Just be aware that you will not be asked to confirm this action. So when you delete a conditional formatting rule here, it is removed forever.

Delete a conditional formatting rule

Duplicate a rule

An easy way to set up a similar conditional formatting rule is by duplicating a current one. Maybe you’ve created a rule based on date so all last week’s dates are highlighted in green. But you also want a rule, so this week’s dates are highlighted in yellow. You can click “Duplicate Rule” for the first one, then click “Edit Rule” for the copied one and make your adjustments.

Duplicate a conditional formatting rule

This is also handy if you want to apply the same rule to a different cell range.

RELATED: How to sort by date in Microsoft Excel

Rearrange your rules

The rules in the tool are in the order in which they apply. This means that if you have more than one rule for the same set of cells, each rule applies in the order in which it is displayed. You can rearrange this order using the arrow buttons on the right side.

Select a rule you want to move up or down and use the corresponding arrow to move it.

Move a conditional formatting rule

In addition to rearranging your rules, you may want to stop them from being applied at some point. In this case, check the box to the right of the “Stop if true” rule. So, if the rule applies and conditional formatting takes place, no additional rules will be applied.

Here is an example: Let’s say you highlight values ​​based on ranking. You have rules for highlighting cells with numbers above average and those in the top 50 percent. But the above average is your primary concern. You can select the Stop check box if true, so if the above average rule applies to these cells, it will stop there and not highlight them in the top 50 percent as well.

Select Stop if true

You can easily keep track of your conditional formatting rules with Rules Manager in Excel. And for additional rules that might interest you, take a look at how to use icon sets to represent values, or how to highlight blank fields or cells with errors.

Leave a Comment