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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,074
Messages
5,835,232
Members
430,349
Latest member
Gregori

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