Excel Tip of the Week #153 - Conditional formatting: Layering multiple formats

Hello and welcome back to the Excel Tip of the Week.  In TOTW #143, we looked at how to colour a whole row using formula-based custom conditional formatting rules.  This week, we have a Creator post in which we look at how to juggle multiple conditional formats in one region, to make sure that Excel applies the rules in the way that we want.


Where might this be useful?

  • If we are looking at a rolling set of deadlines – for example, if we have a process whereby once a first contact is made, a series of other actions must be performed by a set of dates.  We could colour-code each row based on the stage in the process we should be at.
  • If we want to rate a series of results based on a sliding scale (such as A-E grades) but also show this visually
  • To show if all, some, or no criteria (and even which) criteria are met from a list
  • Any other situation where multiple criteria that colour cells are overlapping 


How do you implement this?

So, how to decide what goes where. Excel is actually really helpful here – let’s look at the rules manager.

You can see here that it states the rules are “applied in order shown” – which means that Excel will check each rule in turn and apply if the criteria are met.  So in this example, if the cell has already been coloured white by the first rule, but then the formula result is “TRUE” for the next rule down, the formatting will be overwritten and the cell will end up green. 

But if we don’t want this to happen, Excel has provided us with two tools that will allow us to alter the ways in which the formatting is applied.  Firstly, you can see above that there is the option to use the “stop if true” check box to the right of the formula for each format. If you check this box, Excel will stop checking all other formatting rules on the list if it has applied this format. You can select one, all or some of the rules to apply this to. 

Secondly, you aren’t locked in to the order you entered the rules in in the first place. You can move their order quite simply by clicking on a rule to select it (this will highlight the rule in blue) and using the up and down arrows (next to the “delete rule”) button. This means that if you want to add additional rules or change the order in which rules are applied, you can quickly do this without having to start over. 

It might take some thought to work out in which order you want to apply your formatting rules, but once you know, it is very easy to have Excel do what you want. It also allows you to break the process down into further detail, without needing to add multiple columns to contain all the criteria you are evaluating for – much easier for an “at a glance” look at the data you are analysing.

Check out the attached workbook for a look at this demonstration - and trying altering the options from Home => Conditional Formatting => Manage Rules to see what happens.

Previous post - Nesting functions in the Formula Wizard
TOTW index 
Next post - Database functions

Thanks to Emma Horton for her input on this Tip.

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.

  • I think the rule order might operate slightly different to that. I think Excel takes into account all the rules that evaluate as TRUE, and where the same elements are formatted by more than one TRUE rule, applies the one that is highest in the list.