6/26/09
5:22 am

# MacMost Now 258: Using Multiple Tables In iWork Numbers

iWork 09's spreadsheet program Numbers isn't like the old-fashioned spreadsheet programs you may be used to. You can create small tables and design a sheet with each table having its own numbers and formulas and also using numbers from other tables.

Video Transcript (Click to Expand)
On today's episode: Let's take a look at how to use multiple tables inside of iWorks Numbers.
So, now I've gone ahead and populated these two tables with different data. And in this first one I'm tracking my variable expenses and I've got a header there. I'm going to select and and do command b and make it bold. I've divided it into May and June. Here's May electric, gas, and water bill and the prices and then June's as well. Now, I want to get the total for each month, so I'm going to click here and then put an equals - s-u-m, for sum and then I'm going to go ahead and select these three cells right here and I get the total for those -same thing here - equals, s-u-m, and I'm going to select these three and then I get the total there. And you can see when I go over each one of these, like this one here - I can get the formula, but it will highlight the three that are part of the sum - like that. So, what I basically done is basically figure out what my total monthly expenses are for these two months are. Now, for fixed expenses, I'm also going to do the same thing. I'm going to put here at the bottom and I've got a total for my fixed expenses. I'm going to make that bold right there. I'm also going to make these total bold so I can see them.
So, I'm going to go ahead and I'm going to move this table up here. And I'm going to move this table here, so kind of get them out of the way and make some room here at the bottom. I'm going to add a new table and this one is going to be a combination of these, so total monthly expenses. And I'm going to list the months and what I'm going to do here is put a formula where I'm going to actually select for May the variable expenses plus the fixed expenses. I'm going to do the same thing here - June's variable expenses plus the fixed expenses and I get the totals there. Now, if I click on here I can see that this and this are both highlighted because they're part of this formula, so it's easy to track what each one of these things is doing. Now, I can go ahead and shorten up these tables a little bit and leave some room for future months. And I can go ahead and shorten this up - these are fixed expenses, so they shouldn't change at all - like that. This one may expand in the future, so I'm going to leave more room there. So, now I can go ahead and arrange these a little better.
So, now that I've got these arranged here, I can do all sorts of different things to complete the worksheet. I can go ahead, for instance,add a text box and I can do some texting there. And I can go ahead and move that around here. Increase the font size and I can bold like that. I can also add different shapes, for instance, if I want to add some arrows - add some arrows like this and show that the variable expenses go into the total monthly expenses. You can see that it kind of locks somewhat to the edges here. I can add another one here and show that the fixed expenses also go up to the total monthly expenses. I can arrange those any way I want.
So, you can see that Numbers goes way beyond the old-fashion style spreadsheet. You can actually creates some nice looking documents that make it easy to track different types of numbers in a more visual way. Now, go ahead and take a look at all of the different templates that come with numbers. There's all sorts of different ideas of what you can do and how to use it. Til next time, this is Gary Rosenzweig with MacMost Now.

### Comments: 23 Responses to “MacMost Now 258: Using Multiple Tables In iWork Numbers”

12/31/10 @ 1:54 am

I was hoping you’d show how I could move from one cell in the first table, to a assigned cell of the next table, by using the TAB key only.

Is that possible?

12/31/10 @ 8:37 am

Not sure what you mean. Pressing Tab will move your focus to the next cell to the right. But you want it to move to a cell in another table? How would it even know which cell to jump to?

John Ireland
9/14/11 @ 7:02 am

Nice presentation but what is the advantage of separate little tables as opposed to having all of the info on one spreadsheet?

9/14/11 @ 7:30 am

Organization. Nicer printouts. Nicer display. And the use of header and footer rows to perform calculations (See 475: http://macmost.com/adding-up-amounts-in-iwork-numbers.html)

eric
1/24/12 @ 1:36 pm

Two reasons:
1. Looks nicer (makes data more layout-centered): you can move multiple linked tables around a page without being bound to a glorified electronic ledger sheet with columns of set size vertically, or rows horizontally.
2. Saves space (sizes tables of data to the data within it and allow you to do other things with the available space (other tables, charts, graphics, and other layout items).

Lindy
11/3/11 @ 2:23 am

Over 2 years later people are still benefiting from your explanations. You make it so much easier to understand… and now when I go to the manual I’ll know what they are talking about. ;-)
thanks so much, Lindy

Peggy
11/12/12 @ 8:00 am

How to I get the manual? Do I need to download it?

11/12/12 @ 8:29 am

Go to the Help menu while running Numbers.

Mauricio
6/5/12 @ 4:44 pm

I can’t make a formula with numbers of cells from different tables! Why???
When I click on the cell of a different table, it stops writing the formula!
Thanks!

6/5/12 @ 5:53 pm

Not sure what you could be doing wrong. Click on the cell, type = to start the formula, click on a cell in another table and it should insert that cell’s reference.

George C
7/1/12 @ 11:45 am

Is it possible to link one table to another, so that the data flows through them? Similar to linked text boxes in InDesign. I have an exercise table that I’d like to break up into months in separate tables, but keep the running totals “linked”. If not, what’s the best way to achieve this?

7/1/12 @ 12:51 pm

You would do that in InDesign because you are creating physical sheets of paper. No need for that here. Just keep adding more rows to the table — make it as long as you need.

George C
7/1/12 @ 8:04 pm

Thanks Gary. But what I am trying to avoid is having an extremely long table, I’d rather be able to break it down by month but have all the data linked. I’m new to spreadsheets so I’m not even sure if what I am asking is possible.

I don’t need to print these out, the InDesign reference was just a way to explain what I want to do within Numbers.

7/2/12 @ 12:35 am

No, I don’t think you can do that. Why not try the long table? With scrolling it shouldn’t make a difference to you — you would either have to scroll down the table or navigate to another table, but one long table would be less work. Either way, there’s no way to flow one table to another.

George C
7/2/12 @ 10:58 pm

Thanks again Gary!

Peggy
11/12/12 @ 8:07 am

This may be about the same question. We do a fish fry weekly. I want there to be several sheets, the first sheet being the running totals, i.e. income, expense # of dinners served, etc. Then I want a sheet for each month, with the totals, and I want those totals to go on the first sheet, for a running total for the year. On each sheet, I have figured out how to for example, take the total amt. of \$ made, subtract it from the food bill & help and put that amount in the profit cell. I want all of that to go on my first sheet automatically that I have broken down by month

11/12/12 @ 8:32 am

Sounds like you know what you want. So why not do it that way? Numbers is a very versatile tool. You could probably do what you want in all sorts of different ways. Your way might not be the same as my way, but it doesn’t make either one wrong. You can also evolve your model over time as you learn more about using Numbers.

Peggy
11/13/12 @ 7:38 am

I guess what I am asking is this. I have started my project. My first sheet is Jan-Dec., and I want all of the monthly totals to automatically go to that page. So, I have started that sheet, I just made the spreadsheet & have rows for Jan thru Dec., then a total. Then going down I have everything broken down. How much of each dinner we sold, how much profit, subtract that from food bill, etc., then it gives me a total profit. How to I get all of that to go on my first sheet automatically?

Peggy
11/13/12 @ 7:40 am

that sounds so confusing, ha. Sheet number one I want to be a running total for all of the sheets following. I will have the running total sheet, then I will have 12 sheets after that, one for each month of the year. I want each of those sheet totals to go on to sheet number one.

11/13/12 @ 7:43 am

You’ll need to create a formula that is just a link to the cell on each column. So on sheet 1 you will have a cell that has =X where X is created by clicking in the cell on sheet 2. Then the next cell will be =X where X is created by clicking on the cell on sheet 3. And so on.

Tim M.
10/22/12 @ 1:59 pm

I have a spreadsheet with ±20 Sheets. It is a catalog of sorts. If I were to make “Sheet 1” the Master, is there a way to tie the Master Sheet in with the 20 remaining sheets, so that if I delete, resize, or relocate something on The Master Sheet, the balance of sheets also reflect the change…therby eliminating rework of 20 sheets separately?

Every sheet is laid out the same, and contain 4-6 tables each.

10/22/12 @ 2:04 pm

There’s no way to do that. Your use of the word “catalog” tells me that you probably should be using a database for this, not a spreadsheet. Or, at least just combine your 20 sheets into 1, if the data represents the same kinds of things.

Tim M.
10/22/12 @ 6:14 pm

Thanks for the quick reply. Started out as a small project and now the 20 pages..! Filemaker will be next, but my deadline will not give me the time to “learn” the program. Stumbled upon your website for the first time today….looks like a keeper. Thanks again!