Relative and Absolute Cell References In Numbers

When you create formulas in Numbers, the references to other cells are relative to the location of the formula. So when you move or copy the formula into another cell, the references follow along. But you can use absolute cell references to force the formula to always refer to the exact same cell, no matter where the formula is placed.

You can also watch this video at YouTube (but with ads).

Video Transcript: 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.

Comments: 3 Responses to “Relative and Absolute Cell References In Numbers”

Rich Howell

10 years ago

Great explanation. Short, simple, and to the point. This was a skill I really needed. Thank you!

Robby

Gary, I noticed the cell reference stayed the same but the formula actually didn't change. Note first row was multiplied by Cell G2 and the second row was added to G2.

Tim Law

TQVM for the tip, Gary. This video is very helpful and informational.

Comments Closed.

Relative and Absolute Cell References In Numbers

Comments: 3 Responses to “Relative and Absolute Cell References In Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information