Pie of Pie Charts and other Pie Chart Tricks

Introduction

Pie charts let you show the proportions of the constituents that comprise a larger quantity. Suppose your company has sales on three continents. You can use a pie chart to show the breakdown of sales by region. Using a pie of pie chart, you can show the breakdown of sales by product within one of those regions.

Let's step through the process of making a pie of pie chart, then we'll discuss some alternative charts, including a way to divide all segments of a pie chart into smaller pies.

Pie of Pie Charts

Sample Data

The first thing we need to do is condition the data, so it's suitable for our pie of pie chart. A typical table of data is shown below, with columns for the regions and rows for the products. The data is for an imaginary engineered materials producer, with products in chemical, laminate, and pharmaceutical markets.

Pie of Pie Data

The data above is a good input for various stacked or clustered column, bar, or line charts, but the pie of pie chart needs all of its data in one column or row for the numbers, and an additional column or row if there are attribute labels for the numbers. In this example, we will break out the product sales figures for the Americas region. The proper arrangement for our data is a column with the sales totals for the other regions (the blue numbers) and with the product sales values for the region of interest (the green numbers). The table below shows the data properly prepared for our chart.

Building a Pie of Pie Chart

To make a pie of pie chart, select the range with the numbers and labels, then start the chart wizard. You can do this by clicking the button on the Standard command bar which resembles a small column chart, or by choosing Chart... from the Insert menu.

In step 1 of the chart wizard, choose the Pie chart type in the left column, and the pie of pie subtype in the top left of the grouping on the right.

Pie of Pie Chart in the Chart Wizard

Press finish to quit the chart wizard and let Excel draw the chart. The main chart shows its values plus an extra wedge which represents the sum of the values in the second chart.

First Pie of Pie Chart

Now we need to fix up the chart. Choose Chart Options from the Chart menu. On the Data Labels tab, choose the Category Name option in the Label Contains box (Excel XP or later) or choose the Show Label option (Excel 2000 or earlier). On the Legend tab, uncheck the Show Legend box. Using both legends and data labels is redundant, and data labels are more effective because they are right on or adjacent to the data, rather than herded way over to the side of the chart. Press OK when finished with data labels and legend.

Finally, double click the chart series (the pie itself). On the Options tab (shown below), you need to make a few adjustments. By default, Excel splits the series by position, which means the higher items in the list go into the main chart, and lower items go in the side chart. This is fine, because it's how we conditioned our data. However, Excel by default keeps more wedges in the main chart than it moves into the smaller chart.

Pie of Pie Format Options Dialog

Change the setting so the second plot contains the last three values. Make the second chart 60% of the size of the first, and change the gap width to 50%. The chart now looks like this:

Pie of Pie Chart, in progress

A little more formatting leads to the following finished chart. I have manually changed the "Other" label to read "Americas", I made the data labels bold and centered them within the points, and I used white text for the regional labels and black for the product labels to set them apart.

Formatted Pie of Pie Chart

The Bar of Pie chart type is similar to the pie of pie chart type, except the secondary chart is a small stacked bar rather than a small pie.

Bar of Pie Chart

Alternative Charts

There are a number of ways to show all the data broken out both by region and by product. Excel's column charts offer several variations, as shown below. Each of these variations is also available in Bar chart style, which orients the data into horizontal bars rather than vertical columns, with the category labels up the left hand vertical axis.

Clustered Column charts seem to emphasize the values of each individual item within a cluster. Stacked Column charts emphasize the differences in total value of each stack, and individual items are readily compared, but percentages are difficult to judge. 100% Stacked Column charts emphasize the percentages within a stack, but remove the distinction in values between stacks. Your choice of column chart subtype will depend on which aspect of your data you wish to emphasize.

You may be tempted to use a Doughnut Chart (shown below), because of the way it can compare percentages in an angular fashion like a pie chart does for a single series. A donut chart conveys no explicit information about the relative volume of each series, but there is an implicit (and in this case false) relationship based on the relative areas of the rings. In this doughnut chart, it appears that Asia must have the highest total and the Americas the lowest, because the outer ring has so much more area. In fact, the true relationship is the reverse of what will likely be inferred by the viewer.

Donut Chart Clustered by Region

Advanced Pies of Pie Charts

What you really want, though, is a multiple pie of pie chart, that breaks out each wedge of the main pie chart into a satellite chart showing its constituents. For many data sets and audiences, this may be preferable to the column chart options shown above.

Microsoft Excel does not have the built in capability to show more than one auxiliary pie chart in a pie of pie chart. However, you can readily work around this limitation by creating a main chart and the secondary charts all as separate charts, then manually arrange the smaller charts around the main chart.

Let's use the same data as before. I've added two rows to the table, one showing each regions percentage of the whole, the other computing the width of a satellite pie chart as a fraction of the width of the main chart, so that the satellite chart has the same area as its fraction of the main chart. (There's no mystery: This number is the square root of the percentage in the row above; I used Excel's SQRT worksheet function.)

Central Pie with Smaller Satellite Pies

Pie By Region

Now make three pie charts, representing the breakdown of each region's totals by product. The easiest way to do this is to make one chart, format it so it's perfect, then make two copies and change the data in the copied charts. To resize the charts to represent the relative sizes of each region's totals, one by one select each chart by holding the SHIFT key and clicking on it. From the Format menu choose Object, and click on the Size tab. Check the Lock Aspect Ratio box, then enter a percentage in one of the Height or Width Scale boxes.

Now simply arrange the three small pies around the larger main pie, and your multiple pie of pie chart is complete. Of course, you could embellish it with arrows to be sure your audience realizes how the smaller pies relate to the larger pie. For my chart, I used the Freeform autoshape tool to draw the shapes that stretch from each main pie section to its associated smaller pie. After sending the shapes behind the charts, I filled them with light colors matching the main pie's darker colors. It may be better not to use a color fill with the shapes.

Pie by Region with Regional Pies by Product

Central Pie with Outlying Segments

Another way to separate the many wedges of a pie chart into groupings of wedges is to drag the wedges away from the center of the pie. Rearrange the data according to the flat table structure below. In this arrangement, all of the fields needed to describe the data are arranged in columns, and each value has its own row, or record. In fact, this table is the proper format for input data to a Pivot Table, which can produce a table with the structure used to generate all of our column charts. For more about Pivot Tables, read Debra Dalgleish's Intro to Pivot Tables and Pivot Charts on my web site.

Select the data and make a regular pie chart. Depending on which columns you select, you can produce different data labels in the chart. If you select the Product and Value columns, you get the pie chart at left, which uses the single column Product for its data labels. If you select all three columns, you get the pie chart at right, which uses two columns, Product and Region, for its data labels. This is a handy trick when building compound data labels (and axis labels too), which Microsoft has not clearly documented.

For simplicity, the rest of this exercise will use the one category pie chart. To get an exploded pie chart, select the chart, and drag any of the pie wedges away from the center of the pie. Excel redraws the pie with its wedges at the same position, but somewhat smaller to create a gap between adjacent wedges. The further you drag the wedge, the wider you make the gap between wedges. To unexplode a pie, drag a wedge back toward the center.

Exploded Pie

That's nice, as far as it goes. But we wanted to keep clusters of wedges together with each other, separate from other groups. You can move the wedges individually, rather than all the same. Select the pie, then click on a wedge, so that it is the only chart element selected. Drag it away from the center, and it is the only slice removed. Repeat with the adjacent slice(s), as shown in this chart for the three Americas slices.

Partially Exploded Pie

Well, this won't do it, either. You can only move slices radially, toward or away from the center of the pie. You can't slide them toward or away from neighboring slices to create a composite wedge. As with the multiple pie of pie chart above, we'll have to step out of the one-chart fits all paradigm, and make our desired chart from three separate charts.

Start with the original unexploded pie chart, and make three copies (move the original aside, in case any of the other charts comes to ruin). In each chart, format the slices which are not the focus of the chart so that they are invisible. Select the chart, then click on the wedge so that it is the only chart element selected. On the Format menu, click on Selected Data Point.... On the Patterns tab, select None for Border and None for Area, and on the Data Label tab, uncheck all of the choices, and press enter. Repeat for the other non-focus wedges, by selecting each in turn and pressing the F4 key. Now select the plot area, and make sure it is formatted so its Border and Area are hidden; do the same for the chart area.

Repeat this formatting for the other two charts, then arrange them so their centers are slightly separated, with gaps between each cluster of slices.

Pie By Product By Region, Clustered by Region

Another nice chart, which would not have been possible by adhering strictly to Excel's built in selection of chart types. When making charts, remember that you are in control, and Excel will do what you want. You just need to know how to ask.

Jon Peltier is president of Peltier Technical Services, which provides custom Excel solutions to help clients in engineering, finance, marketing, and other fields analyze, visualize, and understand information more quickly and more thoroughly.

Peltier Technical Services uses the power of Excel's VBA programming environment to integrate Excel's worksheet functionality and charting capabilities with the presentation and reporting abilities of PowerPoint, Word, and other applications. The company web site also houses one of the most extensive collections of information about Excel charting, including tutorials, tips, special techniques, and sample VBA code for generating customized charts and charting features.