Welcome to our series of applied tips and tricks in Excel for accountants and financial analysts wherever you may be. In this article, Liam Bastick considers common mistakes in cell referencing.
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, 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 (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, eg
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 – and I will be taking a look at this next time.
Liam Bastick is author of an Introduction to Financial Modelling, which provides a simple walkthrough of the common perils and pitfalls of financial modelling. Presently, this book is only available on Amazon Australia but if anyone would like to buy a copy directly contact Liam via this link. AccountingWEB readers receive a 10% discount plus p&p (including tracking and insurance at cost).
For readers in Australia or those who just fancy a trip Down Under, Liam will be speaking at Excel Summit South 2019, a two-day Excel event in Brisbane, Melbourne, Perth and Sydney in late July to early August with speakers from Microsoft and Excel experts. For more dates and details visit the website. Especially for AccountingWEB readers there is a 10% discount on all prices – simply quote the code ACCOUNTINGWEB10 when purchasing.
About Liam Bastick
Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their strategic valuations team in London. He was also a senior member of the UK Post Office’s M&A and strategy teams and has worked for / assisted various other Australian modelling companies including BPM, Corality, Navigator Project Finance, PKF and SumProduct.
He has worked in the UK, Australia, Belgium, Denmark, France, Germany, Hong Kong, Indonesia, Malaysia, New Zealand, United States, Switzerland and Vietnam, with many internationally recognised clients, constructing and reviewing strategic, operational and valuation models for many high profile IPOs, LBOs and strategic assignments. Liam is a Fellow of the Institute of Chartered Accountants (ICAEW), a Fellow of the Institute of Chartered Management Accountants (CIMA) and is a professional mathematician.