Excel formatting rule: Applying several conditional formatting rules at the same time

Excel formatting rule: YOUR OBJECTIVE

You can combine several conditional formatting rules to for instance display different value ranges in different colors.

In this learning module “Excel formatting rule”  you will learn how to define and manage several Conditional Formatting rules and what you have to pay attention to.

Excel formatting rule: HOW DOES IT WORK?

Versions previous to Excel 2007 allowed to define only up to three different Conditional excel Formatting rules. Excel 2007 and 2010 on the other hand allow to define up to 64 rules. Allowing to apply several rules to the same cell or range of cells is also a new function with respect to the previous versions.

Excel formatting rule: CAUTION

Only Excel 2007 and 2010 allow to create more than three conditional formatting rules. If you save a file in the compatibility mode in a previous Excel version, then only the top three rules will be displayed and applied.

In this example, you are going to create an additional rule first and then manage all rules with the Conditional Formatting Rules Manager.

Before you create a rule, you always have to select the range of cells that you want to define the rule for:

Then click the button in the Styles group on the Home tab.

Then click the  button in the opening drop-down menu.

This opens the New excel Formatting Rule dialog box:


In this example, a rule shall be defined to highlight all cells that contain values higher than or equal to 280,000 in a different color. So click the Format only cells that contain rule type in the Select a Rule Type: section:

Then modify the rule in the Edit the Rule Description: section:

Excel formatting rule: Define the following condition for the new rule:

Click the Format… button now to define the color in which to format the corresponding cells.

Select the Fill tab then:

Click the color gray in the color palette:

Clicking the OK button will take you back to the New Formatting Rule dialog box:

Close this dialog box by clicking the OK button. Now click in any cell next to your table to remove the selection:

Excel formatting rule: As you can see, Excel now highlights all values higher than or equal to 280,000 Dollars in gray. However, all cells with a value higher than or equal to 300,000 Dollars, which were previously formatted in purple, are now highlighted in gray too. To display the purple format together with the gray format, call up the excel Conditional Formatting Rules Manager.

Click the  button in the Styles group on the Home tab to do so.

Click the command in the drop-down menu.

You will see the newly created rule at the top of the list in the Conditional Formatting Rules Manager dialog box:

Excel formatting rule: CAUTION

If you do not see any formatting rules, even though you have already created one, your cursor has probably not been positioned within a data range for which a formatting rule has been defined. In this case, click the arrow button of the Show formatting rules for: drop-down list and select the This Worksheet option.

Excel formatting rule: CAUTION

A new rule is always placed at the top of the list. The higher the position of a rule in the list, the higher its priority. This means that rules with a higher priority can partially or completely prevent rules with a lower priority from being executed, if their conditions or data ranges are conflicting with each other.

As you can see, the rule with the purple formatting still exists but is not applied for the following reason:

According to the top rule on the list, all values higher than or equal to 280,000 Dollars shall be highlighted in gray, which of course includes all values higher than 300,000 Dollars. And according to the next rule, all values higher than or equal to 300,000 shall be highlighted in purple. This results in conflicting rules, i.e. demanding two different background colors for all values higher than 300,000 Dollars, for which two different rules exist.

Excel formatting rule: Excel solves this conflict as follows: all values to which more than one rule applies will be formatted in the way the rule with the higher priority demands. And the higher the position of a rule on the list, the higher its priority. Therefore you have to change the order, i.e. the priority of the two rules in this example.

Select the second rule, because you want to move this one:

Then click the  button:

The selected rule has been moved one position up now:

Close the dialog box by clicking the OK button.

This is the result:

According to the new order of rules, Excel now highlights all values higher than or equal to 300,000 Dollars in purple. Then the lower rule is applied, highlighting all values higher than or equal to 280,000 in gray, which causes no conflict for all values between 280,000 and 299,999 dollars, since there are no more rules defined for this value range.

A conflict results for all values greater than or equal to 300,000 dollars, because of the values formatted in purple in this range which should be highlighted in gray. However, the purple background is not replaced with a gray one, because the purple format results from a rule with a higher priority.


Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Fatal error: Call to undefined function stc_get_connect_button() in /usr/www/users/soluzi/en_excelhelfer/wp-content/plugins/simple-twitter-connect/stc-comments.php on line 231