Monday, March 11, 2013

Set background colour of spreadsheet cells using conditional formatting

I'll often use Google's spreadsheets as a sort of checklist - the collaboration function is great when you're working with others. However, looking at a long list of items and trying to see what has been completed vs what needs to done can become a task in itself. That's why I like to use conditional formatting to change the background colour of cells - helps me determine where to focus my energies.

Let's pretend I'm building a house and I have the framing all in place. I'm trying to co-ordinate 2 trades: electrical and drywall. Obviously, the electrical needs to be in before the drywall goes up. Once those two tasks are done, I can go in and paint.

As each trade is finished a room, I ask them to place a Y (Yes) in their column. Once both columns show green, I know the room's ready for me to go in and paint. This spreadsheet will also let the drywaller know when he can go into a room and do his work. Here are the steps you need to do this:

  1. Select the cells you want the conditional formatting to run on.
  2. Select Conditional formatting from the Format menu.
  3. Select the condition you want met (in this case, text contains Y).
  4. Select the colour of the text/background you want when this condition is met.
  5. Save the rules.
As I like to visualize where the holdups are, I add an additional rule that makes the cell red if empty.

Check out the spreadsheet in action!