Absolute and relative cell referencing is one of the most important concepts when you work with Excel
formulas.
It's important that you master this. Until now you've seen me use relative cell referencing. Here for example
where we have our list of items price and we want to apply this discount rate we can use this formula.
We take the price and we deduct the full price of the discount from it.
Now when I pull this formula down, the references shift down with the final result so they're always
in relation to the formula cell.
Now I can also write this differently.
I can say =B5*(1-D5). Regardless of the way I write this
in both cases I'm using relative cell referencing. I want both cells to shift when I pull the formula
down.
But what if my setup was different?
What if I had a global discount rate on top here that I wanted to apply to all of these?
How would I write this formula?
Let's just hide these and see if we can use the same concept we've used before.
I'm going to take the price multiplied with 1 minus the discount rate. Close bracket,
press enter. Can I pull this down?
Well, let's try it.
I get problems.
Why?
Because I'm using relative cell referencing for both sides.
This means when I pull down the formula this reference for my global discount is going to come down.
That's not something I want. I want to fix this reference.
I don't want it to move when I pull down the formula.
This is where you need to use absolute cell referencing. Which means you need to add dollar signs to
the column as well as to the row argument.
Now you can do this manually by looking for the dollar sign on your keyboard and adding it in.
But there is a key that helps you do this in one go and that's the F4 key.
If you go to the cell reference you want to fix, click on F4, it puts both dollar signs there.
If you click it again it puts a dollar sign under row which means only the row is fixed. Click it again
only the column is fixed.
Click it one more time you go back to relative cell referencing.
So this is a toggle. We want it fully fixed.
I'm going to click it once to get the dollar sign for both the row as well as the column.
This means that this entire cell reference is fixed.
If I pull down this formula, H3 will stay H3.
Let's try it.
Let's just double click on the side. Go to the bottom and check, H3 stayed H3.
Okay so now let's do another example here.
I also want to calculate the price for February. But I want to use the same price here, the same global
discount.
But I want it applied to both Jan and Feb.
I know that I need to fully fix the global discount. But now what do I have to do with my prices?
Let's check what happens if I keep the same formula as I did in the last example.
So I'm gonna go with price multiplied with 1 minus the global discount. Press F4 to fix it close
bracket and press enter.
Pulling this down, so let's just double click, is gonna work right?
Everything has the correct cell referencing. But now I'm gonna highlight this and pull this also for
February.
I want to see the same numbers here.
I want my formula to still use these prices.
Why doesn't it work? It's because I'm using relative cell referencing. It's shifting the B5 to C5.
How do I have to adjust my first formula to get it right so that if I pull this down and across my
referencing works correctly? What do I have to fix for B5?
Do I have to fix the row or the column?
I don't want to column to shift right? So I have to just fix the B.
I'm going to press F4, once, twice, three times to get the B fixed, press enter.
Now let's double click.
Push it down and pull to the side and everything works well.
So my tip for you is always go to the bottom right hand corner of your data range, go to edit mode and
make sure that your references are correct.
Let's do one more example. This time I want to apply different discounts for each month.
I want to use the same prices but in January we're going to have a big discount.
In February we're going to have a 10 percent discount.
So try writing this one or think about how you would write this one before you watch me write it.
Now this time.
Let's try to get it right from scratch.
We're going to take the prize.
What do we have to fix here?
The column or the row? The column, right? Because we don't want the column to shift when we pull our formula
across.
But we do want the row to shift when we pull the formula down because it should go to the next price
and the next price. So I'm going to click it for once, twice, three times to get the column fixed.
Let's do one minus the monthly discount.
What do I need to fix here?
The column or the row? I need to get the row fixed, right? Because when I pull this formula down I don't
want the row three to go to row four. But when I pull the formula across I want the column to shift.
So I can't fix the column but I have to fix the row. Press F4 once, twice, to get the row fixed,
close bracket, press enter, and let's pull down and across.
Click on the right hand corner, go to edit mode,
It's 40 times 10.
That looks great.
Let's just check January as well.
40 times 50.
Perfect.
Okay so that's how you can use absolute and relative cell referencing to get your formulas correct.
No matter how your data is setup.