Defining Specific Categories to Be in the Secondary Plot in Excel 2007:-


You can choose to have complete control over which slices of a pie appear in a secondary chart. If you are trying to decide among three specific products to discontinue, for example, you can move all three of those products to the secondary pie.

To do this with a macro, you first set SplitType to xlSplitByCustomSplit. You can then use the SecondaryPlot property on individual data points. A value of 0 shows the data point in the left pie. A value of 1 sends the data point to the secondary pie.

Because you aren’t sure how many items Excel will send to the secondary pie by default, you can write a macro such as the following to first loop through all data points and reset them to the primary pie, and then move three specific slices to the secondary pie:

Sub CustomPieofPie()

Dim cht As Chart

Dim chtg As ChartGroup

Dim ser As Series

Dim poi As Point

Set cht = ActiveChart

Set chtg = cht.ChartGroups(1)

Set ser = cht.SeriesCollection(1)

chtg.SplitType = xlSplitByCustomSplit

‘Move all slices to first plot

For Each poi In ser.Points

poi.SecondaryPlot = 0

Next poi

‘Move points 1, 6, 10 to secondary plot

ser.Points(2).SecondaryPlot = 1

ser.Points(6).SecondaryPlot = 1

ser.Points(10).SecondaryPlot = 1

End Sub

The results are shown in the Figure (specified below). This figure shows a bar of pie chart with three specific wedges moved to the secondary plot.



(Instead of focusing on the smallest slices, this bar of pie chart focuses on three particular products.)