Partner The Knowledge Base
Share this content
Dollar sign
istock_kizilkayaphotos

EZ guide to cell references – the great Excel deception

by
18th Nov 2016
Partner The Knowledge Base
Share this content

Simon Hurst continues his Excel Zone tutorial series by exploring cell references - the great Excel deception.  

Objective

Create spreadsheets more quickly and efficiently with a dramatic reduction in the likelihood of error by fully understanding the use of the dollar signs in cell references. In addition, to look at an important shortcut to speed up entering cell references.

Additionally, to get you to read beyond the first paragraph…

Source: Simon Hurst

Overview

One of the issues that concerned me about including this episode of the EZ guide series was whether anyone would read it. Many Excel users would see the use of dollar signs to create absolute or relative cell references as such a fundamental technique that they assume that the majority of Excel users already know all there is to know about it. Other users would see it as an unnecessary complication, without which they have been able to use Excel quite satisfactorily for years.

Knowledge of absolute and relative references is very far from universal. I tend to use it as my 'litmus test' of Excel capability when I'm training and lecturing. Rarely do more than 50% of the audience say yes to my standard question: “Are you fully competent in the use of absolute and relative cell references that is the use of dollar signs in cell references?” This is true both of lecture audiences (usually accountants, or those working with financial data), and the new-intake courses I run for a few accountancy practices.

Being able to use dollar signs to 'fix' aspects of a cell reference is vital, not only because of the amount of time it can save when setting up a spreadsheet, but also because it has a significant effect on the likelihood of introducing errors in the first place and can also greatly reduce the effort involved in auditing and reviewing a spreadsheet. In both cases, understanding absolute and relative references keeps the number of 'different' formulae you need to create to a minimum.

Although there is plenty of academic research to prove the point, common sense would suggest that the greater the number of formulae you type in, the greater the possibility of getting one or more of them wrong. Similarly, when reviewing a spreadsheet, you would need to check each different formula individually. Where one formula has been entered, and then that same formula copied to other cells, you need to check that one formula is correct and that the copy operation has been performed correctly. Not only is this easier to do manually, but it's also easier for Excel, or a third-party auditing tool, to do it for you.

Example

The Excel lie revealed

Having established how important absolute and relative references are, let's look at a simple example. Here we have a list of products and prices and we want to know how much discount we need to apply for a given discount rate – initially 10%. We could create a formula in cell C4 that multiplied the contents of cell B4 by the contents of cell C3:

Source: Simon Hurst

If you are not familiar with absolute and relative formulae, you might think that:

=B4*C3

multiplies the contents of cell B4 by C3. In fact, a cell reference without dollar signs doesn’t refer to any particular cell, but instead to a 'relative' position. In our case, our formula has been entered in cell C4 so:

=B4*C3

actually refers to the cell one column to the left multiplied by the cell one row above. Excel lies to us by showing this as B4*C3. If it really was B4*C3 we could copy it to another cell and it would remain as B4*C3. This is clearly not what actually happens. If we copy our formula in C4 down to C5 Excel copies the relative position, so the formula still refers to the cell one column to the left, multiplied by the cell one row above:

Source: Simon Hurst

Column D shows the formulae entered in column C and reveals the Excel lie. Clearly, if the formula in cell C4 really referred to cells B4 and C3, when it was copied it would still refer to B4 and C3. It doesn't. Instead, it is the relative position that is copied.

We can force Excel to tell the truth by using one of the Working with formulas options in File, Excel Options:

Source: Simon Hurst

The R1C1 reference style option displays our formulas as they really are, rather than after the substitution of the cell references:

Source: Simon Hurst

Although, in this example, this makes it clearer what is actually going on, in general use most of us would find R1C1 referencing much more difficult to follow than the use of column letters and row numbers, hence Excel translating the actual references for displaying to us.

Fixing our refence with a dollar sign

Now we know how Excel works, we can start forcing it to do what we want. In our example, we want our reference to the percentage in cell C3 to stay fixed on cell C3, rather than being the cell one row above. We just add a dollar sign in front of the row reference to do this. Our formula becomes:

=B4*C$3

We are still referring to the cell one column to the left, but we are now fixing our refence to the cell on row 3, not the cell one row above. This allows us to copy this formula to the rest of the column:

Source: Simon Hurst

We can see that the dollar sign has fixed the reference to row 3 when we copy our formula down.

Where to put your dollar signs

You will have noticed that each cell reference contains a column letter and a row number and, in our example, we just entered a single dollar sign in front of the row number. There are four options for using the dollar signs in each reference:

  • =C3 No dollar signs– a reference just based on position. Wherever the cell is copied to, the reference will change
  • =$C$3 Dollar signs in front of the column and row references. Wherever the cell is copied to, the reference will continue to refer to the same cell.
  • =C$3 Dollar sign in front of the row element of the reference only. If the cell is copied up or down to other rows, the reference will continue to refer to row 3, if the cell is copied left or right to other columns, the column reference will change.
  • =$C3 Dollar sign in front of the column element of the reference only. If the cell is copied left or right to other columns, the reference will continue to refer to column C, if the cell is copied up or down to other rows, the row reference will change.

The F4 keyboard shortcut

The dollar signs can be entered manually, or you can use the F4 keyboard shortcut to cycle through the four alternatives above. F4 affects the reference selected or that contains the insertion point or is adjacent to the insertion point.

Note that, when you drag to select a range, if you immediately press F4 both elements of the range will be changed. If you 'go back' to a range, you need to use F4 for both elements individually or select the whole range, in the formula bar or within the cell, before using F4. If you select multiple references, with different absolute/relative settings, F4 will first synchronise the references and then change all of them together.

We can see how the ability to set column and row references separately is important by extending our example to use different discount rates in other columns:

Source: Simon Hurst

By setting our formula in cell C4 to:

=$B4*C$3

We fix our reference to the Price column ($B), whichever column we copy the formula to and fix our reference to the percentages row (C$3), whichever row we copy it to. This means we enter a single formula in cell C4 and copy it down to C13 and then copy the whole block across to column E.

Conclusion

This simple example shows how an understanding of absolute and relative references allows us to create, and check, a single formula and then use two simple copy operations to copy it to 29 other cells. Without knowledge of the technique, we would have needed to enter, and check, 30 separate formulae. This is just for 10 products. The difference would be far greater if we were talking about hundreds or thousands of different products.

Whether you find the use of the dollar signs second nature or hideously complicated, don't underestimate how important they can be and the difference that they can make.

Replies (3)

Please login or register to join the discussion.

avatar
By philrob
18th Nov 2016 15:02

On the Mac the F4 key is also cmd-t (don't know if it works the other way around)

Another quick tip - Double clicking on the bottom right hand 'grab' indicator in a cell (the small green square) copies that cell down the entire length of the adjacent column.

Thanks (0)
avatar
By Fastlane
19th Nov 2016 03:27

I'm dismayed, but not surprised, by your comment "Rarely do more than 50% of the audience say yes to my standard question: 'Are you fully competent in the use of absolute and relative cell references that is the use of dollar signs in cell references?'”

I think the primary cause of this problem are training organizations (the world over!) that don't cover cell referencing in their Excel Basics/ Fundamentals courses - instead leaving this core concept until their Intermediate or Advanced courses! Go figure? How can they explain the grid and formulas (the very DNA of spreadsheets) without also covering cell referencing?

Thanks (0)
Replying to Fastlane:
Simon Hurst
By Simon Hurst
19th Nov 2016 09:10

Hi Fastlane - I'd certainly agree with that. The Excel courses that I run have always covered this either right at the start, or after a brief introduction to the Excel screen and interface or, for more advanced courses, as a suggested pre-requisite.

I'm also delighted that the ICAEW Spreadsheet Competency Framework includes it as a core item within their 'General User' category of users. (The only more basic level is for users who only enter data into existing spreadsheets, rather than create their own).

I think I'd find it hard to teach much about Excel without using absolute and relative formulae from the outset.

Thanks (0)