Stop Moving! - Absolute Cell Reference
Have you ever tried to copy a formula to another cell and noticed the result is not the same as the cell that you just copied?
Example
In my Crime Data, I used an ‘INDEX-MATCH’ formula to find out which category the offence falls into (Offence Category). Notice I’ve inserted some $ (dollar) signs in the cell reference.
Absolute cell referencing becomes useful when you want to copy a formula and either.
Want the row to remain the same, or
Want the column to remain the same
To make a cell absolute (locked), a ‘$’ (shortcut: F4) needs to be inserted between the row, column or cell reference.
=INDEX('List of Offences'!$A:$A,MATCH(B2,'List of Offences'!$B:$B,0))
I’ve inserted $ signs in columns A and B as I don’t want those to change when I copy the formulas down to the remaining rows. I’ve omitted $ signs in “B2” as that is the cell I want to change each time, meaning each row has a different offence.
What happens if I didn’t put the $ signs and copied and pasted the formula to a different cell?
I get an “#N/A” error. Why? Because there is nothing in cell G2 (no lookup_value) and in the ‘List of Offences’, there is no data in columns F & G.
In the above image, I’ve inserted $ signs. However, there is a slight difference:
Why did I put a $ sign before B2? Because my lookup_value is in column B. If I didn’t it would have changed to G2 when I copied the formula (as shown earlier). I didn’t put one before the 2, i.e. $B$2 because I want the row reference to change.
It is important to know where to put the $ sign depending on what you’re trying to achieve, if you were to copy a formula from one cell to another:
Shortcut tip: to insert and remove dollar signs quickly press ‘F4’.
Note:
"Stop sign" image sourced from forexcrunch.com
Comments