MacMost Now 812: Absolute Cell References In Numbers

Hi this is Gary with MacMost Now. On today's episode let's talk about absolute cell references in Numbers.

So absolute cell references are critical for making formulas in Numbers. What is an absolute cell reference? Well, say you want to have a formula in C1 and it's going to be A1 + B1. Well if you copied and pasted that formula one row down you would have A2 + B2. So the actual number, the cell references, in the formula are going to shift with wherever you paste that formula into. If you paste it one row down, all the references are going to move one row down.

So what if you don't want to do that. What if you have a constant like say sales tax or some special number that you are using in your calculations and you want that to be a constant applied to all the different rows. You want to have an absolute cell reference. So a reference to a cell that doesn't change where you paste it. Let me show you.

So here we have some numbers, two columns of numbers, and we want to create a simple formula to add them together. Type = sign to start a formula, click here for B4 + and then C4, hit return and I get the sum of these two. I'm going to copy that and then I'm going to select this area and paste it in. Now you can see this first formula here is B4 + C4. The second one is B5 + C5 and so on. So its moved all of the references relative to the movement of the formula itself. So every time I paste it in a different row it will move everything relative to it.

Now what if we would have a third element. We wanted to add these two numbers plus we wanted to have a number that was constant. Let's put a constant up here, a constant of 100. Let's say we want to create this formula where we say 5 + and the value of C4 right there so B4 + C4 as before except we are going to add also the value of B2. Now we get the correct answer there but if I were to copy and paste is it in here I'm not going to get the correct answer because what it is going to do is its going to move it down. So this is going to be B5 + C5 + B3 as a matter of fact it is going to mess up because it is going to start taking these values here. 

So what I want to do here is I want to change the reference. Instead of B2 by itself I'm going to click on it there and you can see I get the ability to change it to an absolute row and column. I will do that and you can see it doesn't change the formula there. It is still going to be B2 but it is going to be absolute B2. So when I copy and paste it into each of these down here you can see the B2 doesn't change. This one is B8 + C8 + B2 because it is the absolute B2.

The addition there is a very simple example but you can imagine if you were doing something like multiplying by sales tax you don't want to have the sales tax in every single row going down. You want to have it in one place. Then is uses that value there as an absolute cell reference and applies it everywhere. Then if the sales tax would ever change you would change it in one spot and all of the rows, all of the formulas, change to reflect it. 

Now I encourage everybody to use Numbers correctly which means I would never do it this way. What I would do is that I would create two separate tables so here is the table here and I would add a new table here that would have just that one value in it and would have any other constant values I've got. Maybe some titles and things like that. So let's do that. In here I will put the 100 and then let me make that a table that is one value like that. Then I would change this. Instead of having that value there I would add from this table and I would change it right there to be absolute row and column just to be sure and then I would copy and paste everywhere in there and then I would get rid of these rows here so you have a proper table here where I could put headings and ID numbers for the different transactions. In this way I put that in a separate spot and I can now create a nicer laid out table here with the constant over here and just the row and column data here.

Notice too I didn't have to set a cell reference to be absolute row and column. I could have set it to be absolute row or absolute column only. So this has different more sophisticated uses but can come in handy for your project. 

I hope you found this useful. Until next time this is Gary with MacMost Now.