Mar 282010

In all the years I have used excel I have never once encountered this problem so hopefully this helps anyone else in the situation.  I have been used to Excel automatically updating its values (say in a sum() column) whenever I change values above, but somehow today it stopped working.  fortunately my better half happens to be an Excel nerd, so with her help I worked out what it was.

If you find that the values in Excel are not updating automatically it is probably because you have not got the (succinctly named) “calculation options” switched to automatic.  Apparently back in the day you were required to press F9 to do calculations at any stage, and indeed this still works, but if you want a more permanent solution in Excel 2007 at least goto:

Formulas > Calculation > Calculation options

Then check the “Automatic” selection.  This should rectify the problem immediately.

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!