PRDV007: Spreadsheets III: Presenting Data

A column chart is commonly used to show trends over time so long as the data are limited to approximately twenty points or less. Another common use for column charts is frequency distributions. A frequency distribution shows the number of occurrences by established categories. For example, a common frequency distribution used in most academic institutions is a grade distribution. A grade distribution shows the number of students that achieve each level of a typical grading scale (A, A−, B+, B, etc.). The Grade Distribution worksheet contains final grades for a hypothetical academic class. To show the grade frequency distribution, the numbers of students appear on the Y-axis and the grade categories appear on the X-axis. The following steps explain how to create this chart:

1. Highlight the range A3:B8 on the Grade Distribution worksheet. Column B shows the number of students that achieved a grade within the grade category shown in Column A.
2. Click the Column button in the Charts group section on the Insert tab of the Ribbon. Select the first format from the drop-down list of options, which is the Clustered Column format.
3. Click and drag the chart so the upper left corner is in the middle of cell H2.
4. Resize the 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.
5. Click the legend one time and press the DELETE key on your keyboard. Since the chart presents only one data series, the legend is not necessary.
6. Click the title of the chart twice so the cursor is placed in front of the word Class.
7. Type the following in front of the word Class: Final Grades for the.
8. Click any cell location on the Grade Distribution worksheet to deactivate the chart.

Figure 10 Grade Frequency Distribution Chart shows the completed grade frequency distribution chart. By looking at the chart, you can immediately see that the greatest number of students earned a final grade in the B+ to B− or the C+ to C− categories.

Figure 10 Grade Frequency Distribution Chart

Why?

Column Chart vs. Bar Chart

When using charts to show frequency distributions, the difference between a column chart and a bar chart is really a matter of preference. Both are very effective in showing frequency distributions. However, if you are showing a trend over a period of time, a column chart is preferred over a bar chart. This is because a period of time is typically shown horizontally, with the oldest date on the far left and the newest date on the far right. Therefore, the descriptive categories for the chart would have to fall on the X-axis, which is the configuration of a column chart. On a bar chart, the descriptive categories are displayed vertically along the Y-axis.