11/10/10
11:16 am

MacMost Now 475: Adding Up Amounts In iWork Numbers

The simple task of adding a column of numbers in iWork Numbers can be done elegantly using right-sized tables with footer rows. Learn how to easily add a list of amounts and perform other function like averages using iWork 09 Numbers.

Video Transcript (Click to Expand)
Hi this is Gary at MacMost Now. On today's episode let's use iWork numbers to create a table that adds up a column of numbers. So this is a pretty basic task to do with any spreadsheet but numbers does this particularly well as long as you understand how headers and footers in spreadsheet tables work. So in numbers I've created a blank template new file here and I'm sheet one table one and I want to add up a row of numbers so I just create some random numbers here and I've got this and I can add it together by simply typing a formula equals sum, with a parenthesis, and then I can select these cells, right parenthesis and return. And you can see that I've got the total here and it updates it by changing the number- you can see it updates automatically. But there's a better way to do this. I'm going to get rid of the formula and I'm going to keep this column of numbers here- what I'm going to do is I'm going to shrink this table here to fit and go to the bottom and I've got a handle here at the bottom and I can shrink it all the way up so that it fits exactly what I've got there. And I don't need any more columns so I'm going to do the same thing by going to the right and dragging this over. So technically the header here can be some sort of label and on the left here can be some other sort of label. I don't necessarily need to have that. Now all I need to do is add another row there at the bottom that will give me the sum of all those amounts. I'm going to add what's known as a footer row- to do that I've the table selected and then I select table, footer rows and I'm going to set it to one. This will add this row here at the bottom that like the header row is greyed out and is different than the data in the middle. So now to get the total, I do the same thing as I did before: equals to initiate a formula, sum, and then a parenthesis and instead of actually doing a range of cells, I'm just going to go B for the column and it knows by B I mean the amount column and it can ignore the header and the footer itself. So now the interesting thing about doing it this way about a footer row is that if I want to add more amounts here I can simply drag the table and add more space and anything I put in here will be added to the total. Now it can be even easier than this- I can use the insert table function here and insert a sums table. I'll do that here in the same sheet so I can have the two tables side by side. And you can see that in this table it just gives me ten rows here and four columns, and each one of these has a footer row formula here with the same formula I was using over there, with the basic sum of the column. I can select the right side here and actually shrink this down or increase this either way. And if I want to get rid of these columns here I can select delete column for each one. It can do more than just sums too. For example, if I want to do just the average here, I just replace the word sum with average and I get the average here at the bottom. Now notice if I shrink the number of columns here (the number of rows in the column), it doesn't affect it. It knows that the columns were empty at the bottom. So if I remove this number here you can see it adjusts the average so it doesn't take into account the empty ones. Of course I could put zeroes there if my intention really is to have a zero there in that row. You can have more footer columns as well. So I can go table and create, say, four right here. I can leave this one as the average, I can do this one as the sum, I can do this one as the maximum, the largest number there. Let's do this one as the minimum. So learning to use footer rows and condense your table to exactly the size you need to contain the numbers is key to getting the most out of using iWork Numbers. Until next time, this is Gary with MacMost Now.

Comments: 5 Responses to “MacMost Now 475: Adding Up Amounts In iWork Numbers”

    Mehdi
    11/12/10 @ 4:02 am

    Hi Gary,

    thanks for the video, i am trying move from Excel to iWork, but there are many adds-in for Excel which they do not work with iWork e.g. Isoplot which i need it for my job, is there any way to use them with iWork?

    Also another problem is, the Isoplot adds-in needs VB, and as you know, the Mac OS does not support VB any more, i need this for my job and i really do not know what to do? should i necessarily install windows on my mac (which i really hate it)? or do you have a solution for running such programs on Mac which they need VB?

    Many Thanks
    Mehdi

      11/12/10 @ 7:29 am

      If you need those specific things, you will either have to find replacements on the Mac side, or just run Windows using Parallels, VMWare or Boot Camp — and run Excel inside that. Have you tried the new Office for Mac 2011? Does that do what you want?

        Mehdi
        11/12/10 @ 10:28 am

        no, i have not tried the new office yet, but it might help, i will try it once…
        is there any way to use the Excel Adds-in in iWork?

          11/12/10 @ 11:02 am

          I’m pretty sure there is no way to use Excel add-ins in iWork. They were written for Excel and would only work there.

    Ruth Poles
    2/7/12 @ 6:38 pm

    I have successfully txfd my excel spread sheet into Numbers and was able to “add a row above” but now that option is greyed out? Also how can I add multiple rows? Also, the sum at the bottom of the amount column just stays the same?

Comments Closed.