Complex conditional formatting in Google Spreadsheets

I quite like organizing things in spreadsheets generally and of course had a fair amount of familiarity with Google Docs & Spreadsheets after working there for 5 years. Among other things, I more or less project planned my wedding and all its associated craft projects using Google Spreadsheets.

One of my favorite features is conditional formatting, which helps you do automatic color-coding based on the content of the spreadsheet! Very useful when you want to see at a glance which items on a list might still be incomplete or is in danger of being late.

I recently discovered an addition to the conditional formatting options that lets you set the rules across multiple cells at a time, based on the content of multiple cells. This was a big deal for me because I’d previously thought you could only change the colors of a cell based on that cell’s own contents. However, sometimes you want the rule to be based on the contents of multiple cells taken in conjunction together. Before, I’d have to put in a new column somewhere and then base the rule off that extraneous column. And I wouldn’t be able to change the color across an entire row with the result, just the color for that one cell.

The new feature is a “Custom formula is” option, along with the ability to apply conditional formatting rules across a whole range. With this, you can write formulas that combine multiple cells’ values together and change all of their colors based no the result.

In my example, we were doing a prioritization exercise at work where there two different kinds of effort estimates, with L = Low, M = Medium, H = High. We wanted to be able to see at a glance the highest priority ones, which would be HH, vs. the lowest priority ones as LL or any other combo, like MH which is more important than HL.

It took some finagling to figure out exactly how to write the custom formulas to return the correct True or False values that the conditional formatting would then use to judge whether to apply the color styling. You have to use the dollar sign to pin the ranges in the right spots. Here’s what I ended up with:

• Apply to range: A:B (the two columns I want to be colored based on the formula’s results)
• Red for =concat($A:$A, $B:$B)="HH"
• Orange for =or(concat($A:$A, $B:$B)="MH",concat($A:$A, $B:$B)="HM")
• Yellow =or(concat($A:$A, $B:$B)="LH",concat($A:$A, $B:$B)="HL",concat($A:$A, $B:$B)="MM")

Which brings us this result:

Pretty awesome, right?? Project managers everywhere should rejoice! You can make a copy of my example spreadsheet from here.