# 4.1.2 Choosing a Chart Type: Column Charts

# Frequency Distribution: Column Chart 1

A column chart is commonly used to show trends over time, as long as the data are limited to approximately twenty points or less. A 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 some hypothetical Excel classes. To show the grade frequency distribution for all the Excel classes in that year, the numbers of students appear on the Y axis and the grade categories appear on the X axis. The number of students for this chart is in Column C. The labels for grades are in Column A. The following steps explain how to create this chart:

- Select the Grade Distribution worksheet in your CH4-Data_File.
- Change the years in Row3 to the current academic term and year.
- Highlight the range
**A3:A8**on the**Grade Distribution**worksheet. Column A shows the grade categories. - Hold down the
**Crtl key**. - Without letting go of the
**Ctrl key**, select**C3:C8** - Click the
**Column**button in the**Charts**group section on the**Insert**tab of the ribbon. Select the first option in the 2-D Column section, which is the**Clustered Column**format. - Click and drag the chart so the upper left corner is in the middle of cell
**H2**. - 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 O, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 16.
- If Excel displays a legend, delete it by clicking the legend one time and pressing the
**DELETE**key on the keyboard. Since the chart presents only one data series, the legend is not necessary. - Add the text
**Final Grades for**to the chart title. The chart title should now be**Final Grades for All Excel Classes 2016/2017**(or whichever academic year you are using). - Click any cell location on the
**Grade Distribution**worksheet to deactivate the chart. - Save your work.

**Figure 4.12** 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− range.

### 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 horizontal – or category axis, which is the configuration of a column chart. On a bar chart, the descriptive categories are displayed on the vertical axis.

# Creating a Chart Sheet

- Click anywhere on the
**Final Grades for All Excel Classes**chart on the Grade Distribution worksheet. - Right click on the chart. Select
**Move Chart . . .**This opens the Move Chart Dialog box. - Click the
**New sheet**option on the Move Chart dialog box. (The top option.) - The entry in the input box for assigning a name to the chart sheet tab should automatically be highlighted once you click the New sheet option. Type
**All Excel Classes.**This replaces the generic name in the input box (see**Figure 4.13**). - Click the
**OK**button at the bottom of the Move Chart dialog box. This adds a new chart sheet to the workbook with the name**All Excel Classes**. - Save your work.

**Figure 4.14** shows the Final Grades for the all the Excel Classes column chart is in a separate chart sheet. Notice the new worksheet tab added to the workbook matches the New sheet name entered into the Move Chart dialog box. Since the chart is moved to a separate chart sheet, it no longer is displayed in the Grade Distribution worksheet.

# Frequency Comparison: Column Chart 2

We will create a second column chart to show a comparison between two frequency distributions. Column B on the Grade Distribution worksheet contains data showing the number of students who received grades within each category for the Spring Quarter. We will use a column chart to compare the grade distribution for Spring (Column B) with the overall grade distribution for the whole year (Column C).

However, since the number of students in the term is significantly different from the total number of students in the year, we must calculate percentages in order to make an effective comparison. The following steps explain how to calculate the percentages:

- Highlight the range
**B9:C9**on the**Grade Distribution**worksheet. - 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. - Activate cell
**E4**on the Grade Distribution worksheet. - 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**. - Copy the formula in cell
**E4**and paste it into the range**E5:E8**using the Paste command.

Or, use the Fill Handle to copy the calculation in**E4**all the way down to**E8**. - Activate cell
**F4**on the Grade Distribution worksheet. - 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**. - Copy the formula in cell
**F4**and paste it into the range**F5:F8**using the Paste command.

Or, use the Fill Handle to copy the calculation in**F4**all the way down to**F8**.

**Figure 4.15** shows the completed percentages added to the Grade Distribution worksheet.

The column chart we are going to create 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. This chart uses data that is not in a contiguous range, so we need to use the Ctrl key to select the ranges of cells.

- Select
**A3:A8,**hold down the**Ctrl**key and select**E3:F8.** - Click the
**Insert**tab of the ribbon. - 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**. - Click and drag the chart so the upper left corner is in the middle of cell
**H2**. - 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 N, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 16.
- Change the chart title to
**Grade Distribution Comparison**. If you do not have a chart title, you can add one. On the**Design**tab, select**Add Chart Element**. Find the**Chart Title**. Select the**Above Chart**option from the drop-down list. - Save your work.

**Figure 4.17** 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.

### 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 4.18** 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.

# Attribution

Adapted by Noreen Brown from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.

### Media Attributions

- Figure 4-12
- Figure 4-14
- Figure 4-16
- Figure 4-17
- fig4-18