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.