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.

Frequency Comparison: Column Chart 2

We will create a second column chart to show a comparison between two frequency distributions. Column C on the Grade Distribution worksheet contains data showing the number of students who received grades within each category for the entire college. We will use a column chart to compare the grade distribution for the class (Column B) with the overall grade distribution for the college (Column C). However, since the number of students in the class is significantly different from the total number of students in the college, we must calculate percentages in order to make an effective comparison. The following steps explain how to calculate the percentages:

  1. Highlight the range B9:C9 on the Grade Distribution worksheet.
  2. Click the AutoSum button in the Editing group of commands on the Home tab of the Ribbon. This automatically adds SUM functions that sum the values in the range B4:B8 and C4:C8.
  3. Activate cell E4 on the Grade Distribution worksheet.
  4. Enter a formula that divides the value in cell B4 by the total in cell B9. Add an absolute reference to cell B9 in the formula =B4/$B$9.
  5. Copy the formula in cell E4 and paste it into the range E5:E8 using the Paste Formulas command.
  6. Activate cell F4 on the Grade Distribution worksheet.
  7. Enter a formula that divides the value in cell C4 by the total in cell C9. Add an absolute reference to cell C9 in the formula =C4/$C$9.
  8. Copy the formula in cell F4 and paste it into the range F5:F8 using the Paste Formulas command.

Completed Grade Distribution Percentages

Figure 13 Completed Grade Distribution Percentages

Figure 13 Completed Grade Distribution Percentages shows the completed percentages added to the Grade Distribution worksheet. The column chart uses the grade categories in the range A4:A8 on the X-axis and the percentages in the range E4:F8 on the Y-axis. Similar to the trend comparison line chart, this chart uses data that is not in a contiguous range. The following steps explain a second method for creating charts with data that is not in a contiguous range:

  1. Activate cell H2 on the Grade Distribution worksheet. It is important to note that this is a blank cell that is not adjacent to any data on the worksheet.
  2. Click the Insert tab of the Ribbon.
  3. Click the Column button in the Charts group of commands. Select the first option from the drop-down list of chart formats, which is the Clustered Column. This adds a blank chart to the worksheet.
  4. Click and drag the blank chart so the upper left corner is in the middle of cell H2.
  5. Resize the blank chart so the left side is locked to the left side of Column H, the right side is locked to the right side of Column P, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 16.
  6. Click the Select Data button in the Design tab of the Chart Tools section of the Ribbon. This opens the Select Data Source dialog box.
  7. Click the Add button on the Select Data Source dialog box. This opens the Edit Series dialog box.
  8. In the Series name input box on the Edit Series dialog box, type the word Class.
  9. Press the TAB key on your keyboard to advance to the Series values input box on the Edit Series dialog box.
  10. Highlight the range E4:E8 on the Grade Distribution worksheet. This automatically adds the range to the Series values input box. You also see bars added to the column chart (see Figure 14 Completed Data Series for the Class Grade Distribution).
  11. Click the OK button on the Edit Series dialog box.

     

    Completed Data Series for the Class Grade Distribution

    Figure 14 Completed Data Series for the Class Grade Distribution

  12. Click the Add button on the Select Data Source dialog box.
  13. In the Series name input box on the Edit Series dialog box, type the word College.
  14. Press the TAB key on your keyboard to advance to the Series values input box on the Edit Series dialog box.
  15. Highlight the range F4:F8 on the Grade Distribution worksheet. This automatically adds the range to the Series values input box. You also see bars added to the column chart.
  16. Click the OK button on the Edit Series dialog box.
  17. Click the Edit button on the right side of the Select Data Source dialog box under the Horizontal (Category) Axis Labels section. This is used to define the labels that will appear on the X-axis of the chart and opens the Axis Labels dialog box.
  18. Highlight the range A4:A8 on the Grade Distribution worksheet. This adds the range to the Axis Labels dialog box, and the labels appear on the X-axis on the column chart (see Figure 15 Final Settings for the Select Data Source Dialog Box).
  19. Click the OK button on the Axis Labels dialog box.
  20. Click the OK button on the Select Data Source dialog box.

     

    Final Settings for the Select Data Source Dialog Box

    Figure 15 Final Settings for the Select Data Source Dialog Box

  21. Click the Chart Title button on the Layout tab of the Chart Tools section of the Ribbon. Select the Above Chart option from the drop-down list.
  22. Click in the text box containing the chart title. Delete the generic chart title and replace it with the following: Grade Distribution Comparison.

Figure 16 Completed Grade Distribution Column Chart shows the final appearance of the column chart. The column chart is an appropriate type for this data because there are fewer than twenty data points and we can easily see the comparison for each category. An audience can quickly see that the class issued fewer As compared to the college. However, the class had more Bs and Cs compared with the college population.

Completed Grade Distribution Column Chart

Figure 16 Completed Grade Distribution Column Chart

Integrity Check

Too Many Bars on a Column Chart?

Although there is no specific limit for the number of bars you should use on a column chart, a general rule of thumb is twenty bars or less. Figure 17 Poor Use of a Column Chart contains a total of thirty-two bars. This is considered a poor use of a column chart because it is difficult to identify meaningful trends or comparisons. The data used to create this chart might be better used in two or three different column charts, each with a distinct idea or message.

Poor Use of a Column Chart

Figure 17 Poor Use of a Column Chart

Skill Refresher: Charts: Using Data in a Noncontiguous Range

  1. Click a blank cell location that is not adjacent to any data on the worksheet.
  2. Click the Insert tab of the Ribbon.
  3. Select a chart type and format in the Charts group of commands.
  4. Click the Select Data button in the Design tab of the Chart Tools section of the Ribbon.
  5. Click the Add button on the Select Data Source dialog box.
  6. In the Edit Series dialog box, type a name in the Series name input box or highlight a cell location on the worksheet that contains a description for the data series.
  7. Press the TAB key on your keyboard to advance to the Series values input box.
  8. Highlight the range of cells on the worksheet that contain the data that will appear on the Y axis for the series identified in step 6.
  9. Click the OK button on the Edit Series dialog box.
  10. Repeat steps 5 through 9 for each data series that you need to add to the chart.
  11. Click the Edit button on the right side of the Select Data Source dialog box.
  12. Highlight the range of cells that contain the descriptions for the X axis.
  13. Click the OK button on the Axis Labels dialog box.
  14. Click the OK button on the Select Data Source dialog box.