Bar chart reduce number of labels by creating Others

Question

pretty new in excel and I'm currently working on a pivot table and want to create a bar chart.

I have around 15 row labels and 2 columns- row labels and percentages that looks something like this:

apples     0.1%
apricots   0.28%
guava      1.7%
.
.
.
.

oranges    40.25%

When I create a bar chart, it looks too much having too many on the y axis, so I want to create a label "Others" that is basically the sum of specific rows of the percentages in this case when the value is too small.

So I want it to look like

others        7.00%
kiwis        12.50%
pineapples   15.25%
grapes       25.00%
oranges      40.25%

Answer 1

Suppose you have a pivot table like this:

Put your cursor in the values column (column E in the picture above).

Then right click, Sort, Sort smallest to largest. Like this:

Now in the column that says "Row Labels", select the fruits you want to group as others. Then right-click and select 'Group'. Like this:

It will create a new level in the Row Labels column. You can see that it has grouped the selected fruits.

Type over the new group name with the name you want to use.

In my example, I typed 'Others' in cell D2:

Now right-click the original level of fruit name (the ones that aren't bold in the image above) and click on 'Remove "fruit"' (or whatever your column name is). This will leave you with this:

Now use Insert>Pivot Chart

If you want to change the order of the columns, you can optionally sort the items in the pivot table (either manually or A-Z or Z-A).