Hi, this is Gary with MacMost.com. Let me show
you the seven fundamental skills that you need to learn to master 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, of course, there are dozens, if not hundreds, of different techniques that you
need to know to use Numbers to its fullest. But there are 7 really basic things that are at the
foundation of it all. First, is understanding how important it is to manager your tables in
Numbers correctly. Now if you used Excel in the past the whole window is just one large table.
Often Excel users fill that with all sorts of different things in different cells. For instance,
here's an example of that in Numbers. I have this one table here. But you could see part
of it has all this data. There's another set of data over here and then there's some cells
with some labels and some calculations here. That is not how to use Tables.
To use Tables you want something like this. For this table here just contains this information and
nothing else. There's another table here for other pieces of information. You can keep adding as many
tables as you want and put them on the same sheet or add more sheets if you like to organize your
data. But it's important to realize that you should only have one type of data in one table.
Now once you do that the next thing is to understand how important Rows are. A Row
represents a piece of information. A record in a database. So, for instance, for Miles and Expenses
here there's Date, Miles, Hours, and Expenses. You could see each Row represents a different date.
The fundamental piece of information could be a date. It could be a cell. It could be a person.
Whatever it is it should be one per Row. All that information for that item should be stored
in that one Row. Then that row is simply repeated in every single Row in the table itself, with the
exception of the Header Row and perhaps a Footer Row as well, which we will look at in a minute.
So once you understand that the table should have a focus and that each row should represent
one piece of information the next thing to learn is how Headers and Footers work.
Headers and Footers are separate from the regular rows. This is a regular row,
this is a regular row, and this is a regular row and all of these are. But this is a Header
Row. If I look under Format, Table you can see I've got one Header Row here and that is exactly
what I've got. This is treated differently than the other rows. Likewise you could add a Footer
Row. I'm going to add another row here at the bottom and then change this table to have one
Footer Row. So this one will be added here and it will change style depending upon your table style.
Now, instead of have a date, with miles, hours, and expenses here, I can use this for
calculations. For instance I could use it to Sum Up, say, the number of miles. To do that I would
just put a simple SUM formula in here and instead of actually specifying the cells I can just say,
Give me the sum of all of column B. So you could see SUM of B, close the parentheses, and there I
get the total. It's not going to try to add the cell here in the Header Row as that is not part
of it and it is not going to try to re-add the results here to itself. So Header and Footer
Rows are treated differently. You can say I want the sum or the average, some other calculation on
this whole column and it knows not to include those. Plus, if I go to the bottom here and I
press Return you could see how it adds another row knowing that I should push the Footer Row down.
Next, we have Formatting. So often I get asked questions about formulas and functions when the
answer is really about formatting. Notice here that the numbers here are formatted as currency.
These are just regular numbers. That's all done under Format, and then Cell, and then you can do a
Data Format. So here, you can see this is currency with two decimal places. This is automatic. With
Automatic Decimal Places I can force it, say, into a number and say that I want one decimal place,
like that. Then I can make all of these uniform by simply double clicking on the column there. It
selects all of the cells that are not Header and Footer cells, and I can set this up with
that format right there. Any new cells I add would follow that format as well.
This is particularly important when dealing with Dates and Times. You want them to be in a certain
format. Sometimes you want the time included. Sometimes you don't want the date included,
just the time. All that is done with cell formatting, not calculations. So,
for instance, here in these Date cells if I select this one and you could see at the
bottom left it always shows you the actual value that is in the cell regardless of the
formatting. So I can see this is January 6, 2023 even though the formatting here only
shows the date with the month and day. I can change that to include the year. If I select
all of the cells there in that column and then I go to Date & Time and then I go to
set it to be a certain format, like this. Then they will all now have that format.
Next we have Sorting. It is important to understand that when you sort you sort a table
and it is sorting all of the rows in that table based on the values in one or more columns. So,
for instance, in this table right here I can select it and I could go to organize and then
sort and I can have it sort the entire table. I could add a column, like let's sort by the Grade,
and you can see it's now lowest to highest. I can set it to highest to lowest. If I change something
here, like I corrected this grade here, notice how it doesn't resort until I go to Organize Sort and
then Sort Now. Then it will resort everything. You could also click here at the Headings for
each column and you could access a lot of the sort functions here without going to the sidebar.
One of the most important things about sorting is knowing that you constantly change it. So, it is
not like you want to set this up and it's always going to sort by grade and that's it. Sometimes
you may want it to sort by name. Then other times you may want it to sort by a particular column,
like that. You can just keep changing the sorting. Don't think of it as something that is fixed.
Think of it as something you're always going to use to look at the data in different ways.
Likewise, Filtering is something that is used also to look at dynamics. Something you change
all the time. So, for instance, for this table here if I go to Organize, Filter I can add a
Filter and let's say let's filter by grade and choose a rule. We don't want to just pick each
grade here in this case, but we'll do number and we'll say, is greater than 90, and then we can see
we end up with only four rows here. So there are only four students here that have a higher than
90 grade. The other rows are still there. They are just hidden by the Filter and you can switch it On
or Off using this. You can also filter here using the column Headings. So you can do a quick filter
to filter like that. So also use Filters dynamically. Remember when you're adding
or removing data from your table to turn Filters Off. Always have Filters
Off when you're changing the data and then use Filters to view your data in different ways.
Now you may have noticed I haven't even mentioned Function and Formulas at all yet. But here I am
almost done the list and I haven't even mentioned on Function as being fundamental. That's because
until you get everything else right, you know how to work with Tables. You know how to work
with Rows and Headers and Footers and to Sort and Filter, functions only come after that.
I'm only going to actually show you one thing that is fundamental about using the Functions.
That's how to find out what Function to use. So many people ask me questions and it is obvious
that they don't either know how to do this or they are just skipping this part. You've got
to master it. It is so simple. Let's say I want to create a Function right here. I want to add
up all these hours. So I press the equals key to start typing in a formula. I notice here the
Sidebar switches to Functions and I have this List of different categories and the
different functions that I could do with each category. I can Search and most importantly at
the bottom here it describes each Function as you select it. So if I want to find the SUM,
like that, I select SUM and down here is all this information about it. It describes very clearly
what it does. It shows you what the parameters would be. I gives you any notes and the things
you need to know about it. Then it gives you some concrete simple examples that you could
read and then learn how to use that Function. In addition to that it also has See Also at the
bottom so you could see similar functions if after reading this you realize it's not what you want.
When people ask how do I know so much about using spreadsheets and in particular Numbers
it is because I take the time to look here. Sometimes I'll just browse through and read
the different functions and look at the examples. You know what? If I don't completely understand
something I will build a sample table just to try out a function. You don't have to use a function
for the first time with your real data. You can create a sample document and try out a function.
Maybe try the exact example that is here in the Help text. Only once you have mastered the art
of knowing to look here in the Function's Help and read and understand what the functions do,
then you can start creating formulas to perform the calculations that you want.
So I hope you found this useful. Thanks for watching.