Conditional formatting with Google SpreadsheetsApril 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:
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:
Next I use a formula to calculate the value of the weeks in row 3:
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:
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:
As you can see I’m still more or less on track.