PRDV007: Spreadsheets III: Presenting Data

The next chart we will demonstrate is a pie chart. A pie chart is used to show a percent of the total for a data set at a specific point in time. The data we will use to demonstrate a pie chart is related to the overall spending activity in the health-care industry. The Health Care worksheet contains data that shows total spending in the United States for the years 1969 and 2009. In 1969, the total amount spent in the United States for health-related expenses was over $66 billion. The pie chart shows how this $66 billion was funded. The following steps explain how to accomplish this:

1. Highlight the range A2:B7 on the Health Care worksheet.
2. Click the Insert tab of the Ribbon.
3. Click the Pie button in the Charts group of commands.
4. Select the "Exploded pie in 3-D" option from the drop-down list of options.
5. Click and drag the pie chart so the upper left corner is in the middle of cell E2.
6. Resize the pie chart so the left side is locked to the left side of Column E, the right side is locked to the right side of Column M, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 17 (see Figure 18 Pie Chart Moved and Resized).

   

   Figure 18 Pie Chart Moved and Resized

7. Click the chart legend once and press the DELETE key on your keyboard. A pie chart typically shows labels next to each wedge. Therefore, the legend is not needed.
8. Click the Data Labels button in the Layout tab of the Chart Tools section of the Ribbon.
9. Select More Data Label Options from the drop-down list. This opens the Format Data Labels dialog box.
10. Click the box next to the Value option under the Label Options section in the Format Data Labels dialog box. This removes the checkmark (see Figure 19 Final Settings in the Format Data Labels Dialog Box).
11. Click the Percentage option under the Label Options section in the Format Data Labels dialog box. A green check should appear in the box next to this option (see Figure 19 Final Settings in the Format Data Labels Dialog Box).
12. Click the Category Name option under the Label Options section in the Format Data Labels dialog box. A green check should appear in the box next to this option (see Figure 19 Final Settings in the Format Data Labels Dialog Box).
13. Click the Close button at the bottom of the Format Data Labels dialog box.
14. Click the Home tab of the Ribbon and then click the Bold button. This should bold the data labels on the pie chart.

    

    Figure 19 Final Settings in the Format Data Labels Dialog Box

15. Click the chart title twice.
16. Click in front of the year 1969 and type Health Care Spending by Source:.

Figure 20 Final Health Care Pie Chart shows the completed pie chart. You can quickly see that Health Insurance and Out of Pocket made up the majority of health-care spending in 1969. Similar to the column chart, the key to creating an effective pie chart is the number of categories presented on the chart. Although there are no specific limits for the number of categories you can use on a pie chart, a good rule of thumb is ten or less. As the number of categories exceeds ten, it becomes more difficult to identify key categories that make up the majority of the total. In this example, it is easy to see that two categories compose 75% of the total.

Figure 20 Final Health Care Pie Chart