28

I have this pie chart in Excel 2010 (based on a pivot table):

enter image description here

I'd like to combine all the values smaller than X% in a single "Other" slice. In previous versions of Excel, it was possible to use the "split series" option to do this. That option seems to be gone in the latest version. Is there still a way to do this?

Improve this question
3
  • I don't think it's gone, but it may have moved. Googling 'excel 2010 pie chart split series' returned a page from one book on Safari, also available at Amazon. Amazon's online view doesn't include the specific page you want, but the pages right before it make reference to that feature.
    – Mike Renfro
    Commented Jun 24, 2011 at 14:03
  • As a possible alternative you could Group the items in the pivot table by selecting the items, choosing "Group" and then naming the grouped item "Other."
    – Doug Glancy
    Commented Jun 28, 2011 at 0:45
  • @Doug - I wasn't able to make the grouping solution work, but I might be misunderstanding something. Could you perhaps repost your comment as an answer with worked-out steps?
    – Herb Caudill
    Commented Jun 30, 2011 at 12:03

3 Answers 3

Reset to default
12

I'm imagining a Pivot Table with a field called "Part Number."

  1. In your pivot table you'll need to sort the items in Part Number so that those with the smallest values are together.
  2. Select the items that you want to group into your "Other" category. Right-click and choose "Group..."
  3. You'll now have a new field called "Part Number2" in your field list and to the left (if it's a row field) of the Part Number field.
  4. In this new field there will be an item called "Group1". Click into the cell with that label and, in the formula bar, type "Other." That changes the name of the new item.
  5. Right-click in that same cell, the one that now says "Other," and choose "Expand/Collapse" and then "Collapse Entire Field." This hides the original Part Number field. That should do it for you. Your pivot chart should now be charting the new field too.

One thing that has helped me with transitioning from Excel 2003 is that Pivot Table Options> Display now has a "Classic PivotTable layout" choice which might make this a little easier to maneuver if you set it before you start.

A great source for Pivot Table info is Debra Dalgleish's site.

Improve this answer
2
  • wow what a nice find :-) thanks for the link to Contextures! +1
    – Florenz Kley
    Commented Aug 10, 2011 at 20:51
  • Really nice trick, thanks for sharing. Little note: You don't have to sort the values in step 1, you can also select them holding Ctrl.
    – Aljosha Koecher
    Commented Apr 8, 2022 at 11:27
7

You're looking for "Pie of Pie", which is still available in the Pie chart dropdown under the Insert tab. You cannot apply any 3D transformations to it, though.

Image

Improve this answer
1
-1

There's a couple of important steps missing in Chandoo's instructions. After you've set the fill in the pie sections on the right to be 'no fill':

  1. Right click on the lines leading from the big pie to the now invisible small pie and select 'no line'.
  2. Click on any remaining labels that are on the small pie (to select them) and press the delete button on your keyboard to get rid of them.
  3. Right click on any section in the big pie and choose 'Format Data Labels'. Under 'Label options' check the checkbox for 'Category'. This is the vital step that makes the word "Other" appear on that section.
    screen shot:
    screen shot
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .