Conditional Formatting in Excel

Highlighting cells in accordance with a set of rules

That is, in a “nutshell” what Conditional Formatting is all about – the formatting changes based on a set of rules that you specify. This could be whether a cell value is higher or lower than a target, or a date before or after another or matching against other cells in a table to create a “heat map”.

Once you’ve set your conditions & how you want the cells meeting those conditions to look (for example highlighted in Red, Amber or Green), that’s it; Excel will then change the formatting of your cells in accordance with your rules

Simple Conditional Formatting

In simple terms, for Conditional Formatting to work, you need to be comparing values, so we’re looking for comparisons that are…

  • Greater than >
  • Greater than or equal to >=
  • Equal to =
  • Not equal to <>
  • Less than or equal to <=
  • Less than <

So, in this example, we want to know if the value in Cell A2 (the “Actual”) is greater than the Target (in Cell B2). So, having entered two values, we select Cell A2 (where we want the Conditional Formatting to be applied) & then select Conditional Formatting from the HOME tab. Them, as we’re wanting the cell to change colour, we select Highlight Cells Rules followed by, in this case, Greater Than

This takes us to the parameter fields for the conditions & formatting that we want to apply; initially, you’re prompted with a value which can be replaced with another or, even better, linked to your target cell

You can also select the type for formatting & as you can see, the idea of a “traffic light” system (Red, Amber – or “dark yellow” – & Green has been pre-populated)

Once this is set, as soon as the Actual figure exceeds the target, the required shading is applied…

Traffic lights

As has been mentioned, using Conditional Formatting, it’s fairly easy to create some sort of traffic light system… this can be useful if you’re looking at dates before an action needs to be taken – more than three months to go, it’s Green, between three & two months, it goes Amber & with less than a month to go, it turns red.

We have to be careful with dates & remember that Excel treats them as numbers… the further into the future, the higher the number… so, for example, in Excel’s world 30th June 2018 = 43,281, 31st July = 43,312

If we’re setting up this sort of system, then we’ll also find the TODAY function helpful (=TODAY() is all you need in a cell) as that will automatically show “today’s date” whenever you open the spreadsheet.

In cell E4 we have a calculation that resolves to less than zero if the due date is older than today’s date & we then use the Conditional Formatting>Highlight Cells Rules>Less than option to turn any that match that criteria Red

We’ve then use the Conditional Formatting>Highlight Cells Rules>Between option to highlight any that are between a lower value of 0 and 30 & we’ve used the drop down to show these as Amber

And finally, we’ve again used “Between” to shade those with a due date of between 30 & 60 days; any invoices due beyond those terms won’t have any shading

It’s also a good idea to have a key…

When creating the Conditional Formatting, the little button with an upward pointing arrow indicates that the value can be linked directly to a cell

On earlier versions of Excel this looked like this…

Simple Heat Maps

If you have a block of data – perhaps sales figures for a number of products over a number of months – & you need to highlight the highest & lowest sales quickly & easily, then the Heat Map (or Color Scales) that you can generate through Conditional Formatting might be just what you need…

A warning

When applying Conditional Formatting, in later versions, Excel simply adds them to the formats already created. This means that if you’ve made a mistake, it could still be there skewing your results, so it’s always a good idea to check by selecting Conditional Formatting>Manage Rules

And ensuring that the Manager only shows the rules that you’re expecting…

This is also where you can edit & delete specific rules