Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 51 Create Two Sets of Slices in One Pie Chart

Most people think pie charts are limited to a
single set of values, but here is a way to create a pie chart based
on two columns of values.

It is a bit tricky to see two series of
values charted on separate axes within one chart, but the effect is
well worth the effort. To see how this works, first create a basic
pie chart. Put some data in the range B1:C5, and then select that
range and click the Chart Wizard button in the toolbar. In Step 1 of
the Wizard, under Chart Type, select the first pie chart. Now work
your way through the Chart Wizard, making any changes you need. When
you reach Step 4, make sure you place the chart as an object in the
current worksheet.

Next, select the pie chart, right-click
it, and select Format Source Data Series. Click
Add to add another series. Select cell D1 for the Name and cells
D2:D5 for the Values, then click OK. You'll get the
chart shown in Figure 5-4.

Figure 5-4. Pie chart set up from worksheet data

Double-click the pie itself again to select it, select Format Data
Series, choose the Axis tab, and plot the series on the secondary
axis. Click OK. The pie chart still looks the same on the surface,
but it isn't the same underneath.

Select the pie, and while pressing
the left mouse button, drag out from the center, then release the
left mouse button. This will create the exploded effect you are
looking for, as shown in Figure 5-5.

Figure 5-5. Pie chart with exploded secondary axis

By exploding the pie, you will not only separate the two axes,
revealing the second pie chart, but also compress the pie chart
plotted on the secondary axis, allowing you to see both charts.

Now, select each slice of pie in turn and drag them back to the
center of the pie, producing the chart shown in Figure 5-6. Remember that two slow clicks will highlight
an individual piece of the pie.

Figure 5-6. Completed pie chart containing two pies

Join all the pieces of the pie again, and you will have a fully
functional pie chart plotting two series of data on separate axes.
Now you can color and format accordingly.

Andy Pope