Adventures in Spreadsheet Journalism lesson four: Conditional formatting

Conditional formatting, Part 1: A couple of things you need to know

Your spreadsheet skillset likely boasts an at-least-passing acquaintance with Excel’s conditional formatting feature – really a miscellany of features – that impose user-specified formatting(s) upon cells meeting a user-specified condition or set of conditions, e.g., seeing to it that every score in a column of test results equalling or exceeding 90 is coloured blue, and/or that every failing grade is tinctured red.

Conditional formatting is assuredly a good thing to know – and it’s a good thing to know what it isn’t. The mind plays tricks, and delusions can beset the user easily here.

Conditional formatting means what it says – that cells receive a new format after meeting a condition or conditions; but what it won’t do is perform a substantive act upon a value – say, add six to any test score topping 80, or multiply any plus-80 score by 20 per cent.

That sort of capability belongs to standard IF statements and the like, while conditional formats can be regarded as a species of if statements that carry with them formatting consequences only.

Much of conditional formatting is easy to learn, and you’re probably au fait with its basic operation. But there are couple of things you need to know, and that call for a bit of elaboration.

Consider this scenario: You want to conditionally format all test scores equalling or bettering 90 by turning their cells blue, while tinting green all cells with scores equal to or greater than 80. And that pair of intentions poses a problem, because any score registering 90 or above naturally surpasses 80 too, and you need to understand how Excel decides between the two criteria.

Suppose we’re working with these test scores, occupying cells A6 through B16:

We could select the score-bearing cells in B6:B16 and issue two conditional formats that look something like this:

Click OK and you’ll see:

And that’s not quite what we wanted, because even Sally and Roberta – both 90-plus achievers – have turned green, and we clearly want them garbed in blue. This little dilemma exemplifies the way in which Excel manages multiple conditional formats.

When subjected to a more than one conditional possibility, Excel assesses a cell in the order in which the formats are listed.

Since in the above screen shot the more inclusive condition – scores equalling or exceeding 70 – appears first, all the cells fulfilling that condition receive the format, and the process stops then and there, even if the 90-and-above scores meet the second condition as well.

The way out of this ambiguity is to list the more restrictive condition first – that is, reversing the order in which they appear.

In our example you’d select the first condition in the above screen shot and click the down arrow, thus bumping the equal-to-or-greater-than 70 condition down to second position:

Now that the data encounter the >=90 stipulation first, the appropriate formatting kicks in:

That’s one thing you need to know. The next – which we hope to address in the next post – is when you need to devise a conditional format requiring a formula.