Jan 252010

I’ve been using Excel a lot more recently to crunch numbers, and something that I’ve found particularly handy has been the conditional formatting function, which will surprise surprise, format a cell or cells based on a condition. For me I was using the spreadsheet to highlight cells based on the length of their contents, I was using the spreadsheet for managing Google Adwords and needed to be sure that the titles, and descriptions didn’t exceed their maximum length like this:


I found it surprisingly confusing initially, mainly I think because I was thinking the conditional formatting was going to be managed inline like a traditional formula, it isn’t, it has it’s own management interface where you specify to what the formatting is supposed to be applied and for what reasons.

Ok, so I won’t go gthrough the whole process, I’ll assume you have seen (in Outlook 2007) the “Conditional formatting” button under the “home” tab:


And have also seen the “Highlight Cells rules” link underneath, here is the place where you can specify the conditions you want to highlight, and have set it up so that at least one cell does what you want (this was pretty straight forward I think, if you’re not sure what to do please leave a comment/question).

From here though I was stuck, I didn’t get how to update the range to which it was applied, and have it rolled out across multiple cells.  In step the  “Manage rules” link that is in the image above.  This is a separate screen where you get a summary of all the conditional formatting rules on your spreadsheet. Open it up and you will see something like this:


As you can see I have used a formula based on a relative cell value (i.e. one that updates relative to the cell you’re working with rather than referring to a single static location), this is what those $ symbols mean, the rule I have is that any cell that exceeds 25 characters in length should be coloured red.  The mistake I had originally made was to firstly not use a relative cell value (my original formula was len($S$1) > 25    ) and then to not have specified the range correctly in the “applies to” box.

Experimenting with the “Rules Manager” for conditional formatting reveals this to be just as powerful as you were probably expecting when you started.  Good luck!