Creating Pie Charts In Numbers With an Other Slice

If you create a pie chart in Mac Numbers you will get a slice for each row, even if that row is very small. You can use a series of techniques to create a second table and set all of the smaller values to 0, moving the sum of those values to a new row. Then you can create a pie chart from that second table that shows these small values grouped together.

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

Video Transcript: So here's something I was playing around with in Numbers. Suppose you have a chart like this where you have various items with numbers next to them and you want to create a pie chart. 
Let's click on chart here. I'll create a pie chart. Since I had this selected it populates it with all the data. Notice I've got some very narrow wedges here. Typically you'd want to group these all together and then define them as Other. But there really isn't an option to do that. But I figured out a way that you could do it and kind of keep updating things and it would work.
So let's get rid of this chart here. I'm going to go back to the table and I left an extra row here for a reason. I want to switch that to a footer row and get a total. So I'm going to do equals, SUM for sum, click on the heading there and now I have a total for all sales, that's going to come in handy.
I'm going to create another table here, and this table, let's put it next to it, I'm going to do something similar. I'm going to do Items and Sales again, I don't need all those there, and I'm going to do some formulas. So I'm going to do equals and I'm just going to click here where it says apples. In other words just give me a copy of what's there. Then I'm going to do the same thing here. I could just do equals and then click there and get that number.
But instead I'm going to use an IF function. So I'm going to do, IF and I'm going to choose this cell here, is when divided by this cell at the bottom here, but I'm going to click on that cell and say Preserve Row, Preserve Column. So I'm going to take this number here and divide it by that. That'll give me a percentage. So if it's greater than 0.1, in other words 10%, then give me that value. Otherwise give me zero. That's the function there. In other words give me the value only if it's greater than 10% of the total. So I get 58 there. 
Now I'm going select both of these, Copy, and I'm going to paste them and you see for oranges it's greater than 10%, for bananas it's greater than 10%, for peaches, pears, and watermelons it's less than 10%. So instead of giving me 4, 2, and 1 it's giving me 0, 0, and 0. Now the trick here is that charts when you have a zero value won't bother to draw a wedge. So these will be missing if I were to do a chart of table 2. But I still need to figure out how to get that Other in there. 
So let's just shrink this table up here but I'm also going to preserve a row and give myself a footer there. Instead of this being total here I'm going to make this Other. How am I going to get Other? Well, I'm going to use the same kind of formula but a little different. So I'm going to use the SUMIFS function, that's IF with an S at the end. If I look it up over here in the Help, you can see it's sum values, test values, and condition. Which it what I want.
So I want it to add up the values here, so I'm going to click on B there from Table 1 sales. I'm going to test the values here, so Table 2 sales column. The test I'm going to perform is with quotes around it equals zero, quote, then close the parentheses. So basically if the value here is equal to zero, so it's only true for peaches, pears, and watermelons, then I'm going to add to the sum from here. So the 4, 2, and 1 will be added to the sum. The result is I get 7. So I now have these values here, zeros for these so they shouldn't show up in the pie chart, and 7 for Other. 
Now let's create the pie chart using this table. Select it, pie chart, I get it there and sure enough it doesn't have these three in there because they are zeros. But it doesn't have that in there either. I want to click Edit Data References. It doesn't include that because it's a footer row. I can force it to include it. Now it's including the footer row there. Now I can see Other is present. 
So now I can do different things with the chart like let's get rid of the  legend because the legend includes some extra stuff there. But I can add, under the wedges, I can say Data point names. So now I get the names there and I get a nice chart. And it updates. So, for instance, if I were to increase peaches so that it is above 10%, now you can see peaches is included. Only pears and watermelons are included with Other which are just three there. Also if I go back to the chart here I can change the data format to be the number. So I can change it to number rather than percentage. 
So some really cool stuff. I can, of course, update the formula here. These formulas, if I change it. 0.1 that's ten percent so that's my threshold there. I can include that as a number somewhere else and refer to it or I can just go and change the formula and Copy and Paste it in. That's that one threshold that you can change. So this is how you get to have a pie chart with Other in there rather than having tiny slivers of insignificant data.

Creating Pie Charts In Numbers With an Other Slice

Comments: One Response to “Creating Pie Charts In Numbers With an Other Slice”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information