Understanding Numbers Column References

Hi, this is Gary at MacMost.com. Let's take a look at using column references 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.

When you're creating formulas in Numbers you're referring to values in other cells. So, for instance, if I wanted to add the values of these two cells together I could use this blank cell here, I'm going to use the equals key to start a new formula and then I could type B2 plus B3 and add these two cells together. Then I get the result there. Now in addition to single cells you could also refer to a range of cells. For instance, if I wanted to use the SUM function I could find the SUM for all of these cells from B2 to B13 by typing B2 and then colon B13. Then I'll get the sum of all of these. 

Note that you could also use your pointer instead of typing the cell references. For instance I could have clicked here and it will insert B2 automatically into the formula, then plus and clicked here. Likewise, I could have used SUM and then clicked here and dragged to create a range. You could see it is exactly what I would have typed. The result is the same. 

Now often we want to have a range that is the entire column. So all of these numbers here. But instead of saying B2 to B13 we just want to say everything in column B. To do that all you need to do is use the letter for that column. I could do SUM and then type B and that's all I need. Then I get the same result. Likewise I could type SUM and click here and I would get B and then close the parentheses and then I get the result for everything in the column just like if I typed the letter B. But usually you wouldn't want to have the result just sitting out here in the table. You'd want to have every row represent a record of data. Maybe this is Sales Amounts or something like that. So I've got a date and an amount here. I want to put the total at the bottom. So you would think that what you would do here is start a formula in the last row. Type SUM and then either type B or click here. Close the parenthesis then I should get the total for all the numbers in column B. But instead I'll get an error! 

Now, of course, when we get an error we want to check out what it is. So I'll click on the exclamation point there and I'll see that it says, this formula can't represent its own cell or depend on another formula that references this cell. What's happening is when I say give me the sum of everything in B it includes B14, this cell. So it's asking for the sum including its own value. It can't refer to itself. That doesn't make sense. Let's take another look at what happens when we use a column reference. If I do a sum of B out here notice that it shows you what cells are selected. It's B2 to B14. It's not B1. That's because the first row is a Header row. Header rows are not included. Numbers knows that a Header row probably has information, like in this case it says Date and Amount. It is not going to have a value. So it will start with B2, not with B1. So when we actually ask for the sum of B we're asking for B2 to B14 which is no good if we want to put the result in B14 itself. But in addition to Header rows being excluded from the entire column when you're doing a SUM, so are Footer rows. As a matter of fact this is what Footer rows are really meant for. 

So under Format, Table if we look here we see Headers & Footers. We see we've got one Header column, which is the Date here. We've got one Header row which is across the top with titles for each column. We have no Footer rows. Let's change that. So we have one. We can see even the styling indicates now that this at the bottom is, in fact, a Footer row. So if we were to try to get the SUM of B, notice that the highlighted area is B2 to B13 now. It doesn't include B14 because B14 isn't a regular row, it is a Footer row. So now we can actually select B14 and if we put SUM of B in there it will work just fine because it is the sum of B2 to B13. It doesn't include the Header row. It doesn't include the Footer row. 

Even better than that, when we want to insert a new row we would do that by going to the last regular row and then when we press Return it will insert a new row here, let's put a new date in there, and let's add a value here. You could see the SUM now increases by that amount because it's still the SUM of everything in B, not including the Header row or Footer row. So this Footer is going to keep moving down as we add new rows to this table and it will always include all of the regular rows in this column. 

As a matter of fact the use of column references and Header and Footer rows is a good way to figure out if your table is well made. If it is well made then most of the things you're going to do, like getting SUMs or averages or doing lookups are going to apply to all of the regular rows in the table. You'll use Header rows to define what each column is and the Footer rows to perform calculations. You can also add additional Footer rows. I'm going to simply select the cell here in the Footer row, press Return, and you can see how it automatically changes of the number of Footer rows to 2. So I could use this one, say, instead of SUM I could do the average and then use B and it's only going to be the regular rows and not include either of the Footer rows in  determining the average. 

So I hope this helps you to build better tables and spreadsheets in Numbers. Thanks for watching.