View all newsletters
Sign up for our free email newsletters

Fighting for quality news media in the digital age.

  1. Comment
March 1, 2013updated 17 Apr 2013 1:46pm

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.

Content from our partners
MHP Group's 30 To Watch awards for young journalists open for entries
How PA Media is helping newspapers make the digital transition
Publishing on the open web is broken, how generative AI could help fix it

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.

Topics in this article : , ,

Email pged@pressgazette.co.uk to point out mistakes, provide story tips or send in a letter for publication on our "Letters Page" blog

Select and enter your email address Weekly insight into the big strategic issues affecting the future of the news industry. Essential reading for media leaders every Thursday. Your morning brew of news about the world of news from Press Gazette and elsewhere in the media. Sent at around 10am UK time. Our weekly does of strategic insight about the future of news media aimed at US readers. A fortnightly update from the front-line of news and advertising. Aimed at marketers and those involved in the advertising industry.
  • Business owner/co-owner
  • CEO
  • COO
  • CFO
  • CTO
  • Chairperson
  • Non-Exec Director
  • Other C-Suite
  • Managing Director
  • President/Partner
  • Senior Executive/SVP or Corporate VP or equivalent
  • Director or equivalent
  • Group or Senior Manager
  • Head of Department/Function
  • Manager
  • Non-manager
  • Retired
  • Other
Visit our privacy Policy for more information about our services, how New Statesman Media Group may use, process and share your personal data, including information on your rights in respect of your personal data and how you can unsubscribe from future marketing communications.
Thank you

Thanks for subscribing.

Websites in our network