Let’s take a look. Here we have the test score table we looked at previously. Currently, there is one conditional formatting rule applied to all test scores in the table. This rule applies a conditional format of a green fill with white text when the test score is greater than 95. Let’s add another conditional format rule to highlight test scores that are lower than 70, in red. As before, start by selecting the cells to format. Then, select an appropriate item from the Conditional Formatting menu. In this case, we want to choose “Less Than” from the “Highlight Cells Rules” category. Now, enter 70 for the value. We’ll use the red preset “Light Red Fill with Dark Red Text.” Let’s add one more conditional format to highlight scores that are below 75 in yellow. As before, we want to use the “Less Than” preset from the “Highlight Cells Rules” category. After we click OK, we see the yellow formatting take effect. However, notice that we no longer see the red conditional format. To understand why this is, we need to review the sequence of the conditional rules that are now in place. Notice that the rules are listed in the order of creation, with the last rule created on top. This is the problem. Because the yellow conditional format is triggered for values below 75, it is overriding the red conditional formatting, which has a condition of less than 70. To fix this problem we need to reorder the rules into a sequence that doesn’t cause conflicts. We can do this by moving the red conditional format into the first position. Select the rule, and click the Move Up button. Now the red conditional format will be triggered for values less than 70, before the yellow conditional format rule is applied.
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.
