8:30 am

Creating Tables In Numbers

When you create a basic table in Numbers, it is important to use Headers and Footers properly. Headers allow you to define the contents of rows and columns, and you will see these labels appear in formulas and charts. Footer rows allow you to perform functions like SUM and AVERAGE on an entire column, with the formula adjusting to the content in between the header and the footer.

Video Transcript (Click to Expand)
Hi this is Gary with MacMost.com. Letís look at using Tables with Headers and Footers in Numbers.

Using Numbers version 3 here and weíre going to take a look at how to create a basic table with Headers and Footers and how to use those Headers and Footers.

Weíve got a blank sheet. Weíll just choose from the template and it is going to give us a default table inside of a sheet and it already has one header row and one header column.

The first thing weíre going to do is to learn how to shrink the table to fit what we need. We donít have any data in yet so I can select the table here clicking on the dot at the upper left hand corner of a table will allow me to select the entire thing and I can use these other dots here with the two lines in them to shrink the table.

So since I donít have any data yet Iím going to shrink it down to just include one actual row for data, in addition to the column there, and then Iím going to also shrink it this way. So I only have just a limited number of rows to create a sample table.

Here is a good beginning. Iím going to create some sample column headings here. Let me do a simple inventory spreadsheet. So weíll do ďcountĒ and ďcostĒ and ďpriceĒ. Then as a sample row weíll add one product here. Letís say we have thirty of them and they cost 30 cents a piece and we sell them for 50 cents a piece. The idea is that we have no excess rows here. We just have this one.

If we hit return in any of these cells we automatically get a new row and we can type in some new data. I can keep hitting return to add more rows.

But first letís add a footer column. So I want to add one footer row. By doing so Iím going to use Format, then Table, and then add one right here but it is going to basically turn the last row into a footer row. So Iím going to drag here and add one so I have one for it to use and Iím going to add one like that. You can see it is going to draw a thicker line there at the bottom and these are my footer cells.

So hereís what I can do with them. Letís type a formula in. Iím going to do equals sum and going to click on B here which identifies as count. The reason it identifies as count is because that is its name in the header row. So you can see it is already using the header there for something. It gives me the total.

Iím going to also go in here and letís say letís do something like average. The average price. I can click on C there and it says cost and Iím going to copy and paste it in there. So Iíve got the sum. Iíve got two averages here.

So now Iíve got this in the footer row. What happens now is when I want to add a new row for a new item here all is need to do is hit return, like before, and you can see it inserts it there and moves the footer down. So it recognizes that this area in the middle is the area for the data and the header and the footer are different. So the footer gets pushed down and I can add something in here. You can see how it automatically updates this.

So remember this is the sum of this column, count, and it knows that it is not supposed to include the header or the footer row. So it is not adding itself to itself here. It is just adding between the header and the footer. The same thing with the average. It is only adding the stuff between the header and the footer.

So having formulas in footer rows and then using the column names that allows you to just basically perform operations on everything in the column without worrying about overstepping its bounds.

Furthermore, it is going to use the labels here in the header column and the header row for making formulas clearer. So if I wanted to say add this you can see that instead of saying B3 it is saying count orange plus count pear times cost pear. So using these makes it really easy to figure out what your formulas are actually doing and then using the footer there makes it easy to put together formulas that perform operations on the entire column.

Then in addition to that it knows that the headers here shouldnít move with the rest of this text. So if I actually scroll up the window you can see how the header stays there and the same thing if I scroll to the left the header column stays there. So it makes it very easy to fill in data and move throughout the spreadsheet without having to set some sort of predefined, you know this stays and this doesnít stay kind of thing.

In addition youíve got a lot of formatting options under Table here. I can change the table style. You can see its got a style that takes into account these header columns and header rows and footer rows. So there is a lot you can do to make it very nice and easy like that if youíve got these predefined and your table put together correctly rather that just say putting the formula in. If I had not defined row five as a footer and I tried to do some of these formulas it wouldnít quite work because it would be taking into account these cells here.

So that is how you put together a proper table in Numbers.

Comments: 8 Responses to “Creating Tables In Numbers”

    11/8/13 @ 4:49 pm

    good stuff Gary, looking forward to more Numbers examples, I’ve used Excel a very long time, and am pretty proficient at it, but am liking the features Numbers has more and more since I’ve moved onto a Mac

    11/9/13 @ 6:08 am

    Excellent video. Very clear and simple and easy to follow. Your videos are a perfect source for learning about all of Apple’s products.

    11/14/13 @ 9:49 am

    Great video. Used Excel for years and was having a very hard time with Numbers. You have solved many of our Numbers problems and made us happy that we switched. All of your videos confirm that our moving to Apple was the right move. Thanks

    11/20/13 @ 12:56 pm

    When I add a one-line footer and type =sum and click on the row title a warning shows up in the box that says: “This formula can’t reference its own cell, or depend on another formula that references this cell.” I keep trying it but it doesn’t give me anything different. Help? (Please and thank you.)

      11/20/13 @ 1:50 pm

      That error comes up if the cell is not really a footer cell. You have to add the extra row, and then also change the footer setting to include that row. I do that in the video.

        11/20/13 @ 1:56 pm

        Thanks for the quick response, Gary, but I added the footer just as you showed in the video and it has the thicker line to confirm it. I’ve watched the video again and again to see what else you’ve done, but I can’t see anything else you did to “change the footer setting to include that row.” Thank you.

          11/20/13 @ 2:01 pm

          I just tried it lots of different ways and it always works for me. Are you sure you are entering the formula right? I mistyped once and got your error. I missed the parenthesis key. Are you forgetting that? You type = then SUM then ( and then click the column top (or just type it) and then ).

            11/20/13 @ 2:20 pm

            That makes sense! Fantastic!

Comments Closed.