Adventures in spreadsheet journalism lesson five: More fun with conditional formatting

My first instalment on conditional formatting broached the standard, by-the-book protocol for applying conditions to cells: selecting the cells you want to subject to the potential formats, and then specifying those conditions that when realized would put the formats in play.

Part two takes up a subtler contingency: formatting cells on the basis of conditions met in a different set of cells.

For example, let’s return to that complement of students and their test scores, as positioned in cells A6:B16:

In instalment one we conditionally formatted the scores themselves, such that scores equal to or greater than 90 were hued blue, and scores in the 70-89 span turned green. Here, we want to apply those formats to the student names – such that Sally will turn blue, so to speak, instead of her score.

In order to make that happen, we need to select cells A7:A16, the name-bearing cells. Then click Home tab > Conditional Formatting > New Rule > Use a formula to determine which cells to format (beneath the Select a Rule Type heading). Then enter:

Note that the reference denotes B7, the cell alongside A7. We want the name in A7 to turn green provided that B7, its companion test score cell, equals or exceeds 70. And because we initiated the process by selecting A7:A16, each of those cells will receive the conditional format should its associated cell in the B column match or better 70. The reference to B7, then, will be copied down through B8:B16, as well.

After entering the formula click the Format button you see above and select a green Fill format. Click OK and you'll be delivered here:

Then click New Rule in the Manager’s upper-left corner and click back on Use a formula…and enter:

Click Format and select a blue (or any other colour) fill to characterize those scores equalling or topping 90, and click OK twice (and recall my caution in the previous post requiring that the stricter condition appear first in the Conditional Formatting Rules Manager).

Now the student names, as opposed to their scores, are duly colour coded:

Note that Bob, Ed, and Quentin’s scores satisfy neither conditional format, thus restraining their cells in their default, colour-free status.

Of course as is generally the case with Excel’s enormous grab-bag of capabilities, there are more parts to conditional format formulas than the ones we’ve inventoried here, but once you nail down the basics detailed above you can start to do real work with the feature.