MacMost Now 887: Counting Rows and Pie Charts

Creating useful spreadsheets is usually a matter of combining several techniques. In this tutorial, you'll learn how to create a table to hold a small database of information. Then, you'll create a second table to count the number of records that have certain criteria. Finally, you'll use the second table to create a pie chart. Each element updates automatically as new records are added or changed.

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

Video Transcript: Hi this is Gary with MacMost Now. On today's episode let's take a look at a variety of techniques in Numbers.
So let's create a Numbers document that has a short database in it and then count the items in the database depending upon some criteria.
For instance, let me just put some random names in and then I'm going to put in here something like say what type of user they are, maybe this is for a seminar. I'm going to add a few more names here so I've got twenty rows in my example.
Now one of the first things you want to do is that you want to make sure that you create a good table. In this case I've got all these extra blank cells. I don't want to have that. I'm going to select the table here and I'm going to shorten it. I'm going to grab the columns over here on the right and drag them in. I'm going to scroll down and grab the bottom there and drag that up. So now I've just got what I need. I have a header row here. I can put Name in the header and Class there.
Notice that since this is a database it behaves like one. If I am in this row, I've just typed intermediate, I can actually hit return and it will give me another row there. So I can continue to add names to this one table here. 
I can even name this table. I'm going to instead of Table 1 I'm going to click on it here and call it Attendees.
Now let's create a second table. The second table is going to contain a count of how many people are in each class. So I'm going to copy and paste the class types here into the header left column, Advanced, Intermediate, Beginner, and I'm going to put the Count there. Then I'm going to shrink this down to just the size I need. Nothing extra.
Now to get the data in there I'm going to use the Count If function. That will give me the number of people in each class. First parameter after the parenthesis there, the left parenthesis, I'm going to do V. You can see it grabs the entire row here and it puts it in. It has no problem with this being one table and this being another. The second thing I want is to be able to compare it to the word Advanced. So I'm going to put in quotes "=Advanced" and close the parenthesis there.
The result is that I get 6 right there. So there are six in the Advanced class. I'm going to copy and paste this in these two places here. But these are both also counting advanced so I want to go in and edit them and change it. Intermediate there is four in that class and beginner there is nine in that class. 
This updates as you change things. So if I were to change like say one intermediate person to advanced you can see it updates. Also if I were to add a new row there you can see it updates there as well.
So the next thing is let's create a pie chart. So I'm going to select this entire table here and then just go in and say give me a pie chart. I's going to automatically take what I have selected here and apply that to a pie chart. You can see how it has done that.
So now the great thing is that everything updates automatically. I can go in here and change this chart. Let me go in and name this table to be named and call this Counts and call this Pie Count. Of course it gives me the title there so I probably want to name it something better than that. But I can go ahead in here and change somebody to another class and you can see it adjusts not only this table here but also the pie chart itself. It is even animated as the change is applied.
So I have talked about each of these techniques before but putting them all together you can see how you can use Numbers to get some useful results.
Hope you found this useful. Until next time this is Gary with MacMost Now.

Comments: 6 Responses to “MacMost Now 887: Counting Rows and Pie Charts”

Joel Anderson

11 years ago

Useful. One thing I would've done differently is, in the formula that uses COUNTIF, have a reference to the adjoining cell that contains the description, rather than the hard-coded description itself.

Daniel M. Clark

This is driving me bananas... if I add a new row to a table, the chart doesn't update with the data that I enter into that new row. When I created the chart, I told it to show "Column A" (Y) and "Column B" (X), and it was as a 60-row table. If I add a 61st row, the chart still only displays up to row 60. Do I have to manually reset the chart's attributes or something?

Gary Rosenzweig

Not sure what you mean. If you add a 61st row, aren't you looking at that row when you add it?

Here's a simplified example. Say I have 60 rows, and the cells in the data column alternate 1 and 0. Row one is a "1" and row two is a "0" and row three is a "1", etc. I create a simple vertical bar chart. The bars alternate tall/short/tall/short/etc. That's good for 60 rows. Row 60 is a "0". The chart shows it with a 'short' bar. If I go to the table and add a Row 61 and put a number "1" in the proper column, the chart does not update with a 'tall' bar. (Thanks for the quick reply! Wow!)

I see what you mean. The chart is for rows 1 to 60. Adding a row 61 doesn't automatically add it to the chart. If you select the chart, you will see the area it covers shown in the table. You'll see that it only covers 1 to 60 and not 61. But there is a handle on that selection and you can drag it to cover row 61 too. Then the chart updates.

Ahhhhh gotcha. Okay, I can see that, that works. Add a row, select chart, drag handle... kind of a bummer that I'll have to manually update that chart every day in that fashion, but it's not a very complicated table/chart, so it's not the end of the world.

Thanks!

Comments Closed.

MacMost Now 887: Counting Rows and Pie Charts

Comments: 6 Responses to “MacMost Now 887: Counting Rows and Pie Charts”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information