How Cell References Automatically Adjust in Numbers

When you copy and paste a formula in Numbers it doesn't just paste an exact duplicate of the formula. Instead, it adjusts the cell references based on the locations of the original and the new location. Learn why and how it does this, and how to modify this behavior to fir your needs.

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

Video Transcript: Hi, this is Gary with MacMost.com. Let me show you how cell references work when you Copy and Paste formulas in Numbers. 
MacMost is brought to you thanks to a great group of more than 1000 supporters. Go to MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
Now you may have notices that when you're in a spreadsheet in an app like Numbers if you have a formula in one row and you copy and paste it into another row it automatically adjusts the formula to refer to the cells in that row. For instance, here I've got a simple table here, Date, Amount, and Price and I want to calculate the Total. So I can start a formula right here, multiply the amount times the price and then I get the total there. Now if I select this cell and look at the bottom left I can see that it is B2 times C2. I can even see here the colored cells showing me which ones are used in this formula. If I were to use Edit Copy or Command C to Copy and then go to the cell below it and then Paste you would think I would get exactly what I copied which is B2 times C2. But instead if I use Command V to Paste notice the formula that pastes in is B3 time C3. This is one of the few instances in all of computing where when you copy something and then you Paste it you don't get what you copied. You get something has has been altered. In this case it adjusted the row from 2 to 3. It knew to do that because I was copying the formula from cell D2 and pasting it into D3, one row down. So it automatically adjusted all of the row references in that formula down by one row.
If I were to select the cell below it and then paste in here again I don't get B2 times C2. I get B4 times C4. It sees that this is two rows down and adjusts all of the rows in the formula by two. This happens if you paste into multiple cells as well. So if I copy this cell and slide to both of these and then paste then the cell right below is B3 times C3 and two below that is B4 times C4.
Now I've made a change that you didn't see. I'm going to Copy and then Paste here again and notice this time it pasted B2 times C2 and you could see here the references to those cells. If I pasted here you could see it is still referencing those cells. It's not adjusting the rows. What change did I make? Well, if I look at this cell here, i'll double click in it to look at the formula, notice this little dollar signs before the row number. If I click on the little down arrow there next to it you could see I've got two checkboxes. Preserve Row and Preserve Column. By default these are Off. But I have turned On Preserve Row for both of these. Turning on Preserve Row is indicated by that little dollar sign there. You can see I can turn it On and Off. The dollar sign doesn't refer to currency at all. It refers to whether or not the reference to the Row or Column is Relative to where you paste the formula or Absolute meaning it is always going to point to exactly that column or row. That dollar sign symbol goes way back to the early spreadsheets in the '80s when you didn't have little checkboxes. You would actually have to type that dollar sign. In fact you still can type a dollar sign when typing out a formula in Numbers.
Sometimes you want to specify that a cell references is Absolute. So, for instance, if I were to multiply the amount times the price and then also times a special value, say in another table like this, then if I were to Copy this and Paste it I get an error. Why? Because if I look at the formula here it doesn't have a reference below this row here. There is no row 3 in this table. So when it tries to adjust that row by 1 it hits an invalid value. So what I need to do is go back to this formula here, then switch this to preserve row, and now when I Copy and Paste it moves the reference to cell in B and the cell ion C by 1, but it doesn't change this reference here. So now I get the proper result and I can paste it here as well and it is still always going to point to row 2 here even though it is adjusting the row in every other part of the formula.
Sometimes Numbers will automatically make a cell reference absolute. For instance, when you're using a Header Row or Column. So I've got a Header Column here. This column is a Header. Here you can see it is a different color. If I look on Format Table I can see I've got one Header Row and the amount is there. So if I'm going to, say, create a formula where I multiply the amount times the price notice how it automatically puts a dollar sign in front of the column. It notices this is a Header Column and it will automatically make that an absolute preserved reference to column A. It won't adjust it. The result is if I take this formula, which is A2 times B2 and paste it here I get A2 times C2. The reference to B2 is adjusted over by 1 since I pasted it one column to the right. But the reference to A was not adjusted because the column reference is preserved. It has the dollar sign in front of it which was automatically put there because I was referring to a Header Column. Not a regular column.
Here's another example of how you might use this inside of a function. So let's say I want to have a running total and I wanted to have that here in the cell on the right. Now I might do that by selecting the last cell here and then using the function SUM and the summing up all of the cells in Column B from the beginning to the current one. So, B2 to B5. Now above I want it to be the sum of B2 to B4. Notice that since it is a range I get Start and End Preserve Row and Preserve Column. A whole set of four checkboxes. So I can say for the start Preserve the row. Always start with B2. But for the end don't! Always have it adjust according to the location where I'm pasting. So the result is this is B2 to B5. But if I Copy and Paste here then I get B2 to B4. This one is B2 to B3 and this one is B2 to B2. I have a running total provided by a set of SUM functions all using one absolute reference to the beginning of the range and a relative reference to the end of the range. So now when I have a new column here and an amount you can see it properly adjusts this one so it is B2 to B6. The 2 is held constant, but the 5 from here changed to a 6 since it is down one row.
Now this isn't just how Numbers works. This is how all spreadsheets work, even going back decades. So if you're using Excel or Goggle Sheets it is going to work the same way. Sometimes you'll see this referred to Absolute and Relative cell references in Numbers. There is the whole Preserve Row, Preserve Column wording. Either way it is the same basic thing and you should be able to use the dollar sign in the cell reference as shorthand if you're just typing out the reference. So I hope you found this useful. Thanks for watching.

Comments: 9 Responses to “How Cell References Automatically Adjust in Numbers”

Alan Chambers

2 years ago

Hi Gary, I have been using numbers for iMac for a while now and I thought I was doing OK. That was before viewing your video . In the past in numbers I could copy and paste but now I am using headers and footers copy and paste will not work for selected figures in the footer. Is it not possible to do so?.

Gary Rosenzweig

Alan: Not sure what you are doing. What are you copying exactly, from where exactly, and where are you pasting it? Once you paste, what is not as you expected?

Charles Dunbar

1 year ago

Numbers preserving rows: Hi, Have three columns containing digits, a column that keep a running average (G2) and one column/cell (J2) keeping a count of the rows in column (C2).

I use this formula to calculate the running average SUM(C$2:C26)÷J$2
and this column (J2) to count the number of row (COUNTIF(C$2:C25,">0"))

the problem is this: When the next row calculates the running average - all the running averages above take on that value. What am I missing? Thanks.

So J2 doesn't related to row 2, just a count of all rows in the table?
Why not just use AVERAGE(C$2:C25) for the average?

charles dunbar

Gary, thanks for the input but the result is still the same. I'm trying to keep the previously calculated rows in the column from taking the same value. How do I protect the cells that have been completed from changing? My calculations are good.

Charles: Examine your cells carefully. In J15 for instance it should be AVERAGE(C$2:C15) and then in J15 it should be AVERAGE(C$2:C16) and so on. Copy and paste should take care of that for you as long as you are starting off right.

Gary, I'm not making myself clear. i'll try again: ex. C2 thru C6 each contains a diff val. d2 thru d6 each calculate a RUNNING average for for the cumulative val of rows to that point ex. c2 thru c4. But when ANY D row after the first (d2) is calculated all the the D rows ABOVE take on that value erasing what was already there. Another ex.: d2 has calc. value of 122. when d3 is calculated to 155, d2 will then also show 155 - and so-on. It's a poser. Many thanx for you attempts to help me.

Charles: So if d4 is the average of c2 to c4, then it should be =AVERAGE(C$2:C4) is that what it is? If not, what do you have in there? Then, copy and paste d4 into d5. Now look in d5. Is it =AVERAGE(C$2:C5)? It should be. And d4 should still be =AVERAGE(C$2:C4). Is it?

Gary, you are correct. I could not get your method to work in my sheet so finally I just rebuilt the sheet from scratch and used your method of calculation, it worked! I have no idea what was hidden in the old sheet. Thanks for your efforts in helping me.
Charles

Comments Closed.

How Cell References Automatically Adjust in Numbers

Comments: 9 Responses to “How Cell References Automatically Adjust in Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information