group all categories under 5% to "other" in piechart or pivot

primprim

New Member
Joined
Jul 11, 2010
Messages
10
Hi again and thanks for all the help so far!
On to my next problem.... I am creating a piechart out of a pivot table. I am using a makro to make the pivot table choose all used cells and the pie chart to source the pivot table.

I want the chart to show eg 3 categories and then group the rest in one group called "others".

Eg. lets say i have a pivot table showing total amount sold by a store. ie;
Store A $100000
Store B $80000
Store C $85000
Store D $2000
Store E $7000
Store F $500

Then I want the pie chart to show:
Store A $100000
Store B $80000
Store C $85000
OTHER $9500

Basically, I want the pivottable or chart to automatically sort all categories that are eg under 5% of total to be grouped into other. Is there a way to do this? either by using a makro or manually applying the grouping... or is there a way to apply the grouping directly to the pivot table?

thanks in advance!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

primprim

New Member
Joined
Jul 11, 2010
Messages
10
managed to solve it... so if anyone else has the same problem, here is what i wrote:

Code:
Dim PT As PivotTable
Dim rngBig As Range, rngCell As Range
Dim other As Range
Dim ColumnTotal As Range


Set PT = ActiveSheet.PivotTables("PivotTable1")
Set rngCell = Nothing
Set rngBig = PT.PivotFields("group").DataRange 'trade date

With PT.RowRange
        Set ColumnTotal = .Rows(.Rows.Count).Offset(0, 1)
End With

On Error Resume Next
For Each rngCell In rngBig.Cells
    If rngCell.Offset(0, 1).Value / ColumnTotal < 0.2 Then
        If other Is Nothing Then
            Set other = rngCell
        Else
            Set other = Application.Union(other, rngCell)
        End If
    End If
Next rngCell
other.Group
PT.PivotFields("Group2").PivotItems("Group1").Caption = "other"
PT.PivotFields("Group2").PivotItems("other").ShowDetail = False
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top