Understanding Sheets and Tables In Mac Numbers

Hi, this is Gary with MacMost.com. Today let's talk about Sheets and Tables in Mac 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.

So when you want to get beyond the very basics in Numbers you want to understand how Sheets and Tables work. Let's create a blank Numbers document here. What it's going to do for us is create a default sheet and in there a default table. Let's backup a bit. A Numbers file is simply called a Document. It's the same for most things on the Mac. A Pages document. A Keynote document. And you have a Numbers document. If you're used to using Microsoft Excel then a file is also called a document but it's also commonly called a Workbook. You don't really call a Numbers documents workbooks. You just call them documents. Now inside each document you have one or more sheets. Now you may never get beyond using a single sheet in a document. In this case you could see here I just have one sheet. If you only ever use one sheet you may not even notice Sheet 1 here at the top. It's okay just to leave it like that but there's a lot you can do with Sheets like for instance you can add another one. So here I've added Sheet 2 and I can switch between them. They almost seem like they are separate documents but they are actually two sheets in the same document. Microsoft Excel does the same thing and they actually call them sheets there as well although an older term for that is worksheet.

Now with Sheets you can do a lot here by clicking in the little down arrow to bring up a popup menu. Here you can rename the sheet. You can duplicate it. You can show sheet options which will bring up Format, Sheet in the Sidebar. You can do the same thing by selecting the background here. I'll set the background, deselecting the Table, and you could see under Format there's only Sheet here. There's another way to change the name. You can also just double click here to change the name for any sheet. Note you can set a background color for a sheet. So if it helps you to differentiate the sheets, maybe make it easier to move between them and remember which one you're in, you can use a color. It will help, of course, if you set it to be a very subtle color. So, for instance, something that's really close to white but maybe tinted. You can also Cut, Copy, and Paste sheets if you like and of course delete them. You can also rearrange the order that they are in by dragging them back and forth. 

Now each Sheet is like a blank sheet of paper. It always starts off with a default table. So, I've got this one in the original sheet. Any others I create give me a smaller default table. But I could easily select the table by clicking the little circle here at the top left, delete it, and there's nothing on the sheet at all. It's a blank sheet of paper. You can add lots of things to Sheets. For instance, in addition to Tables I could also add a Chart. I could add some text. It's just a text box and I can move it around and make the text larger, style it, I could add a shape. There are a variety of different shapes. You could use anything that you use in Pages or Keynote. You can also do arrows, connecting lines, all sorts of things. You can even add Media like photos. You can put videos and audio on the sheet as well. So you can do all the basics that you can do in Pages and Keynote here inside of a sheet in Numbers. 

In fact we can see some of this in use if we just use one of these existing templates. Here's a Home Improvement template. You could see here there are some images here at the top. There's some text boxes. There's a line here. Then you've got a Table and a couple of Charts. But notice in this template it uses multiple sheets. You've got a sheet for Costs, Products, a To-Do List sheet, and a Contact List sheet. Here's another template that's for just storing recipes. In this one you could see there's a picture here, there's some text, lines, and there are a few different tables. This here is just some text. Here's an example of a document that just has a single sheet in it. It doesn't need additional ones. But it does have multiple tables. So to create a new table all you need to do is click Table here, choose one of these starting points, and it will insert it in. In this case I've already added two tables here and I've moved them around putting them however you want on the sheet. This is a very simple document here. I don't want to put these on separate sheets. I've just put two tables next to each other. 

Now note that the use of Tables is one of the biggest differentiators between Numbers and Excel. In Excel you have a single table in each sheet. It goes all the way from the top left to the bottom right. It's just filled with cells inside of a single table. But in Numbers you can have multiple tables inside of a sheet. Now one of the things that's confusing then is how do you refer to cells in Table to Table. But you don't really have to think about it too much because Numbers makes it easy. Let's create an extra table here and let's say I wanted to have two numbers and I wanted to add them together. I'll type equals for formula and I could simply do A1 + B1 and I'll get the result. It takes the cell A1 and cell B1 and adds them together. Now how would I do that if it was in another table. After all if I want to refer to cell A1 here it's different than this A1. Well you could type it all out. But there's no need to because you could simply click to select a cell. So let's just do the same formula again. I'll do equals and I'll click here to select A1 + and click here to select B1. I get the same result. But what if I were to do equals click here plus and then click here. It's going to go and figure out what to put there. So it refers to that cell. In this case since the row and column are unique it's simply going to use those instead of B2 it's going to say Amount Adam. If there was another table that also had Amount and Adam then you would see it say Contributions, Amount Adam. Each table has a name just like a sheet does. So this new table here has this name here at the top. I can change this to whatever I want and that's the name or title of that table. You can actually go to Format, Table and hide the title. But that's the only way for you to actually name the table. So even though it's hidden the name of the table is still there. You can reveal it, change it to whatever you want, and then turn it Off again if you really don't want to see the name. 

But the general idea is that clicking to add a cell to a formula is the best way to make sure you're selecting the right cell. So when using multiple tables forget about typing in things like A1 and simply click on the cell that you want to use.

So for instance here we've got Trip Expenses and a Total and Contributions and a Total. Let's say we want to calculate the outstanding amount, the amount that is still owed. We can add another row here and we can call that Outstanding and then to do the calculation here I would do equals and this, the Total Expenses minus the Total Contributions. It will correctly calculate the result taking the values from two different tables. Another way to do this would be to simply create another table. There's nothing wrong with having tables that are very small. So I could create a table like this. I can shrink it down to when there is just one value. In fact I could even get rid of the header row there. Just have this one value here and I'll do equals and I'll say Expenses minus Contributions. There's the total. I'll call this table Outstanding and now I can have this as a single cell table that I move around and place wherever I want. 

Now here's an example of a document that uses multiple sheets. In this case I'm recording daily sales and I want sale's totals. Now the daily sales is going to be the table that's going to grow and grow and grow. So I probably don't want to combine that with other tables in the same sheet. So I have a sheet here just called Sales. It's got one big table in it and that table is going to keep growing as I add more sales to it. If I put another table here, once this table grows very long it's going to be hard to scroll all the way to the top here to see what information is in the smaller table. So just having one table on a sheet like Microsoft Excel does works for something like this where it is essentially a database of information. However I also have another sheet here called Daily Sales. Here I've got dates and I have the total sales for that day. So the formula here is actually going to draw on values from a different sheet. You could do that just as easily as getting values from a different table. 

Let's select this cell here and delete it so I can recreate it. I'm going to use SUMIF to get the total sales for the date here. So I'll do equals to start a formula. I'll do SUMIF and the column for the values is going to be here in the Sales table. Notice I still have a floating formula right here. It hasn't gone away. So I'll select Column B there and it has it in. Now the value is back here in the first sheet. It's this. This is the date for this row. Comma. Now the values come from here. So I'll select this one. Then I'll close the parentheses and hit Return. It's going to take me back here since I just entered a value for this cell. So this gives me the correct amount right there and I could see here in the formula it's going to the Sales Sheet, Date, that's the column and you could see here it's referring to the Sales Date but here it just says Price. Why? Well, there's only one column in my entire document called Price. So that's all it needs to show me so I know which column it's referring to. But there are two columns that are called Date. There's this one and the column called Date in the Sales Sheet in the Table there. So it shows me Sales and the two colons and Date. So now it's very clear exactly where these values are coming from. It gives me the correct amount right there drawing on one value from this table and two columns from this table.

So it's important if you're creating anything but the simplest Numbers spreadsheet to understand Sheet and Tables and also to get a lot of use out of them. You can certainly go and have one table in one sheet and put different types of data in different cells and treat things like a single sheet in an Excel workbook. But you're not taking full advantage of what Numbers has to offer unless you're trying to break things up into multiple tables across multiple sheets to have a document that makes the most sense and that you can use efficiently.