We have covered the use of conditional formatting in many articles over the years, including looking at a range of practical uses. In this article, we are going to concentrate on what happens when multiple conditional formats are applied to the same cell or group of cells.

Perhaps the first thing to note is that it is perfectly possible for a single cell or range of cells to have multiple conditional formats applied to them at the same time. In this example we have applied Data bars, Colour scales, Icons, font colour, font bold attribute and font borders. Any cell for which any of the conditions are met will include all the applicable conditional formatting graphics and attributes since none of them conflict:

Of course, things change when we apply conditional formats that conflict. For example, here we have four cells all of which have the same two conditional formats applied. Conditional format one sets the font colour to red, and conditional format two sets it to green. Where the value that we have entered only matches one of the two conditional format criteria, that format will be applied, but where both match, conditional formatting has to choose between them. Where neither of the two conditions apply, no conditional formatting is used:

Just to demonstrate one way of setting up multiple rules, we have selected our four cells then used the Conditional Formatting dropdown in the Styles group of the Home Ribbon tab to select the Highlight Cells Rules menu. We want to use a condition that checks whether the value in each cell is between two target values. The menu includes a Between… option, but we will click on the More Rules menu option to open the New Formatting Rule dialog. This allows us to show that the comparison dropdown in the dialog includes some additional options. We will just choose our Between comparison, but we can see that, for example, this dropdown includes a ‘Not Between’ option that isn’t directly available in the Conditional Formatting, Highlight Cells Rules menu.

After choosing Between we can then just enter the values to which we want to compare our cell value. In the case of our first conditional format this is 1 and 2. It would usually be a better idea to use references to the cells that contain our chosen values, rather than entering the numbers directly. Doing so can make it more obvious to the user why a particular conditional format is being applied and can also make it much easier to change any values if needed:

With our condition now set, we can click on the Format button to choose the associated format. In this case we will just set the Font Colour to green and click on the OK button to apply our conditional format to the four cells. We should see that the Font colour of the two cells that match the ‘Between’ 1 and 2 rule change to green.

Having created our first conditional format, our second conditional format is going to be very similar. We could just create our second condition in exactly the same way we did the first, with the same four cells selected. However, because the two conditions are so similar we could use the Manage Rules… dialog from the Conditional Formatting dropdown. This dialog includes a Duplicate Rule button that creates a copy of the selected rule:

We now have two identical rules, we select the second one down and click on the Edit Rule… button. This displays the Edit Formatting Rule dialog allowing us to change the value to 2 and 3, and the Format to Font Colour red. We now have our two mutually exclusive formats and we can see that the formatting of the top two cells doesn’t change, but the third cell is formatted with a red font. Our fourth value is outside both of our conditions, so remains unchanged:

Examining our first three values, the value of 1 only matches our first condition so is correctly formatted as green. The value of 3 only matches our second condition, so is formatted in red. Our value of 2 matches both of the conditions and is currently formatted in green. If we looked at our rules, we might think they would be applied from top to bottom. If this were the case, then our value of 2 would be red as the second condition would override the first.

However, as our example shows, this is not how the priority of multiple rules works. Rather than being applied in order, the rules are listed in priority order. This means that, where rule conditions overlap, it is the rule closest to the top of the priority list, rather than the last one in the list that is applied. The Conditional Formatting Rules Manager dialog includes arrow buttons to allow the order of rules to be changed. If we swap the order of our two rules, so that our ‘red’ conditional format is now above our ‘green’ conditional format we will see that our value of two is now formatted in red:

Note that there is also a ‘Stop If True’ option that will stop processing later rules. Here we have added a Data Bar rule:

Setting ‘Stop If True’ for an earlier rule prevents the Data Bar rule from being applied:

You can explore all aspects of Conditional Formatting, and articles on a wide range of other Excel features and techniques, in the ICAEW archive:

• Excel archive