The 7 Skills You Need To Learn To Master Numbers

In order to use Numbers to its fullest, first master these essential skills before worrying about functions.
You can also watch this video at YouTube.
Watch more videos about related subjects: Numbers (197 videos).

Video Transcript

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.

Comments: 8 Comments

    Claudio Silvaggi
    2 years ago

    thanks bro!

    Eric
    2 years ago

    Useful as ever. Have used sorting many times but never(?) used the filtering facility. Time to give it a go.

    Mark
    2 years ago

    Wow, I’ve been using Numbers forever and thought I wasn’t bad at it, yet I was missing a couple of your basics. Subscribing to MacMost has been more constructive than I can say!

    Umesh Kumar
    2 years ago

    Excellent. I do hope you do he same for Pages,

    Michael
    2 years ago

    Fantastic - I have been a life-long user of Excel because I didn't realise that numbers had capabilities that I needed. Guess I have a long period ahead of me moving Excel files to Numbers! As always thank you Gary

    Alastair
    2 years ago

    There I was, a lifelong Excel user (Lotus 123 and others before that), wondering wtf was happening and why wasn’t Numbers behaving correctly. I had a set of assumptions. Wrong! Gary shining a light in his inimitable way. Namasté.

    Dr. Saleh Al Saleh
    2 years ago

    Dear I am looking for your hellp.currently I am using MacBook Pro m2 excel app to enter space into sentence I have press Shift with space bar together before I update it to llastest only I press space bar to insert gap into sentence

    2 years ago

    Saleh: Try turning off System Settings, Accessibility, Keyboard, Full Keyboard Access. If that doesn't work, call Apple Support.

Comments are closed for this post.