Overwritten Cells

Very often where I work I come across spreadsheets of a certain type, characterised as follows:

• 1 row containing headings (of varying levels of helpfulness) ...
• followed by 1 row containing formulae ...
• all copied down for several hundred rows or more.

This is the kind of thing that spreadsheets are really good at. Write your calculations for 1 record and copy them as many times as you need. Brilliant.

That's all fine as long as people don't mess it up! All too often I see situations where the formula in cell AZ653 doesn't quite work due to a quirk in that record. Rather than think about the wider implications the user simply types the right value in instead and carries on regardless. There are numerous problems with this but I'll concentrate on the poor person whose job it is to check this spreadsheet. It is very possible that this change will be missed, or perhaps just become overwritten, e.g. if someone copies all the formulae right the way down again. We need an easy way to spot it.

Enter column differences. First select the range containing all the formulae but leave out the header row(s). In Excel 2007 the command is found here:

• Home tab
• Find & Select
• Go To Special
• Select the "Column differences" option
• Press OK.

(A slightly quicker route is to press the F5 key followed by the "Special" button.) Now Excel will only select those cells where the formulae don't match with those in the first row. You can cycle through these cells by pressing the TAB key. You can even colour the cells in using the normal fill control.

This works equally well when you have lots of formulae in a column copied across to the right. Only this time - you guessed it - you would select "Row differences" instead.

These range differences are powerful tools that can be a real life saver. If you ever come across spreadsheets like the ones I've described then I heartily recommend getting used to using these tools.