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.

You can also watch this video at YouTube (but with ads).

Video Transcript: On today's episode: Let's take a look at how to use multiple tables inside of iWorks Numbers.
So numbers is a great spreadsheet program, but it's a little bit different than the old-style spreadsheet programs. Instead of having one huge spreadsheet to work with, you can have multiple tiny spreadsheets inside of a single document. Let's go and take a look at what I mean. So, here I've gone ahead and created a simple Numbers spreadsheet. I've just chosen the blank template and this gives me - you can see on your left here sheet number one, table number one. So, this is a table and it's on a sheet, but you can't see that because the table covers the entire sheet. What I'm going to do is click on the upper-right hand corner of this table right here and this will give me these little pull handles and basically select this table so I can move it. I'm going to delete the table, right there and now I've got sheet one and no tables in it and I have this big blank page. How you add a table, you simply go to table button at the top and you select any table to add. I'm going to select one that's called plain and there - it puts it right there. And I could basically navigate around in this little spreadsheet. I can also click here in the upper-left portion of it and drag the spreadsheet anywhere I want. When I click outside the spreadsheet, you can see that I lose all of the different header information here and it's just a simple spreadsheet in its own. So, I can go ahead and basically leave that there and I can go ahead and create a second table there and you can see both of these are now on the left and I can select either one. And I can select one and use the upper-left hand corner and move it around. I can also use the bottom right corner to grow it or shrink it just like that, so I can have two separate tables right there. Now, let's put something in them.
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.
Now, another thing I can do is I can create charts and add them to the sheets as well. For instance, I can go ahead select the data here and select the total monthly expenses that I want to chart. Go to charts, select the chart I want. We'll go ahead and put that at the bottom. So, I've got a chart here of the two different months. Now, notice that the one thing -- it's charting from three hundred and five dollars to three hundred and two dollars - makes it look like a huge difference. I can bring in the chart palette here, which opens automatically when I create this chart. And, I can go ahead and create changes to it, so for instance, I can go ahead and select the minimum being 0 for it. I can even set the maximum to something like a thousand dollars and you can see a chart more to my liking here. You can see these months are pretty similar. I can set a name for these charts and I can go ahead and also change the size of it - make it kind of smaller here. I can move it up into the rest of my tables to make a nice looking sheet here to give me a quick summary of what I want. Now, all I need to do is go in and change something here, like say, for electric is one hundred and seventy-eight dollars. It goes ahead and updates the variable expenses that sum there; updates the total expenses there; and then, goes ahead and updates that chart and you can even see that it animates when I change the number.
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”

Adam Berkey

14 years ago

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?

Gary Rosenzweig

14 years ago

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

13 years ago

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

Gary Rosenzweig

13 years ago

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

13 years ago

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

13 years ago

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

12 years ago

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

Gary Rosenzweig

12 years ago

Go to the Help menu while running Numbers.

Mauricio

13 years ago

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!
Could you help me, please?
Thanks!

Gary Rosenzweig

13 years ago

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

12 years ago

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?

Gary Rosenzweig

12 years ago

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

12 years ago

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.

Gary Rosenzweig

12 years ago

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

12 years ago

Thanks again Gary!

Peggy

12 years ago

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

Gary Rosenzweig

12 years ago

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

12 years ago

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

12 years ago

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.

Gary Rosenzweig

12 years ago

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.

12 years ago

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.

Gary Rosenzweig

12 years ago

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.

12 years ago

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!

Comments Closed.

MacMost Now 258: Using Multiple Tables In iWork Numbers

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

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information