lesson 3-7-3 relative vs absolute referencing

Using Dollar Signs to Lock Cell References

Have you ever had to do the same calculation over and over again? It's a bit boring, right? Spreadsheets are brilliant at copying formulas for you, but sometimes they can be a bit too clever and change things you wanted to stay the same! Today, we're going to learn a superpower that tells a spreadsheet exactly which parts of a formula to lock in place. This is a key skill used by Financial Analysts and Data Scientists to build powerful models that can predict profits or analyse sales data. By the end of this lesson, you'll be able to create much smarter spreadsheets that can handle changes in things like tax rates or prices with just a single click.

Learning Outcomes

The Building Blocks (Factual Knowledge)

Recall that a spreadsheet is organised into a grid of cells.

Describe the difference between a formula and a function.

Describe the difference between relative cell referencing (which changes when a formula is copied) and absolute cell referencing (which stays fixed).

The Connections and Theories (Conceptual Knowledge)

Analyse why copying a formula with a reference to a single value (like a tax rate) can cause errors.

Evaluate why using an absolute cell reference makes a spreadsheet model easier to update and more reliable.

Digital Skill Focus: Spreadsheet Modelling

The Skills and Methods (Procedural Outcomes)

Apply arithmetic operators to perform calculations in a formula.

Create a formula that uses a relative cell reference.

Create a formula that uses an absolute cell reference ($A$1) to refer to a fixed value.

Apply the fill handle to copy formulas down a column correctly.

What's the problem with being 'relative'-ly clever?

In our last lesson, we built a simple financial model for a tuck shop. We saw how we could write a formula once (e.g., to calculate profit) and then use the fill handle (the little square at the corner of the cell) to drag it down and instantly apply it to all the other products.

When we do this, the spreadsheet is smart. It uses relative cell referencing. If your first formula is =B2-C2, when you drag it down to the next row, it automatically changes to =B3-C3. This is incredibly useful, most of the time...

Your teacher will now demonstrate a problem with this approach particularly when formulae in the sheet rely on the value in a fixed cell.

Task 1 The Unchanging Price Tag

Get organised

Open your tuck shop spreadsheet from the last lesson. If you don't have it for some reason or you didn't manage to get it finished, download yourself a new copy from here.

Open the spreadsheet and click the Enable Editing button on the yellow warning bar at the top.

Let's get absolute!

In cell C6, type the label: VAT Rate.

Format cell D6 as 'Percentage' (Home > Number > Percentage (%)). This tells Excel to divide the value in the cell by 100 before it uses it in any calculations.

Type the value 20 in cell D6. It should display as 20%.

Now, using your mouse, right click on the column F header and choose Insert. This will insert a new column before column F which now becomes the new column F (confused?).

In cell F1, type the new column header, VAT.

Now for the magic formula! In cell F2, we need to calculate the VAT on the purchases of goods which we have sold. The formula is: Cost price * VAT Rate.

To tell the spreadsheet to lock onto the VAT rate in cell D6, we use dollar signs ($). Type the following formula exactly into cell F2: =E2*$D$6 and press Enter. The correct VAT for the first item should appear. Yay!

Click on cell F2 and use the fill handle (the small square in the bottom-right corner) to drag the formula down for all your other products.

Now, let's do the same for VAT on goods sales. Insert a new column before column H (which now becomes the new column H).

Click on cell H1 and type the new column heading VAT.

To calculate the VAT on goods sold, we use a similar formulae than the one we used to calculate VAT on purchases, including the absolute cell reference for the VAT Rate. In cell H2, enter this exact formulae: =G2*$D$6 and press Enter.

Drag fill the formula down the column and, voila!

Test your model! Click on cell D6 and change the VAT rate to, say, 25. Did all the VAT amounts in columns F and H update automatically?

Outcome: I will have a spreadsheet that correctly calculates the VAT for a list of products. My formula will use an absolute cell reference so that I can change the VAT rate in a single cell and have the entire model update instantly.

Well done!

Excellent work! 👏 You've now solved the problem using an absolute reference. Your model is powerful because you can change the VAT rate in one place and everything updates.

But what if your spreadsheet was hundreds of rows and columns long? Would you remember what $D$6 actually was? Professionals make their models easy to read by using 'named ranges'. Instead of a cryptic cell address, what if we could give that cell a memorable name and write our formula in plain English?

Let's learn a new technique that does exactly that.

Task 2 Beyond the Dollar Sign - Give it a Name!

You have a working spreadsheet from Task 1 that uses an absolute reference ($D$6). This is good, but we can make it even better and easier to follow.

Click on the cell that contains your VAT rate (cell D6).

Find the Name Box. It's the small box on the top-left, just above column A, which currently says "D6".

Click inside the Name Box, delete the text D6, and type a new name for this cell: vat_rate

Important: Press the Enter key to confirm the name.

Now, click on your first formula in cell F2. It should currently say =E2*$D$6.

Change the formula to use your new name. Delete $D$6 and type vat_rate instead. The new formula should be:

=E2*vat_rate

Press Enter. You'll see the result is exactly the same, but the formula now makes much more sense when you read it.

Use the fill handle to drag this new, improved formula down for all your other products.

Do the same for column H.

Outcome: I will have improved my spreadsheet model by replacing the absolute cell reference with a named range. This makes my formulas much easier to read and understand, which is a key skill for creating professional-quality spreadsheets.

Well done again!

Today you have learnt how to use absolute cell references to lock parts of a formula, making your spreadsheet models more powerful and easier to maintain.