What’s the difference?

  • Tagged with:
  • Blocks,
  • Design,
  • Excel,
  • Inconsistencies,
  • Macros,
  • Spreadsheets,

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.

How to find the error

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.

Found two errors

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:

Sub Check_Block()
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
End If
Next Cell
End Sub

Happy checking!

2 Comments so far Join the Discussion

  1. Rickard Warnelid – Corality

    15th July 2009 at about 10am

    Darren,

    Thanks for sharing your VBA code.

    I am not sure I understand your statement about the limitations of the tools in the Go To menu. If you select the whole 2D-range and click ‘Ctrl+\’ you achive the same result as with the VBA code. Am I missing your point?

    I have also seen a solution where you use the XML info to apply the conditional formatting based on the formula directly in the conditional formatting dialog box (instead of using VBA) but I can’t recall it now. Will look around in my archive and see if I can find it.

    Regards,
    Rickard
    http://blog.corality.com

  2. Dan

    29th August 2009 at about 7am

    Interesting, I didn’t know this existed.

    I use the the error-checking options built into Excel which “highlight inconsistent formula in range” and add a green triangle to the cell.

    Other error checking options available include numbers stored as text and a few other things.

Join the discussion

Popup

Register your Interest

Please register to keep up to date with our developments.

Register your Interest

Please register to keep up to date with our developments.

buy paper | shopelix.com