Conditional formatting with Google Spreadsheets

April 26, 2010 at 8:39 am | Posted in Programming | 4 Comments

I like Google Spreadsheets a lot. It is free. It is not overloaded with features that I’m never going to use. It is on-line so I can easily share my spreadsheets with the rest of the world. And it has conditional formatting like any other modern spreadsheet. Well, sort of at least. In Google Spreadsheets it is called ‘Change colours with rules’ and this feature was introduced already in 2007. The downside: you can only change a limited set of cell properties (color of text and background) and only based on the contents of the cell itself. For most real-life situations that is pretty much useless because people want the format of a cell based on formulas that involve other cells.In this blog I will show how this is possible with a trick, although still limited. For my personal goals 2010 I maintain a spreadsheet that looks like this:

Personal Goals 2010

Personal Goals 2010

Each week I update the numbers and Google Spreadsheet generates a nice graph (see end of this blog) for me. I publish this graph via my Twitter account. I tend to forget what week (row 3) we are living so what I wanted is a different color for the current week. In the picture you can see the last week (week 17) formatted in dark green as opposed to the other weeks.

So how did I do this without being able to use conditional formatting based on formulas? The trick is very simple. First I inserted a (hidden) row with week numbers:

Spreadsheet with visible row 1

First row contains week numbers

Next I use a formula to calculate the value of the weeks in row 3:

Formula to calculate week number

Formula to calculate week number

As you can see in this formula I add a space to the week number if the week equals the current week. In this formula CurrentWeek is a named cell in which the current week is calculated. So now we have cells that are formatted differently: all the weeks are just plain numbers, except the current week that is a number with an invisible space at the end. And this is exactly what we use to format the header:

Change colour with rules

Change colour with rules

Both rules check on the availability of a space and set the color accordingly. This trick can be used in many scenario’s.  It is not very elegant, but until Google Spreadsheets get real conditional formatting, it will do the job for me. Finally the burn-down chart for my personal goals for 2010 so far:

Personal Goals 2010

Personal Goals 2010

As you can see I’m still more or less on track.

4 Comments »

RSS feed for comments on this post. TrackBack URI

  1. Nice blogpost, lots of cool stuff here, good use of that live updating chart. I see you’re using the new version of spreadsheets!

  2. Hey: great post. I love google spreadsheets. Sorry to hijack the thread…I would love to embed one in our blog, but I can’t seem to find a workable solution anywhere. Any chance that you could help me out? It’s a wp.com blog. indygolftour.com.

    • @indygolftour: sure, I might be able to help you. If you want you can email me directly and explain what you are trying to accomplish.

  3. Thanks, that’s a nice trick. Google’s conditional formatting is lousy.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: