Monday, 4 August 2014

The Pie of Pie Chart in Excel Demystified

Pie charts are used to display data points as a percentage of the whole value. Excel provides a few different variations of pie chart to choose from. One with a difference is the pie of pie chart.

The pie of pie chart is used to display the smaller values of a pie chart in a secondary pie to make them more visible.

Take the pie chart below for instance. The loyalty coupon, offline and referrals values make up such a small percentage of the pie chart that they are difficult to read and understand.

Pie chart struggling to handle minor values

Let’s see the same data set using a pie of pie chart. The primary pie displays the smaller values making up 5% of the whole value. These values are then displayed in a secondary pie giving much more attention to the detail.

The smaller data points retain their percentages as a contribution to the whole and lines are used to visualise the enhanced segment easily.

Pie of Pie chart in Excel

Creating a Pie of Pie Chart

The pie charts above were created using the data set below. Let’s look at how we can create a pie of pie chart from this data.

Table of sales data to chart

1. Select the data range you want to chart. In this example that is range A1:B9. The last three values are used for the secondary pie so you may need to sort your table first.

2. Select the Insert tab, Pie and then the Pie of Pie chart.

The chart is created. Further formatting can be applied to improve the look of the chart.

Creating a Pie of Pie chart

I’m a big fan of displaying the percentage and category name on each data point of my pie charts. Let’s do this and also remove the legend.

3. Click on the legend and press the Delete key on the keyboard to remove it.

4. Click the Layout tab on the Ribbon and then Data Labels. Select More Data Labels Options from the list.

5. Check the Category and Percentage boxes, uncheck the Value box and click Close.

6. Resize the chart if necessary to improve how the data labels fit in with the chart slices.

Finished pie of pie chart

Formatting the Secondary Pie

There are some specific formatting techniques that can be applied to the secondary pie in the chart. These include changing which data points are displayed in the secondary pie, and also changing the size in relation to the main pie.

Changing the Data Points Shown in the Secondary Pie

By default, the pie of pie chart displays the last 3 values in the secondary pie. To change which values are displayed;

1. Double click the secondary pie, or right mouse click and choose Format Data Series.

2. The Format Data Series dialog box appears. Specify the number of values to show in the Second plot contains the last 3 values option.

Changing the values shown in the secondary pie

3. Alternatively, you can change the Split Series By option to change how Excel decides which values to show. For example, the list can be changed to Percentage value and the Second plot contains all values less than changed to 15%.

Changing Size of the Secondary Pie

The size of the second pie can also be changed.

1. Double click the secondary pie, or right mouse click and choose Format Data Series.

2. Increase the percentage of the Second plot size. The percentage is related to the size of the main pie.

Watch the Video

No comments:

Post a Comment