Pie Charts in Microsoft Excel

Pie Charts

Pie charts are a very common chart type. They appear in newspapers and magazines, on television, all over the internet, and in many business publications and presentations. They are easy to make and easy to understand. I'll start with a simple How-To lesson, then discuss advantages and disadvantages of pie charts, and finally show some advanced pie charting topics, including VBA techniques.

Creating a Pie Chart

My favorite pie chart was made by Rob Cockerham, to show the percentages of peanuts and other nuts in a can of mixed nuts.

Photo provided with permission from Rob Cockerham. See Rob's description of his charting activities at http://www.cockeyed.com/inside/nuts/nuts.html.

I'll use Rob's data to demonstrate how easy it is to make a pie chart in Excel. Rob has provided category labels (the nut names), values (the relative weights), and percentages. Rob's data is in rows, but data in columns works just as well. We only need one of the quantitative rows for the chart: let's use the weights.

Select the first two rows of data, and run the chart wizard. Select a regular pie chart, and click Next through the rest of the wizard. The result is the chart below, not as enticing as Rob's, but it does just as well at displaying the data.

Note: Be careful if you copy the data from this table into Excel. In my workbook, I used a custom number format for the weights (0.0"g") so the cells contained numerical data despite the symbol for grams. Delete the "g" from the cells before plotting, so Excel doesn't try to plot all zeros.

You can try various things to spice up the chart. I will only say this once: Don't abuse your viewers by using a 3D chart type. The inclination and distortion that produce the 3D effects make it difficult to interpret the data. In the view below, the Hazelnuts (4%) and the Cashews (8%) look the same. Unless your intention is to mislead your readership, you should avoid using such distortions.

Pie Charts - What are they good for?

Pie charts are good when you are showing the relative proportion of numbers that add up to a total. They are good for a single series of data. They are best when there are not too many wedges, particularly too many small ones. They show a good qualitative view of the data.

Pie charts are not good for quantitative displays, because it's hard to judge the relative angle of the wedges at different orientations. They are poor at showing progress toward a goal, especially if you've exceeded that goal, because the entire pie circle by definition is 100%. For this you should use a bar chart with a horizontal line at the goal.

Pie charts are a poor choice for comparing two sets of proportions, i.e., multiple series of data. A donut chart shows the relative proportions of the constituents of the two series, but I think it also emphasizes the outer series at the expense of the inner one. The better option is a stacked column chart.

Compare the mixed nut package above with an imaginary competitor's:

Side by side pie charts are a poor means of comparing sets of data.

A donut chart shows relative proportions.

A stacked column chart shows that the Brand X has a larger total.

Formatting

Formatting a pie chart is straightforward, with one exception. When you format a pie slice, remember that it is one point of the series. You must click on the slice to select the series, then click on it again to select the slice. If the entire series is selected when you do your formatting, every slice will receive the formatting, and you will lose the differentiation between slices.

A noteworthy format for a pie chart is the Angle of First Slice. In our example, the first slice, peanuts, starts at 0 degrees, or straight up. You can enter any angle between 0 and 359 degrees. The numbers increase as the starting point moves clockwise around the pie chart (see below).

Changing the orientation is helpful if you want to change the orientation of the pie without rearranging all your data. You might want to locate the peanuts semicircle at the bottom instead of side of the chart. Or you might be making a fancy combo chart, like my Speedometer chart (http://peltiertech.com/Excel/Charts/Speedometer.html), which uses a starting angle of 270°.

Moving and Sizing

By default, Excel leaves a fairly wide blank area around plot area of the pie chart within the chart area. The chart area is the entire editable region within the chart object, which is what Microsoft calls the embedded chart. The plot area is the square region that contains the pie. When it is selected, the plot area is outlined by a thick gray line, as shown below. Select the plot area by clicking in a corner of the square, outside of the pie.

You can move the pie within the chart area by selecting the plot area and dragging it around. You can resize the chart within the chart area by selecting the plot area, and dragging one of the black handles on its corners.

Moving Slices

You can easily "explode" a pie chart by dragging a pie slice away from the center of the pie. If you drag after one click on the pie chart series (when all slices are selected), all slices move outward by the same amount. After moving them, Excel shrinks them so they still fit within the original plot area. The farther you drag them from the center of the pie, the farther apart they will be, and the smaller they will get. You can drag a pie slice of an exploded pie chart toward the center to decrease or eliminate the spaces between slices.

To remove an individual wedge from the pie, select the wedge before dragging (two clicks). This doesn't shrink any of the pie, and the wedge can be dragged beyond the plot area to the edge of the chart area. You can explode a chart, then further remove a selected wedge.

To remove a wedge completely, format that wedge so it has no border and no fill. To remove the label from the legend, click once to select the legend, then click again on the text of the legend entry, then press the Delete key.

Data Labels

Excel offers many possibilities for data labels. You can add data labels in step 3 of the Chart Wizard, or from Chart Options on the Chart menu and the popup menu when you right click on the chart. You can also add data labels from the Format Series dialog if the entire series is selected, and from the Format Point dialogs if a single point is selected.

Excel 2000 Data Point Label dialog In Excel 2000 and earlier, you had the built-in options to show the value, the percent, the category label (the type of nut in our example), or the label and percent. You had to take special steps to get more detailed labels.

Excel XP Data Point Label dialog Excel XP introduced more flexibility in data labels. You can select any combination of Series Name, Category, Value, and Percentage, and you can specify a separator to insert between your choices. You can also customize your labels beyond this.

When we add Label and Percentage data labels to our mixed nut chart, it looks like this:

There is a small discrepancy in the percentages shown. If you compute the percentages, and round them according to standard practices to the nearest percent, the total is 99%. The labels in the chart add to 100%, which Excel accomplishes by rounding the 51.1% for Peanuts up to 52%. While summing the percentages to exactly 100% is a nice feature for all the employees in sales and management positions, to the rest of us it's a little bit dishonest. Personally, I'd rather see the honestly rounded percentages, even if they sum to 99% or 101%. The remedy in this case is to increase the number of digits by clicking on the Increase Decimal button on the Formatting commandbar.

Custom Data Labels

One way to customize data labels is to select the label (two single clicks) and change its text. To make the labels dynamic, you can link them to the contents of a worksheet cell. Select the label, but don't put the cursor in the label itself, then press the equals key, and select the cell with the mouse; the formula bar will show the cell address: =Sheet1!$A$1. Use a formula in the cell with the label so the label text changes with the data. Suppose you want your data label to show the category label followed by the value in parentheses. If the data in the original table at the top of this article is in A1:F3, select the range A4:F4 with A4 as the active cell, enter the following formula in cell A4, and press Ctrl+Enter to enter it into the selected range:

    =A1&" ("&A2&")"

This is fine for a small number of labels, but it soon becomes tedious if you have lots of charts to customize. The process can be automated using VBA, and there are even free utilities available on the Internet that you can download for this purpose. Both of the following allow you to specify a range of cells to use for data labels for a chart series:

• Rob Bovey's Chart Labeler, http://appspro.com
  Also copies the cell's font formats to the label
   
• John Walkenbach's Chart Tools, http://j-walk.com
  Has additional tools to export and resize charts

Data Label Positions

There are a number of options for the position of the labels. To choose the label position, double click on the label or labels, click on the Alignment tab, and select one of the options in the Label Position dropdown box. The position used in the chart above is Outside End, which puts each label outside the corresponding wedge. Inside End locates the label just inside the circular edge of each slice. Center puts the label in the center of a wedge. Best Fit seems like it should arrange the labels so they don't overlap each other, but the algorithm isn't as advanced as it could be. You can also select a single label (two single clicks) and drag it around manually.

The Outside End or Best Fit positions are best if you need a lot if information in each label. You can make more room for labels by shrinking the chart's plot area, as described earlier. The Inside End and Center positions are good for short labels in a chart with a small number of slices. Maximize the plot area to get more room for the labels.

Hiding Zeros

People have trouble hiding data labels for wedges with zero values. The wedge isn't visible, but the label hangs in there. If the label is showing a simple value or percentage, you can use a custom number format that hides the zero. Double click the labels, click on the Number tab, then select Custom from the list, and use 0;;; for whole numbers, 0.0;;; for decimal numbers (with as many zeros after the decimal point as required), or 0%;;; or 0.0%;;; for percentages without and with decimal digits. A number format has four entries separated by semicolons: These are for positive numbers, negative numbers, zero values, and text. Since there is no entry for zero values, the data label will be present but it will contain no visible text.

If the label contains more than just a numerical value, a custom number format will hide the zero, but the associated text will still appear. There are a number of ways to hide this text. One way is to use an autofilter on the chart's data range, and hide the rows with zero values. By default, charts do not show hidden data, so the wedge and its label do not appear in the chart. If the data changes, you have to update the autofilter.

Another option is to create a custom label in a cell as described above. Modify the formula to something like this:

    =if(A2>0,A1&" ("&A2&")","")

You can use a VBA procedure to hide your data labels. One drawback of this is that the labels have to be reapplied and the macro rerun whenever the data changes. Below is one simple macro that deletes any labels in the active chart if the associated value is zero. To add a macro to your workbook, Press Alt+F11 to open the VB Editor, then Alt+I and M to select Module from the Insert menu. Copy the code below and paste it into the code module. If the module says Option Explicit at the top when it is opened, delete the duplicate line. To run the macro, select the chart, press Alt+F8, select the macro CleanUpActiveChartLabels, and click Run.

Option Explicit

Sub CleanUpActiveChartLabels()
    Dim iPts As Integer
    Dim nPts As Integer
    Dim aVals As Variant
    Dim srs As Series
    If ActiveChart Is Nothing Then
        MsgBox "Select a chart and try again.", vbExclamation, _
            "No Chart Selected"
    Else
        For Each srs In ActiveChart.SeriesCollection
            With srs
                If .HasDataLabels Then
                    nPts = .Points.Count
                    aVals = .Values
                    For iPts = 1 To nPts
                        If aVals(iPts) = 0 Then
                            .Points(iPts).HasDataLabel = False
                        End If
                    Next
                End If
            End With
        Next
    End If
End Sub

Pie of Pie and Bar of Pie Charts

Excel's Pie of Pie and Bar of Pie chart types allow you to construct a pie chart, then break down one of the wedges in a second pie chart or a stacked bar chart. Suppose you had the following list of office supplies in A1:B5, and wanted to group the different writing supplies in the main chart and split them in a second chart.

Create a pie chart as usual, and select the Pie of Pie or Bar of Pie chart type. By default, Excel only groups the last two of the five items into "Other" in the main chart and shows only these two items in the second chart. Double click on one of the pies, and click on the Options tab. There are several ways to split the series. In this case, split by Position, and indicate that you want the last 3 values to be split. You can split all values less than a value or percentage you indicate. You can also choose the Custom option, which allows you to drag wedges from one pie to the other. If you want to drag the wedges around, you don't even need to waste time with the dialog.

On the Options tab, you can adjust the relative size of the second plot, as well as the gap between the two plots. The plot area is the rectangle that includes both plots.

You can add labels to the chart using the same techniques described above. The wedge that sums the split values in the main chart is called "Other" by default. If you use one of the third party chart labeling utilities, you can enter a custom label for this point. Enter the desired name, "Writing" in cell A6 in this example, and the formula =SUM(B3:B5 in cell B6. Select C1:C6, with C1 as the active cell, enter the following formula into C1, and press Ctrl+Enter to enter the formula into the selected range. CHAR(13) is a carriage return.

=A1&CHAR(13)&TEXT(B1/SUM($B$1:$B$5),"0%")

The finished Pie of Pie and Bar of Pie charts with labels are shown below. I've deleted the legend, and used the Center position for the labels. I've also changed from black to white text in a few wedges.

The Pie of Pie and Bar of Pie chart types only allow one auxiliary chart, and you can only locate it to the right of the main chart. To get around these limitations, you should construct multiple regular pie or stacked bar charts and position them as required to get the desired effect.

Programming with Pie Charts

Here are a handful of helpful VBA routines which I've written to help with various pie chart issues. Many of the techniques can be applied to other chart types, particularly the conditional formatting macros.

Create a Pie Chart

Making pie charts requires only very simple macros. The following creates an embedded pie chart in the active sheet. The numbers in parentheses after ChartObjects.Add are the position and dimensions of the chart object in points

Sub MakeAPie()
     Dim chtOb As ChartObject
     Set chtOb = ActiveSheet.ChartObjects.Add(100, 100, 250, 175)
     With chtOb.Chart
         .ChartType = xlPie
         .SeriesCollection.NewSeries
         .SeriesCollection(1).XValues = ActiveSheet.Range("B9:B11")
         .SeriesCollection(1).Values = ActiveSheet.Range("C9:C11")
         .SeriesCollection(1).Name = ActiveSheet.Range("C8")
     End With
End Sub

With this standard recipe in hand, you could easily set up a loop that would create a system of charts. The first could plot column C as above, the second column D, etc.

Same Size Pies

A common issue is that pie charts come in many sizes. Sometimes Excel squeezes them to allow room for labels, and sometimes you've dragged and distorted them. The following is a family of procedures which all use the main procedure FixPies to resize pie charts. FixActivePie only changes the active chart, while FixAllPies changes all pie charts on the active sheet. Change the constants at the top of the module to suit your idea of a good sized chart.

Option Explicit

' Dimensions of all pie chart objects
Const ChtAreaWidth As Integer = 300
Const ChtAreaHeight As Integer = 250

Sub FixActivePie()
     If Not ActiveChart Is Nothing And ActiveChart.ChartType = xlPie Then
         FixPies ActiveChart
     Else
         MsgBox "Select a pie chart and try again."
     End If
End Sub

Sub FixAllPies()
     Dim pie As ChartObject
     For Each pie In ActiveSheet.ChartObjects
         If pie.Chart.ChartType = xlPie Then
             FixPies pie.Chart
         End If
     Next
End Sub

Sub FixPies(cht As Chart)
     Dim i As Integer
     With cht
         .Parent.Width = ChtAreaWidth
         .Parent.Height = ChtAreaHeight
         For i = 1 To 4 ' Sometimes you have to repeat it, 4 is probably overkill
             .PlotArea.Top = 0
             .PlotArea.Width = .ChartArea.Height
             .PlotArea.Left = (.ChartArea.Width - .PlotArea.Width) / 2
             .Legend.Position = xlLegendPositionRight
         Next
     End With
End Sub

The next routine copies the dimensions of the active chart and applies them to all charts on the active sheet. There's no checking here for chart type, so it will beat up on any other charts you have. Use the test in the routines above.

Option Explicit

Sub SameSizePies()
    ' Apply ActiveChart's size to all charts on the page
    Dim oCht As ChartObject
    Dim ChtOHt As Double, ChtOWd As Double
    Dim PltAHt As Double, PltAWd As Double
    Dim PltALf As Double, PltATp As Double
    With ActiveChart
         ChtOHt = .Parent.Height
         ChtOWd = .Parent.Width
         PltAHt = .PlotArea.Height
         PltAWd = .PlotArea.Width
         PltALf = .PlotArea.Left
         PltATp = .PlotArea.Top
    End With
    For Each oCht In ActiveSheet.ChartObjects
         With oCht
             .Height = ChtOHt
             .Width = ChtOWd
             With .Chart
                 .PlotArea.Height = PltAHt / 3
                 .PlotArea.Width = PltAWd / 3
                 .PlotArea.Left = PltALf
                 .PlotArea.Top = PltATp
                 .PlotArea.Height = PltAHt
                 .PlotArea.Width = PltAWd
             End With
         End With
    Next
End Sub

Color Wedges by Percentage (Conditional Formatting)

Suppose you wanted to color the pie slices according to value, and you don't know which slice will be what value. Say you wanted to identify <20%, 20-40%, and >40%. Put the cutoff levels in the sheet (B11:B13 in this example), and color the cells accordingly. The following will make <20% red, 20-40% yellow, and >40% green:

The following macro looks up each pie slice's percentage in the table, and applies the color of the matching cell to the slice. This way is easier than hard coding color indexes and cutoff values: to change the scheme, simply adjust the numbers and colors in the range above.

Sub ColorByPercent()
    Dim iPtCt As Integer
    Dim iPtIx As Integer
    Dim iCell As Integer
    Dim dTotal As Double
    Dim rColor As Range
    Dim vVals As Variant
    
    dTotal = 0
    Set rColor = ActiveSheet.Range("B11:B13")
    If ActiveChart Is Nothing Then
        MsgBox "Select a chart and try again.", vbExclamation
    Else
        With ActiveChart.SeriesCollection(1)
            iPtCt = .Points.Count
            vVals = .Values
            For iPtIx = 1 To iPtCt
                dTotal = dTotal + vVals(iPtIx)
            Next
            For iPtIx = 1 To iPtCt
                iCell = WorksheetFunction.Match(vVals(iPtIx) _
                    / dTotal, rColor, 1)
                .Points(iPtIx).Interior.ColorIndex = _
                    rColor.Resize(1, 1).Offset(iCell - 1, 0) _
                    .Interior.ColorIndex
            Next
        End With
    End If
End Sub

Color by Category Label (Conditional Formatting)

Suppose you made charts comparing different companies, and you always wanted to use the same color for each company. The following macro loops through the active chart, finds the name using Select Case, and applies the appropriate color. The labels and colors are hard coded into this macro, unlike the one above, but it wouldn't be hard to build a similar lookup table for this situation.

Sub ColorWedges()
    Dim iPoint As Long, nPoint As Long
    With ActiveChart.SeriesCollection(1)
         For iPoint = 1 To .Points.Count
             Select Case WorksheetFunction.Index(.XValues, iPoint)
                 Case "AAAA"
                     .Points(iPoint).Interior.ColorIndex = 6 ' Yellow
                 Case "BBBB"
                     .Points(iPoint).Interior.ColorIndex = 5 ' Blue
                 Case "CCCC"
                     .Points(iPoint).Interior.ColorIndex = 3 ' Red
                 Case "DDDD"
                     .Points(iPoint).Interior.ColorIndex = 13 ' Purple
                 Case "EEEE"
                     .Points(iPoint).Interior.ColorIndex = 46 ' Orange
                 Case "FFFF"
                     .Points(iPoint).Interior.ColorIndex = 4 ' Green
                 Case "GGGG"
                     .Points(iPoint).Interior.ColorIndex = 8 ' Cyan
             End Select
         Next
    End With
End Sub

Pie Chart Links

There are not nearly as many interesting links about pie charts as there are about other types of charts. I would be remiss, however, if I did not mention Excel MVP Andy Pope's web site. Andy has gotten a lot of mileage out of pie and donut charts, and he shows many creative tricks here: http://www.andypope.info/charts/pies.htm