Formulae: Seeking Inconsistencies Consistently

In previous Excel modelling articles, the importance of constructing consistent formulae has been emphasised. But how do you check this is the case without using specialist model auditing software? By Liam Bastick, Director with SumProduct Pty Ltd.

Query

I have just started a new job and inherited an Excel model that will become my responsibility. How can I check that formulae in the model have been copied across consistently without purchasing specialist software?

Advice

Model formulae should be consistent. Indeed, I advocate wherever possible use only unique formula across a row or in a block of calculations. End users often see “one-off” formulae as plugs in data and it can lead to mistrust of a model. Using fewer unique formulae makes models more “trustworthy”, easier to understand and quicker to review.
But how do you check formulae have been copied correctly?

Example

Consider the following block of data:

Let’s assume this data is supposed to refer to a similar block of data elsewhere. How can we tell if the formula has been copied across and down correctly? Inspection by eye achieves nothing here.

One option is to use the keyboard shortcut CTRL + ` (the character is the key to the left of the 1 on a standard QWERTY keyboard):

This shortcut toggles cell values with their content (i.e. formulae). This will show formulae which have not been copied across properly, but this is still fraught with user error (can you spot the relevant cells?) and would be cumbersome with vast arrays of data.

Instead, there is a simpler, automatic approach. Select all of the data (click anywhere in the range and press CTRL + *). Then use the keyboard shortcut CTRL + \ viz.

This automatically selects all of the cells whose contents are different from the comparison cell in each row (for each row, the comparison cell is in the same column as the active cell).

CTRL + SHIFT + \ selects all cells whose contents are different from the comparison cell in each column (for each column, the comparison cell is in the same row as the active cell). In this example, where a formula is supposed to be copied across and down, there will be no difference.

These cells can now be highlighted and reviewed at leisure.