1/26/17
9:01 am

Checklist Pie Charts in Numbers

Learn how to create a Numbers spreadsheet with a checklist and pie chart that tracks your progress through the checklist. To do this, you'll need the functions COUNTIF and COUNTA.

Video Transcript (Click to Expand)
Here's a fun project you can do in Numbers. You can create a checklist and then a pie chart and then have your pie chart automatically track your progress as you check items off in the checklist.

The first thing I want to do is I want to get rid of the default table there. I'm want to create a new one that's a checklist type. You can see it gives you that. I'm going to get rid of everything except the one column here. So let's just give it a title and I'm going to put some placeholder items here like, for instance, let's just do one, two, three, etc. So let's say these are things you need to do and you can check these off very easily by using the checkboxes there. Let's just give this a heading there.

Now we want to add the chart which is going to be a simple pie chart. That's going to be here on the right. Now in order to get this pie chart to work like we want it should show basically zero percent when nothing is checked and 100% when everything is checked. So we need to have those numbers represented somewhere. I'm going to create another table.

Just a totally blank table here and I'm going to shrink it down so we just have these two columns here with just one row each. In this column we're going to put the item that will represent how many of these are checked off. So we want to use a formula for that. I'm going to hit the equal sign and in order to do that I'm going to use the count if formula. Count if and I'm going to do it on this column here and what I'm looking for is for them to be true. So now I see I get a zero and if I check any of these off it will count the number that are true.

So now I want to further change this because I don't want it to be actually the number, I want it to be the percentage. So what I want to do is I want to divide this number by the total number of check boxes there are. So at the end of the formula here I'm going to hit the slash key, it will give me a divided by, and what I'm going to do to count the number of items here is I'm going to use the Count A which will count the number of non blank cells in A. So now when I check them off you can see it's going to add up to 100.

The reason I wanted to do non blank cells is because this first one up here, you could see is blank because there are actually ten rows but the first one is the header row. So by counting only the non blank ones I'm getting a total of nine rather than ten. If I used another function like rows I would actually get ten which wouldn't give me an accurate count.

So now I have a good percentage here. If I wanted to I could change this here to go to cell and change it to percentage so I can see that maybe even turn off the number of decimals. I can see it now clearly what percent of these are checked.

Now I want to figure out what percentage are not checked. That's pretty easy to do because all I need to do here is basically say one, I'm going to do a formula equals one minus this cell and this gives me the opposite. So I get 22%, 78%. 33%, 67%. That's great. So that's what I need now for this pie chart to show me the percent complete and percent not complete.

So I'm going to select the pie chart, hit Edit cell references, select these two here, and I'm going to deselect this one by clicking on it and hitting the delete key. So now I just have these two here. 22% and 78%. Now I can further customize this pie chart if I want. So I can actually go into like the widget here. I can double click and you can see I selected just the green widget. I turn off values so you don't see that value there. I can go ahead and change different things about like say the style. So if I wanted to change it to No Fill, for instance, I can remove it. Or I can change it to a basic color fill and maybe just make it gray.

Now I'm all set. When I check these boxes you can see the pie chart grows. I can further customize this. I can get rid of the legend at the top. I can arrange this different ways that I want. I can change it's style. All sorts of different things. I can also get rid of this if I want. An easy way to do it is just to move it behind here. So I'm going to put it right over on top of this and I will go to Arrange and I'm going to bring to front so the pie chart is actually going to cover that one.

So now as I go through my checklist I can see what percentage I've done. It's great for teams. If you have a computer that's tracking everybody's progress to get a task done you can, as you check off items, see how close you're getting to 100%.

Comments: 2 Responses to “Checklist Pie Charts in Numbers”

    Eric
    1/31/17 @ 8:35 am

    I found this to be very useful. Thank you Gary.

    Is there a method that could be applied whereby the colour of the pie segment could change according to the percentage value. For example, red if = 66% ?

    1/31/17 @ 9:28 am

    Eric: Nice idea, but I don’t think it is possible, sorry. I suppose if you wanted to put a lot of work into it, you could create another table or set of cells to get it done. For instance, if A1 was false and B1 was true, then A2 could map to A1, B2 could use an IF function to contain B1 if less than 66% and 0 otherwise, and C2 could use an IF function to contain 0 if less then 66% and B1 otherwise. Then pie chart A2, B2, C2 instead of A1 and B1. The color for C2 would be red.

Leave a New Comment Related to "Checklist Pie Charts in Numbers"

:
:
:

0/500 (500 character limit -- please state your comment succinctly and do not try to get around this limit by posting two comments)