Following on from the previous post, once you’ve started on the path of setting out your model in blocks of related cells, you may become obsessed with knowing that all the cells in the block contain the same formula. After all, there’s no point in arranging your spreadsheet into nice blocks of logically consistent cells, if one or more of the cells in a block has a different formula to the others.
Fortunately, Excel provides a neat little shortcut to help you quickly check a range of cells for logical consistency. It is contained (buried?) in the Edit>Go To menu, which itself can be accessed using Ctrl G or F5.
Once you click the “Special” button you get a whole list of useful goodies, including the two items we are interested in: “Row differences” and “Column differences”. Unfortunately you can only perform one of these operations at a time, so if you’re checking a range which is two dimensional you’ll need to first do one and then come back and do the other.
As an example, let’s say you have a block of cells which should all have the same logical formula. Let’s assume you gave your spreadsheet to a colleague and they sent it back to you and now you suspect they messed up some of the formulae. Here’s how to quickly figure out where the error is …
Firstly select the range of cells you want to check (see right). You’ll notice that the top left cell is the active cell. We’ll be checking all other formulae in the range against this cell. Next, hit F5, click Special and select Row differences. After you click Ok, any cells in the block that do not contain the same logical formula as the cells in the first column (i.e. C) will be selected. It is probably best at this point to shade all of these cells some garish colour so that they stand out. Then go and fix them.
Now you’ll need to check Column differences, since it is possible that formulae for an entire row have been changed. Row differences will not spot this, but Column differences will. To do this, reselect the block and repeat the procedure described above, except this time select Column differences.
This process is a bit convoluted, but it works. Of course, you could record two macros to do this and put them in your Personal.xls file. Then allocate keyboard shortcuts to these macros and voila!, you can perform this check really quickly any time you like. Another alternative it to write a macro that examines the entire range in one step and compares it to the active cell, shading any inconsistencies in a chosen colour. That way you only have to run the check once, instead of twice.
Here’s some code you can use to do this:
Dim Cell As Range
Dim Formula As String
Application.ScreenUpdating = False
Formula = ActiveCell.FormulaR1C1
For Each Cell In Selection
Cell.Interior.ColorIndex = xlNone
If Cell.FormulaR1C1 <> Formula Then
Cell.Interior.ColorIndex = 3