Gideon’s Excel Tips, no. 5: In cell references, what exactly do the dollar signs do?

Today’s Quick Tip is the simplest so far, but it is fundamental, so if you’re not quite sure what absolute and mixed references are, it is worth a read. Everyone who writes formulae in Excel should understand this one thoroughly!

The quick answer to the question in the title is that careful use of dollar signs in cell references to make absolute or mixed references saves time because it can allow us to reuse the same formula over an entire range, rather than having to tweak every formula separately.

If you would like to look at the examples in this blog on your own computer, you can download the spreadsheet from my website here.

There are three types of cell references, relative, absolute and mixed.

Relative references

Most formulae in Excel use references that are relative, that is, the cells referred to are always in the same position relative to the position of the formula. The example spreadsheet contains some sales data for janitorial supplies sold in Germany. First, let’s add formulae in column G to calculate the sales value for each sale by multiplying the Units and Price in columns E and F.

We enter a formula at the top in cell G6 like this: = E6 * F6 and press Ctrl+Enter. (Pressing Ctrl+Enter rather than just Enter enters the formula as usual but the active cell stays where it is rather than dropping down a cell, so it will save you a little time if you want to use autofill.)

Now copy the formulae down either by double-clicking the autofill handle or by using the keyboard technique I described in Excel Tip number 4.

Now check that the new formulae are pointing to the correct cells by selecting one or two random ones and pressing the F2 key. This takes you into Edit mode and highlights visually which cells are referred to in the formula.

The references in these formulae are relative references: each one says, ‘Take the number two cells to my left and multiply it by the number one to my left’.

Absolute references

Value Added Tax or VAT is paid in Germany and the standard rate is 19%. In column H we’ll work out how much this is for each row in the table. The VAT rate is already entered into cell C3. We’ll calculate the VAT due by multiplying each sales value in column G by the rate in C3.

First, we’ll see what happens if we try to use an ordinary relative reference for our first formula.

The reference to the VAT rate, coloured red in Edit mode, slips down when we copy our formula down and ends up pointing to one of the Product codes, giving a #VALUE! error. In the original formula in cell H6 the reference to the VAT rate needs to remain pointing at cell C3 when we copy it down into the cells below. We need to stop the row number in the reference from changing. We can do that by adding dollar signs to the reference to C3, making the reference an absolute reference, $C$3.

Now, when we copy the formula down and test the new formulae, each of them contains a reference, coloured red in Edit mode, to the VAT rate in cell C3. They all work!

So far we have seen relative references with no dollar signs in and absolute references with two dollar signs in, one before the column letter and one before the row number. When we use an absolute reference in a prototype formula, whether we copy the formula down or to the right, the new formulae contain references to the same cell as the prototype. This is what we want when we are referring to a parameter in a single cell, like a VAT rate.

Mixed references

In some situations we want exactly one dollar sign in a reference and these are called mixed references.

There is another example on the second tab in the practice spreadsheet. Here we have a quarter’s sales forecast for seven sales divisions for the company. They are shown in Euros in column C. We would like to convert them to British Pounds, US Dollars and Japanese Yen using the rather out-of-date exchange rates shown in row 4.

If we are careful where we put the dollar signs, we can create a formula in cell D5 that can be copied down and to the right and all of the new formulae should work: we get 21 different formulae for the price of one!

The basic calculation is just to multiply the sales value in Euros in column C by the exchange rate in row 4. So – not worrying for the moment about the dollar signs – the prototype formula in cell D5 should be something like this: = C5 * D4. The question is: where should the dollar sign or signs go in the two references C5 and D4?

Think of curtain rails. The reference to C5 needs to be free to run down the rows when we copy the formula down but it needs to be fixed to column C when we copy the formula to the right. It is as if it runs up and down a vertical curtain rail in column C. So the C needs to be fixed with a dollar sign in front of it but the 5 should not have a dollar sign in front of it.

The reference to the exchange rate in D4 is the other way around. It needs to be able to run along a horizontal curtain rail on row 4, so we need to fix the 4 with a dollar sign, but not fix the column letter D.

One final point before viewing the GIF: you can enter the dollar signs by just typing them in, like I did for the VAT example. But it is much quicker to use a keyboard shortcut. To enter dollar signs into a cell reference while typing a formula, make sure that the cursor in the formula bar is on the reference and press the F4 key. Pressing it once inserts both dollar signs; pressing it again gives a mixed reference with the row fixed; pressing it again fixes the column; pressing it a fourth time takes you back to a relative reference with no dollar signs. So the F4 key cycles round all four permutations of where the dollar signs can go.

Let’s make our formula and enter it into all the cells in the range. We can do this by selecting the whole range D5:F11 before we start and then entering the formula using Ctrl+Enter rather than just Enter. Then we’ll test some of the new formulae by selecting them and pressing the F2 key.

We could have used autofill to copy the formula down and to the right but this would have copied the format of the original cell as well as the formula. We have used the Ctrl+Enter technique here because it leaves the formats of the cells unchanged.

Excel Masterclasses for aspiring power users

If you have found this tip and the previous four easy, then you might be interested in our new range of Masterclasses. They are intended for Management Consultants and other people who need a higher level of Excel skills than is available from mainstream training companies. The courses are intended for analysts and managers who need to become true Excel power users.

The full range of courses is outlined here.

The first course to be launched is a two-day course on the fundamental subject of Formulae. If you are interested in any of the subjects taught in our more specialised courses such as creating dynamic text reports (as in the image above), sensitivity analysis using probability distributions as inputs or what-if analysis, they you should really consider doing the formula course first. For more information on the Formula course or to book take a look here.

The courses will be run using webcasting technology in real-time and with small groups, so there will be plenty of interaction and lots of practical exercises. For the first course we will be using UK timings, starting at 9AM UK local time, but you are welcome to join from outside the UK, and the first course is scheduled for 13-14 June 2019. The course will be taught in English.