Spotting Errors

In a workbook, I like to be consistent in the use of columns across all worksheets. If the first month of my model is in column L in one worksheet, then it will be the same in all sheets.

I have found that this method is really useful in reading formulas because you can promptly see a displaced reference that points to a wrong column.

This week, I wanted to quickly see if I had formulas that might be referring to a wrong column. In order to do so, I switched Excel in R1C1 reference style and simply used Excel’s Find functionality to look for C[1] and C[-1] across the workbook.

As an example, I have a formula to get the revenues in another worksheet. In a consistently structured workbook, that formula would look like this in R1C1:

=Rev!R14C 'Refers to the Rev sheet, row 14 and same column

However, if my formulas are shift by one column, they might look like one of these:

=Rev!R14C[1] 'Refers one column to the right
=Rev!R14C[-1] 'Refers one column to the left


If you find some offsetting errors, simply replace the [1] or [-1] with nothing. Beware of collateral damages by clicking “Find All” and reviewing the list of results before replacing all occurences.

Other related post:

R1C1 Enlightenment
Seven tips to develop a structured model