Choosing a Chart

Read this section, which details each major chart type and reviews how to create them. Review the examples to see when it is appropriate to use each type of chart.

Percent of Total Trend: Stacked Column Chart

The last chart type we will demonstrate is the stacked column chart. We use a stacked column chart to show how a percent of total changes over time. For example, the data on the Health Care worksheet shows spending by source for 1969 and 2009. A stacked column chart can show whether there is any change in the percent of the total for each source between the two years. The Y-axis of the chart shows the percentage from 0% to 100%. The X-axis shows the two years: 1969 and 2009. The following steps explain how to create this chart:

  1. Highlight the range A2:C7 on the Health Care worksheet.
  2. Click the Insert tab of the Ribbon.
  3. Click the Column button in the Charts group of commands. Select the 100% Stacked Column format option from the drop-down list (see Figure 21 Selecting the 100% Stacked Column Format).

    Selecting the 100% Stacked Column Format

    Figure 21 Selecting the 100% Stacked Column Format

    Figure 22 Initial Construction of the 100% Stacked Column Chart shows the column chart that is created after selecting the 100% Stacked Column format option. As mentioned, the goal of this chart is to show the percentages on the Y-axis and the years 1969 and 2009 on the X-axis. However, notice that Excel places the spend sources on the X-axis. The remaining steps explain how to correct this problem and complete the chart:

    Initial Construction of the 100% Stacked Column Chart

    Figure 22 Initial Construction of the 100% Stacked Column Chart

  4. Click the Switch Row/Column button in the Design tab on the Chart Tools section of the Ribbon. This reverses the legend and current X-axis categories (see Figure 22 Initial Construction of the 100% Stacked Column Chart).
  5. Click and drag the chart so the upper left corner is in the middle of cell E19.
  6. Resize the 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 N, the top is locked to the top of Row 19, and the bottom is locked to the bottom of Row 37.
  7. Click the legend one time and press the DELETE key on your keyboard.
  8. Click the Layout tab on the Chart Tools section of the Ribbon.
  9. Click the Data Table button in the Labels group of commands and select the Show Data Table with Legend Keys option from the drop-down menu. This is another way of displaying a legend for a column chart along with the numerical values that make up each component.
  10. Click the Chart Title button in the Layout tab of the Chart Tools section of the Ribbon.
  11. Select the Above Chart option for the drop-down menu.
  12. Click the chart title two times. Delete the generic chart title name and type Change in Health Care Spend Source.

Figure 23 Final 100% Stacked Column Chart shows the final stacked column chart. Notice that the Out of Pocket category, or the amount of cash people paid for health-care expenses, decreased significantly from 1969 to 2009. However, the Health Insurance category increased significantly from 1969 to 2009. Overall, the chart shows that the total out-of-pocket and health insurance expense increased significantly from 1969 to 2009. These two categories made up approximately 75% of total health-care spending in 1969. By 2009, these two categories increased to over 80% of total health-care spending.

Final 100% Stacked Column Chart

Figure 23 Final 100% Stacked Column Chart

Skill Refresher: Inserting a Stacked Column Chart

  1. Highlight a range of cells that contain data that will be used to create the chart.
  2. Click the Insert tab of the Ribbon.
  3. Click the Column button in the Charts group.
  4. Select the Stacked Column format option from the Column Chart drop-down menu to show the values of each category on the Y-axis. Select the 100% Stacked Column option to show the percent of total for each category on the Y-axis.

Key Takeaways

  • Identifying the message you wish to convey to an audience is a critical first step in creating an Excel chart.
  • Both a column chart and a line chart can be used to present a trend over a period of time. However, a line chart is preferred over a column chart when presenting data over long periods of time.
  • The number of bars on a column chart should be limited to approximately twenty bars or less.
  • For column, line, and bar charts, the X-axis can be used only for labels, not for numeric values.
  • When creating a chart to compare trends, the values for each data series must be within a reasonable range. If there is a wide variance between the values in the two data series (two times or more), the percent change should be calculated with respect to the first data point for each series.
  • When working with frequency distributions, the use of a column chart or a bar chart is a matter of preference. However, a column chart is preferred when working with a trend over a period of time.
  • A pie chart is used to present the percent of total for a data set.
  • A stacked column chart is used to show how a percent total changes over time.