Cell addresses were covered in a previous blog (What use is a cell address in a spreadsheet?). Next we move into how the addressing types (relative and absolute) can speed up creating your calculations across a spreadsheet.
One of the most useful facilities of a spreadsheet is that you can setup the calculation and copy it to other places to reuse it.
In other words, for example, you have numbers in columns that need to be added up. You write the calculation to add up the first column, check that it’s right, and then copy it across to the other columns.
Without the addressing ‘thing’ this wouldn’t work – image if it just adds up the same group of numbers every time?
Relative Address
This is where the first option – Relative Addressing – comes in.
Pretend your giving out directions from your house to the local supermarket, to a friend.
You start at your house, and explain where to go. However you didn’t say that you were starting at your house.
So, the friend leaves their home, and follows your directions – they end up somewhere completely different.
Moving over to the spreadsheet:
We want to add up the two numbers in each column.
Writing the first calculation, we can see it’s right. Ready for it to be copied to the next two columns.
MS Excel uses Relative Addressing as standard.
Rather than seeing the cell addresses (A1 / A2), it actually works out the location from the starting point – the selected cell.
In this case, it’s 2 cells above, plus, 1 cell above.
Which means that when we copy it, the directions stay the same, but because our starting point moves (first to B3, then C3), the destination changes – giving us the calculation we want.
Absolute Addressing
There maybe times that you want to refer to a specific cell in your formula – an address that doesn’t change.
This is is where Absolute (Fixed) Addressing comes in.
Looking at an example of when you might want to use absolute addressing:
We can write a simple formula to add the VAT to each cost. But when we reference cell H1 and copy it down, it will move to H2, H3 etc.
Making the formula wrong.
As relative addressing is the default, to make a cell absolute you need to “add something” in.
That “something” is the dollar sign ($).
=A2*$H$1
In the example above, the dollar signs are before the column letter and the row number – locking both parts.
You can manually type the dollar signs in – either as you enter the formula, or you can go back and edit it, when you know it’s right.
Note: I recommend the second option – write the formula, check it, then add the absolute references.
Now, as it’s copied down, the VAT cell (H1) remains the same – giving the right answer.
Give it a go yourself!
More tips and shortcuts on my YouTube Channel – subscribe to be notified of new videos.
If you’d like some tips, including AutoCalculations in Excel – sign up for my 15 Top Productivity Tips for Microsoft365
Related Posts
- Excel Paste Values – Get Exactly What You See, Every Time
If you’ve ever copied something in Excel and ended up with a completely different result than you expected, you’ve probably fallen foul of one of Excel’s little quirks. The good news is that once you understand Excel Paste Values, you’ll know exactly how to handle it – and it only takes a couple of extra… Read more: Excel Paste Values – Get Exactly What You See, Every Time - Excel Sparklines: The Tiny Charts Making a Big Difference
If you’ve ever stared at a column of numbers and thought “I just want to see the pattern” — Excel Sparklines are about to become your new favourite thing. These miniature charts sit inside a single cell, right next to your data, giving you an instant visual snapshot without the faff of building a full… Read more: Excel Sparklines: The Tiny Charts Making a Big Difference - Do You Know All the Excel Flash Fill Tricks?
If you’ve ever exported data and thought “why is everything jumbled together in one column?”, Excel Flash Fill is about to become your new best friend. It’s one of those features that looks impressive but is actually really simple to use — and once you know it, you’ll find yourself reaching for it again and… Read more: Do You Know All the Excel Flash Fill Tricks? - The Complete A-Z Guide to Excel Keyboard Shortcuts: Master Your Productivity
When most people think about keyboard shortcuts, Microsoft Word typically comes to mind first. However, as an Excel enthusiast, I’m here to tell you that Excel has its own powerful set of keyboard shortcuts that can dramatically improve your productivity. Today, I’ll guide you through the complete A-Z of Excel’s keyboard shortcuts for Windows users.… Read more: The Complete A-Z Guide to Excel Keyboard Shortcuts: Master Your Productivity - Complete List Of Options Where You Can Customise the Colour
When it comes to colour in your office files, we rarely move beyond changing the font or fill colour. But there is so much more to play with when you customise the colour. Read through the post, or jump to your favourite office suite – Google or Microsoft. Google Let’s start with Google, as it’s… Read more: Complete List Of Options Where You Can Customise the Colour