How Cell References Automatically Adjust in Numbers

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.