How to limit pie-chart slices to top 5 & rest to group in others

[Guest]

In a pie chart if there are multiple slices, then the readability decreases.
Therefore I am interested in knowing if excel provides a way of restricting
the number of slices, yet accounts for the total picture.

For example if there are 25 slices created, I would like the pie chart to
display the top 5 as individual slices and the balance 20 to get clubbed
together under OTHERS.

 

[Andy Pope]

Hi,

There is no built in method for this.
You would need to sort the data and then sum the points you want included in
the Others category. And produce your pie on the six data points.

Possible but not advised. You can use the Pie of Pie to create the chart.
You need to know the cut off figure for the Others group. Or sort the data
and define the position. Once you have the pie of pie you can format the gap
width to be zero. The size of the second plot to be five. You then need to
format each slice in the second pie to have no fill or border colour. Also
set the leader lines to the same colour as the plot area.
The major drawbacks with this work around are you can not rotate the pie so
the Other slices is positioned nicely. Or get an Others category to appear
in the legend.

Cheers
Andy

 

[Guest]

Thanks Andy for your feedback. I had previously tried what you had suggested
but was hoping for a better/easier way. Perhaps MS could take it on as a
feature addition in a future release of Excel.

 

[steven.gibbs]

Use the LARGE() function to identify the top five slices then subtract
from total to get remaining portion.

 

[Su Marney]

Hi,
I was working on a pie chart from a pivot table. I have 'Customers' as row header, 'Year' and 'Period' as Column headers and 'sum of profit' as values.
My User wanted to display the top 10 customers in a pie chart and put the rest in a bucket called 'Others'.
There are 72 Customers so I went to the Customer filter and selected the bottom 62! (not dynamic, I know.).
Then with the filter applied, select the customers left and group these (right - click Group). Rename this group 'Others'. Minimise the group and you will see a clearer list of the top 10 and 'Others'. If you insert your pie chart now it will look more like it. You do end up with 2 Customer columns but you can hide the original using your Pivot Field List and un - ticking.

Hope this makes sense.