Test your skill at cell referencing in Excel

Let’s talk about referencing cells. This should be a relatively straightforward topic, but my years as a model auditor have opened my eyes to how challenging this issue can be.

Confusing absolute and relative cell references

Many model formulas 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 formulas reference the corresponding values in cells A1:F6, ie, 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 (eg, 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 (ie, 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 formulas 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. Many modellers get referencing wrong all too often. Want to see how you fare?

Simple example

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

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

=F16*G15*G12

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 practise, practise, practise?

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, eg,

=A1
=Sheet1!A1
=‘Another Sheet’!A1

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, eg,

=[Book2]Sheet1!$A$1

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

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:

=E7+(Sheet2!C4+Sheet2!C6)/Sheet1!E3

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

=E7+(Sheet2!C4+Sheet2!C6)/E3

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.

Word to the wise

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.

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.