CODEWITHSUNDEEP
excelvba
Ole Albers
Ole Albers

Reputation: 9305

Painting a chart in Excel: conditional labels

I create a pie chart in Excel 2013 using VBA. Everything works like expected: The chart is painted and each segment of that chart has its percentage value attached to it.

Now I have the problem that I got a lot of parts that are below 1% of the data making that chart very ugly with all that "0%" parts and its labels.

Now I still want all pies (otherwise I would just have filtered the source data) but I do only want lables on segments that are at least 2% of the data.

Is that possible?

Set DataSource = CreatePivotTableCurrFy
        If Not (DataSource Is Nothing) Then
            ' Create chart object 
            Call ThisWorkbook.Worksheets("META").Shapes.AddChart(xlPie, 600, 200, 504, 360)
            Set Co = ThisWorkbook.Worksheets("META").ChartObjects(2)

            Co.chart.SetSourceData Source:=DataSource
            Co.chart.ChartTitle.Text = "Sales by Brand"
            Co.chart.SeriesCollection(1).ApplyDataLabels ShowPercentage:=True, ShowValue:=False

        End If

Upvotes: 0

Views: 147

Answers (2)

L42
L42

Reputation: 19737

You can try this not really very neat solution.

Dim d As Datalabel, Dim v As Long

For Each d In Co.chart.SeriesCollection(1).DataLabels
    'v = CLng(Mid(d.Caption, 1, Len(d.Caption) - 1))
    v = CLng(Split(d.Caption, "%")(0)) '~~> just thought this is better
    If v < 2 Then d.Delete
Next

Upvotes: 1

Noldor130884
Noldor130884

Reputation: 1004

it is possible, just a bit complicated :) I take you want to hide the portion under 2% and you need to it for the slice as well as the legend and so on. Naturally you start by selecting the slices of the pie that doesn't reach 2% (that is quite easy and it depends on how you give that % to your slices). Then you can look here for a full procedure to follow. This link shows some code to do what I explained.

Upvotes: 0

Related Questions