Are You Absolutely Sure?

Liam Bastick highlights some of the common mistakes prevalent in financial modelling / Excel spreadsheeting.  This article considers common mistakes in cell referencing.

Common Referencing Mistakes

I feel a little embarrassed writing this article.  I want to bring up what should be a relatively straightforward area of modelling: referencing cells.  However, as a very experienced model auditor, it no longer surprises me how frequently modellers get this wrong.

1. Confusing Absolute and Relative Cell References

Many model formulae are copied across rows and down columns.  Some of these calculations are quite complex.  It’s important that the references in these calculations refer to the correct cells – and sometimes they do not.  Some modellers seem to be perennially confused between relative, absolute and semi-absolute references.

Let me explain.  Consider the following example:

In cell A8 I have created the formula =A1.  This is what is known as a relative reference.  As I copy the formula across and down through the range A8:F13 the formulae reference the corresponding values in cells A1:F6, i.e. as I move across one column the reference moves one column to the right, as I move down one row, so does the reference and so on.

If I am in ‘Edit’ mode in Excel (e.g. by clicking in the formula bar or pressing the F2 function key), the F4 function key will toggle cell references as follows:

Pressing F4 whilst in ‘Edit’ mode for a formula (or part of a formula) toggles the dollar ($) signs in the cell references.  These signs anchor the row and column references.  A dollar sign before a column reference anchors the column (that is, the column reference will not move when the formula is copied across a row); a dollar sign before a row reference anchors the row (that is, the row reference will not change when the formula is copied down a column).  These dollar signs may simply be typed in; the function key entry is not necessary.

To show the effect, =$A$1 produces

This is known as an absolute reference.  All cells point to the same reference: A1.  The other two options produce slightly more variety though.  For example, =A$1 produces

In this example, the formulae allow cell references to refer to different values in row 1, but other rows may not be varied.  This type of reference is known as a semi-absolute reference.

Finally, =$A1 produces

In this final example, the column (A) is anchored instead – another example of a semi-absolute referencing.  It is possible to have a formula that has all types of referencing in one calculation.  The trick is to get the referencing right first time every time – and this comes with practice.

Talking of which, you may have read all of this so far and thought this article seems trivial.  I wish it were.  However, many modellers get referencing wrong all too often.  Want to see how you fare?

Try the following.  Give yourself, say, 30 seconds to attempt the following question from the attached Excel file:

In this “BEFORE” example, click on cell G16.  This cell contains the formula

This is producing a simple ‘times table’ grid, multiplied by a factor stated in cell G12.  The challenge is simple: can you put the necessary dollar signs in the formula highlighted (cell G16) such that the formula may then be copied correctly into cells G16:K20 as shown (below)?

Simple, yes?  Try it now and then compare your answer to the “AFTER” example.  It is amazing how often people make mistakes and / or cannot do it quickly.  This is a core skill in modelling and comes with practice.  If you found it trickier than you thought you would, don’t worry, I won’t tell anyone but may I suggest you practice, practice, practice.

2. Linking from Another Workbook

We get so used to linking cells from elsewhere in the same worksheet or a different sheet in the same workbook that it becomes second nature to expect relative cell referencing, e.g.

The problem is, this is not always the case.  If you link from another workbook, whether it is open or not, the reference will be absolute, e.g. 

Take care as often modellers do not notice this and then formulae produce incorrect results when copied.

3. Linking to Other Sheets from the Same Workbook

What could possibly go wrong here?  Believe it or not, this is the most subtle issue of all.  Often, we link to cells on other worksheets and then refer back to a cell on the same worksheet which causes the sheet reference to be referred to unnecessarily.

For example, imagine we were in Sheet1 cell A1 and wrote the following formula:

Do you see the Sheet1! reference is superfluous?  Instead, I could have written the following:

Not only is this slightly easier to read, it’s not as dangerous either.  If this formula were on a worksheet that was copied, depending upon how the sheet was copied, the formula may always refer back to cell E3 on Sheet1 which may not be what was intended.  Therefore, I strongly recommend that superfluous references are always removed.

And Finally…

People say I am an “expert” in Excel.  Not sure about that.  How do you think I know about all of these common mistakes..?  Until next time.