An Introduction To Using Formulas In Mac Numbers

Learn the basics of using formulas to perform calculations in the Numbers spreadsheet app. You can perform basic arithmetic, copy and paste formulas to multiple rows, and use functions to get the sum of a column.

Video Transcript: Hi, this is Gary with MacMost.com. Here's a basic introduction to using 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.
Spreadsheets are a great tool that I think everybody that has a computer should learn how to use. On a Mac you can get the Numbers Spreadsheet App for free from Apple. In Numbers here I'm going to go to File, New and choose the basic blank template here to create a new document from it. You can enter data into each one of these cells. Usually that means numbers. Lets, as an example, use two numbers here. I'm going to type a 6 in this cell and click on this cell and type a 7. I'm going to press Return for it to accept that value. So now I've got two values. One in each cell. Now let's suppose I want to add these two numbers together. I can create a formula in this cell that adds this number plus this number. The way to enter a formula is to go to Insert, Formula, and then New Formula. Now you're typing a new formula. 
But nobody does that. Instead you use the keyboard shortcut Equals. Just press the Equals key on your keyboard and you go into Formula Entry Mode. Now you can type a formula. Now notice that there are columns and rows. Columns have a letter associated with them. Rows have numbers. So when you want to identify a cell you would identify it by its column and then it's row. So this is cell B2. This is cell C2. So if you want to create a formula that adds the two of them together you need to use those references. I'm going to press Equals here and I'm going to type B2 and then +C2. You could see as I do that it even highlights the cells that I'm referring to. Now I can press Return or click the green checkmark here. You could see it gives me the total there. So I have a formula that works. 
Now I do want to point out if you were to just type a formula without using the equals key first you wouldn't get a result. In fact you wouldn't be typing the formula at all. You would be typing what Numbers thinks is a label. So if I were to type B2+C2 I'd just get that text in there. You have to go and enter the formula mode with the Equals key and type it as a formula in  this box in order for a calculation to be performed. The great thing about formulas is they update automatically if your change the values. So if I go into this cell and I press 9 to change the value to 9 and Return notice how the formula automatically recalculates and comes up with a total of 15. Also notice when I select a cell with a value in it, like B2, if I look at the bottom left hand corner it says Actual meaning actual value and shows 6. C2 shows an actual value of 7. But D2, the cell with my formula in it, will show formula and it will show B2+C2. So the cell here shows you the result of the formula. But to see what is actually in the cell you would look to the bottom left hand corner with this cell selected. You can see it's not actually the number 13 typed in there. It's a formula and the results of the formula are what is being shown.
Now I'm going to delete this by just selecting it and pressing the Delete key. I'm going to show you another way to type a formula. You can use Equals to start and instead of typing B2 you can actually click on the cell B2 and then you could type + and then click on cell C2. If you want to move this out of the way you could grab it right here and drag it wherever you want. If you like having it at the top here you can drag it there if you like. Now I'll click on C2 to complete the formula and press the check. To Edit a formula all I need to do is click to select the cell and then click again to edit it and the formula entry box will come up again. Now I can either type or click or do anything I want to edit this. So I'm going to select the + here and instead change that to minus -. Now you can see I can do subtraction as well as addition. If I want to do division I would do a slash. That's the key on your keyboard that also has the question mark on it. But it appears here as a division symbol. Now it's dividing. If I wanted to multiply you would use the asterisk. On American keyboards that's the Shift and 8. You can see that shows up here as a little multiplication symbol and now it's multiplying the two. 
Now let's turn this into more of a proper table here. Instead of just numbers sitting up by themselves let's label the columns and rows. You could see here the first row looks different than the others. It's a Header Row. The first column also looks different. That's a Header Column. These are used for labels rather than values. If you go in the sidebar to Format, Table you can see Headers and Footers and you could see I've got one Header Column and one Header Row. So let's use this to label these. I'm going to say that the first one is Name, second is Amount, and third is Price. The idea is these would be Sales so the Total would be the amount times the price. Let's put a name here and now look we've got these labeled. The formula is going to look a little different. I'll click on it once to select it and again to Edit it. You can see instead of saying B2 it actually uses the labels. So it says Amount Mark Times Price Mark. Now you could still type it the same way. So I could do Equals and then B2 + and you could see how it changes to Amount Mark, C2 and you could see it changes to Price Mark. I could also do the same thing by clicking. So I could do = click here, multiply here and you could see it's using those labels instead. 
Now let's add another row to this. So you could see here the second sale has a different amount and different price. I want to do the same calculation here. Now you don't have to type it. I could if I wanted to. Put = then this times this and get the right value. But instead what I'm going to do is Copy and Paste. I'm going to select this cell and use Command C to Copy. Then I'm going to go here and Paste. Now it seems like this shouldn't work. After all what I've copied here is B2 + C2. If I paste it here it's still going to be B2 + C2. It's going to give me the total of 42. Not the total of 40. But Numbers, and all spreadsheets, are smart enough to know that when you Copy and Paste a formula it should adjust the rows and columns by the difference of where you are copying it from and where you're pasting it to. So since I'm copying from D2 and pasting in D3 it's plus one row. So it's going to adjust all of the references in the formula by one row. So I'm going to Paste in here and you could see it's giving me 40, the proper answer. You could see here the formula is Amount James times Price James. So it did, in fact, adjust this. This is actually B3 times C3. It recognized it went down one row so it should adjust the formula by one row as well. 
So let's enter some more data. So you can see here I've added three more rows. If I want the formula to fill out and complete these three rows I can just Copy one of them and then select all these cells. I can do that by selecting one and then Shift Clicking and selecting the last one to select a range. I can also grab this dot here and stretch the selections. Now I've selected three cells. When I use Command V to Paste it will paste that formula in all three cells and adjust each one. So you could see here this is Amount John times Price John. This is Amount Susan times Price Susan and Amount Tom times Price Tom. Each will adjust properly even if you insert a row or a column. So if I go over here and click Add Row Below it's going to insert a new row between 4 and 5. But you could see that this formula and this formula remained in sync with which row it was on. Also, it knew enough to say, well you had formulas in all of these so you probably want the same one here. You could see this is indeed B5 times C5. Since we haven't entered a name here it's going to use the row and column letters and numbers. So let's enter a name here. As soon as I enter this value you could see now the calculation is able to come up with the right answer. 
Now you could do more with formulas than just basic math, like addition, subtraction, multiplication, and division. You could use what are called Functions. So, for instance, let's say I wanted to get the square root of a number. I'll use this cell here as an example. I could do Equals and if you look here on the right you should see a Functions Browser. You have all these categories of functions and a list of all the functions right here. You could scroll through them to see what is available. You can also Search. So let's say I want to do the Square Root. So let me search for root here. I can see a bunch of stuff there. Right here is square root.
Whenever you find a function always look down below and read the description. There's a description. There are examples and everything. This is the key to using any Function in Numbers. Always read through this and look at the examples. 
So let's use Square Root. I can double click here or I can just type SQRT. But if I double click here it will actually fill it out and show what it needs in terms of parameters. Values that need to process to get the result. So in this case I've got none here. I could type a number, like 9, and then it will give me the result.  But I could also replace this with a reference to a cell, like this one. Now it's going to give me the square root of whatever is here. So that's the basics of how Functions work. Now let's do something that's actually useful for this. Let's say we want to get the sum of all of the totals here. So to do that there is a Function called Sum. I'll start a formula. I'll look here and search for Sum. It will come up and then I will read carefully the description and examples. Then I'll use Sum and instead of a single value you are giving it a range. So I can click and drag to select this range. Notice how its defining the range with two cells. D2 and then a colon D7. The range from D2 to D7. Then I'll close the parentheses there. So this should give me the sum. In fact it does. Notice how all the formulas are going to update whenever I change something. So if I go here and say, well the price is going to change to this, notice how the 40 to the right changes to 48 and then the sum will update.
Now when using tables in Numbers you should try to keep each row representing the same kind of thing. In this case a record of sales. So row 2, row 3, row 4 are all records of sales. The Headers are labels so these are labels here and the column here that's a Header Column or should be an identifier as well. So in this case it's a name. Another case it may be dates or maybe receipt numbers or things like that. But by looking here it identifies what the sale is and here are all the details about it. 
Now we've got a bunch of rows here that don't follow this pattern. First we've got a bunch of blank rows here. These don't represent anything. Then we have this row here which doesn't actually represent a sale but actually shows the total. So the proper way to put together a table is not to have any rows that aren't the same kind of thing. So let's get rid of the Sum here, so this is blank, and let's get rid of all of the blank rows. I'm going to just drag the bottom up by dragging this little button right here and all the way to the top. So now all the rows do indeed represent a sale and the Header Row gives us the labels for it. Now the great thing about this is if you want to add another sale all you need to do is go to any cell in the last row and press Return. It instantly adds another row. So we can now add something here and put some data in and notice how it automatically filled in the formula there for us when we added the new row. So all we need to do is enter the raw data and now the row is complete. We can also get rid of the columns here. We don't need the extra columns. 
But how do we get the total? Well, the Header Row represents labels. But you can also have a Footer Row that represents calculations. Things like sums or  averages. So let's add an extra row here at the bottom by dragging this down one. Then in Format, Table I'm going to add a Footer Row right here. One Footer Row. Now you can see this is a little bit different. It's a slightly different style than the rest. There's a thicker line above it. Now in this cell here let's delete what was left there and instead create our Sum. Now we could drag and select that whole range and then we get the sum. But since we're using a Footer Row we can do something special. If you do SUM and then click here just to get the name of the column you could just use that. What that represents is every regular row in there. So everything but the Header and the Footer. You could also have just typed the letter D and it would do the same thing. Now we get the total. Now anything we add here will just push the Footer Row down and the total, the Sum, will take into account all of these rows. 
So those are the very basics for using formulas and Functions to do calculations in a spreadsheet. I've got many more videos on using Numbers here at MacMost.com. Also a course if you're interested in that. Hope you found this useful. Thanks for watching.

Comments: 10 Responses to “An Introduction To Using Formulas In Mac Numbers”

Ric

2 years ago

Thanks for the refresher.

Isabel Ruttle

This came at just the right time for taxes! However, I continue to get '0' as my answer when adding a column. The formula looks right, but, when I press the check mark, the answer is '0'.

I type'=' the formula shows up. I drag the cursor over the column. The cells appear. There are some empty cells in the column. thanks for your help!

Gary Rosenzweig

Isabel: Start from scratch again and follow along closely with what I am doing in the video. You'll get it.

Me, again. On a new spreadsheet I followed your directions and it worked!I Unfortunately, when I went back to the original the total was still '0'. So, I opened a new SS and copied the information to it. I still get '0'. Oh, and occasionally, when I hit =, it showed up! Now what?

Isabel: Troubleshoot. Use your deduction skills to figure out what is different. Is there something about the values you are trying to sum up? Maybe they are text instead of numbers. Maybe you have selected the wrong range or there is something included in the range that is not a number. Investigate carefully to find the problem.

Thanks, Gary Shall do!

Leo

Hey Gary... I have and issue. I need a formula to increase procuct by one for every two people in the household.
1 person gets 1 item, 2 people get 1 item, 3 people get 2 items, 4 people get 2 items, 5 people get 3 items and so on.
I do not know what this type of formula is called and need help.. please can you help me???

Leo: Sounds like you just want simple math. Divide by 2, round up.

Andy

1 year ago

Gary, is there any way to change the default location of the formula edit box after hitting the = key? It appears over the cell being edited, but also over two adjacent cells which I often need to see or select. I realize that the edit box can be moved, but it gets tedious as I frequently have to move this box.

Andy: No, it will always appear right at the cell.

Comments Closed.

An Introduction To Using Formulas In Mac Numbers

Comments: 10 Responses to “An Introduction To Using Formulas In Mac Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information