Show All

Hide All

Pie charts are excellent for displaying data points (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same color constitute a data series.) as a percentage of the whole. However, when several data points each amount to less than 5 percent of the pie, it becomes hard to distinguish the slices. For example, a pie chart of the following sales data contains three slices that fall below 5%.

To make smaller slices more visible in a pie chart, Excel provides the Pie of Pie and Bar of Pie chart sub-types. Each of these chart sub-types separates the smaller slices from the main pie chart and displays them in an additional pie or stacked bar chart, as shown in the next picture.

Notice that the percentage labels in the secondary pie chart display the same numbers as in the regular pie chart. The numbers only represent the individual slices that were moved to the secondary chart; they do not total 100%.

Setting up your data and creating the chart

Both of these pie chart sub-types are based on the same data that you would use to create a regular pie chart. Pie charts can display only one data series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only one data series.), which means you can include only one column or row of values in your selection when you create a pie chart. You can also include a row or column of categories, as long as it is the first column or row in the selection. Categories will then be presented in the legend of the pie chart.

When you select the Pie of Pie or Bar of Pie chart sub-type, Excel moves the last third of the data points into the secondary chart by default. For example, if there are nine data points, the last three points in your selection are plotted in the secondary chart. However, after the initial chart is created, you can change the number of data points in the secondary chart — and the way in which Excel splits the data points between the charts — by changing settings on the Options tab of the Format Data Series dialog box. (To find out how, see Change Pie of Pie or Bar of Pie data series options, later in this article.)

Create a Pie of Pie or Bar of Pie chart

1. Select the data that you want to display in the Pie of Pie or Bar of Pie chart.
2. On the Insert menu, click Chart.
3. In the Chart type box, click Pie.
4. Under Chart sub-type, click Pie of Pie or Bar of Pie.

   For a quick preview of the chart you are creating, click Press and Hold to View Sample.

5. Click Next, and continue with Steps 2 through 4 of the Chart Wizard.

   For help on any of the steps, click the question mark (?) on the Chart Wizard title bar.

 Note   Depending on how many decimal places are specified for percentages on the Number tab of the Format Cells dialog box (Format menu, Cells command), percentages that are displayed in data labels (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a datasheet cell.) may be rounded so that they don't add up correctly.

Customizing the way data points are displayed

You can format specific chart elements of a Pie of Pie or Bar of Pie chart the same way you would in a regular pie chart. However, there are a few things that are unique to Pie of Pie and Bar of Pie charts:

• Position of primary and secondary charts   The primary and secondary charts are always positioned horizontally and adjacent to each other. They cannot be moved separately. The left chart is always the primary chart, but it's not always the larger of the two charts — the secondary chart can be larger than the primary chart.
• Selection of primary and secondary charts   You cannot select the individual charts or format them separately. Both primary and secondary charts are part of one data series. You can only select the entire data series or its individual data points, and then apply formatting to the selection.
• Connector lines   Connector lines are automatically added to emphasize the association between the two charts. You can format them by applying different line styles. You can also remove them if you need to.

Change Pie of Pie or Bar of Pie data series options

1. In the chart, select the entire data series.

    Note   To select the entire data series, make sure nothing else is selected, and then click either of the charts once.

2. On the Format menu, click Selected Data Series.
3. Click the Options tab.
4. To specify what kind of data you want in the secondary chart, click an option in the Split series by box.
5. To adjust the number of data points that appear in the secondary chart, do one of the following:
   • If you chose to split the series by position, change the number in the Second plot contains the last [ ] values box.
   • If you chose to split the series by value or percent value, change the number in the Second plot contains all values less than box.
   • If you want to customize the split of the data, close the dialog box, and then use the mouse to drag pieces between the two charts.

      Note   The slice in the primary chart that represents the data in the secondary chart cannot be moved into the other chart. If you drag a slice from a pie chart without placing it in the other chart, space is created between the slices.

6. To change the size of the secondary chart, increase or decrease the number in the Size of second plot box. (This number represents size as a percentage of the primary chart size.)
7. To remove the connector lines, clear the Series lines check box.
8. To use the same color for all data points, clear the Vary colors by slice check box.

   Tip

   You can change the color of individual data points. Select the piece that you want to change, and then, on the Format menu, click Selected Data Point. On the Patterns tab, under Area, click the color you want to use.
9. To change the space between the charts, increase or decrease the number in the Gap width box. (This number represents gap width as a percentage of the secondary chart width.)