Hi, this is Gary with MacMost.com.
Let's take a look at using Relative and Absolute Cell References in formulas in Numbers.
I'm using Numbers version 3 here.
Let's take a look at the difference between Relative and Absolute cell references in formulas.
Let's populate this table with some sample data and let's just put some random numbers
in here and let's go ahead and then perform some calculations based on this.
Let's just add this to this and subtract this.
There we go.
The great thing is that we can then copy and paste this formula from here to here and it
will perform the calculation on a different row.
So in this case you can see it is showing you right here it is using these three cells
to get that number.
If I double click on it I can see it is B2 + C2 - D2.
Great.
Here it is using these three cells.
I copied and pasted and the cell references changed to reflect the change in position.
I went from B2 to B3.
I increased the row by one.
So it increased the row by one for each one of these.
So now instead of B2 it is B3.
Instead of C2 it is C3 and instead of D2 it is D3.
I've got a new formula.
I can continue doing this as much as I want and it will continue to change.
So in this case you can see it is using these.
There we go.
It's all from row 4 now instead of row 3.
These are relative cell references.
As I move the formula down one row it is going to move all of the references down one row
as well.
Now let's add a little wrinkle to this.
Let's go and add a new row here, I'm going to erase this formula, I'm going to erase
all the formulas here.
We're going to use a new formula.
In this row we are actually going to put another number.
We're going to put the number 2.
The formula now is going to be this plus this plus this times what is in here.
So B3 + C3 + D3 X G2.
The answer is going to be 86 which would make sense.
But here is what happens if I copy this formula and I paste it.
Now notice it has moved all of the references down one because they are all relative.
Up here it is these three in this one.
Down here it is these three and this one which is not what we want.
We want it to stay here.
We want it to always use this number 2.
So we need to switch the reference for this cell from a relative one that would change,
to an absolute one that will not change.
So let's go ahead and do that.
Let's delete the formula here.
There we go, delete it.
Go to this formula here and edit it.
We want these to remain relative and this one to be absolute.
You notice there is a little menu arrow there.
We can select that and we can select preserve row and or preserve column.
We are not moving things around in columns.
So we don't really care about columns here.
But we'll just set both of them to be absolute.
So in other words no matter where this formula gets copied and pasted this cell is going
to remain exactly at G2.
It puts dollar signs in front of both the column and the row to let us know that these
are now absolute.
This is pretty standard spreadsheet stuff going back decades using the dollar sign there.
So the result is going to be the same here but if I copy and paste you can see how it
is using these three but it is still using this one.
If I look in here I can see there is the dollar sign.
$G$2.
No matter where I paste this it is going to continue to use this absolute cell even though
it is using these relative cells.
The same concept applies whether the cell is in this table or another table or another
sheet.
You want to make sure that if you always want it to be absolute, to be preserved to be this
exact cell G2, you want to turn that on in the formula right there.
Then for these you want them to move around with it.
You want to make sure that it is turned off.
That is how you use relative cell references and absolute cell references in your formulas.