_edited.jpg)
We all know that cell references in Microsoft Excel spreadsheets consist of a letter (the column label) and a number (the row label). Basically, it's like one giant game of battleships! But when you start to use cell references in your functions and formulas, you need to be mindful of the concept of cell addressing.
Here's my handy guide to get your head around it!
When using a cell reference in a function or a formula, you are going to have to consider whether it should be presented as relative or absolute.
I find the easiest way to explain this is to imagine you've got a stick of glue in your hand.
There are several cell references within your calculation and you are going to have to decide which ones you are going to stick down and which ones you are going to allow to move about.
Relative cell addressing is where you allow the cell reference to 'move about'
Absolute is where you apply a dollop of glue so the cell reference stays put.
There's a certain symbol we use in Microsoft Excel to help us 'glue' our cell references down. It's the dollar ($) symbol and now I've told you this, I bet you've noticed it knocking around in calculations you've seen before!
Each and every dollar ($) symbol you use against any cell reference acts as a dot of glue, sticking things down so that when you copy and paste the calculation elsewhere in your workbook, the cell reference remains the same.
When I use cell references within my calculations, I always use the following top tips to ensure accurate and successful cell addressing every time!:
1. Firstly, I check for cell ranges in my calculations. These are any sets of two cell references, separated by a colon, i.e. B:B or A14:A200. If these are there, I make sure I add dollar signs to all column and row references, i.e. $B:$B or $A$14:$A$200.
2. I'll next consider my individual cell references. Sometimes dots of glue, or absolute cell addressing, is just not needed, or only needed on the column letter or just the row number. Other times, I may need to stick down both the row and the column. To help me get this right, I always review the colour coding that appears when you write a calculation in Microsoft Excel.
This is a great example of colour coding within this SUMIF function. We've got three colours - Blue, Red and Purple.
These colours are presented in the formula bar at the top and we can easily visually reference them to our spreadsheet. In checking this, we can establish where best we can add our dollar signs to glue cell references down.
In this example, do you think we need to add any dollar signs? Answer at the bottom of the page!
3. When I want to add dollar signs, I'll use my F4 key to do so. Simply click in the middle or at either end of the cell reference and press F4. Dollar signs will appear in front of both the letter and the number of the cell reference, i.e.:
$F$4
If we press F4 again, we can toggle the position of the dollar signs to this:
F$4
then to this:
$F4
then finally back to this:
F4
So there you have it! My handy guide, all about cell addressing in Microsoft Excel! If you found this post helpful, give it a like and maybe share it to your socials so it might help someone else!
Answer to question - in this example, we don't actually need to fix any of the cell references down with dollar signs, but best practice would be to fix the ranges (blue and purple) and add a dollar to the F or F4.
Comments