This section reviews the most commonly used Excel chart types. To demonstrate the variety of chart types available in Excel, it is necessary to use a variety of data sets. This is necessary not only to demonstrate the construction of charts but also to explain how to choose the right type of chart given your data and the idea you intend to communicate.

Choosing a Chart Type

Before we begin, let’s review a few key points you need to consider before creating any chart in Excel.

1. The first is identifying your idea or message. It is important to keep in mind that the primary purpose of a chart is to present quantitative information to an audience. Therefore, you must first decide what message or idea you wish to present. This is critical in helping you select specific data from a worksheet that will be used in a chart. Throughout this chapter, we will reinforce the intended message first before creating each chart.
2. The second key point is selecting the right chart type. The chart type you select will depend on the data you have and the message you intend to communicate.  If you are using categorical (sometimes referred to as qualitative data), a bar chart or pie chart is appropriate.  If you are using quantitative data, then a histogram or line chart is appropriate.
3. The third key point is identifying the values that should appear on the X and Y axes. One of the ways to identify which values belong on the X and Y axes is to sketch the chart on paper first. If you can visualize what your chart is supposed to look like, you will have an easier time selecting information correctly and using Excel to construct an effective chart that accurately communicates your message. Table 4.1 “Key Steps Before Constructing an Excel Chart” provides a brief summary of these points.

Table 4.1 Key Steps before Constructing an Excel Chart

Time Series Trend: Line Chart 1

The first chart we will demonstrate is a line chart. Figure 2.61 shows part of the data that will be used to create two line charts. This chart will show the trend of the NASDAQ stock index.

Read more: http://www.investopedia.com/terms/n/nasdaq.asp

This chart will be used to communicate a simple message: to show how the index has performed over a two-year period. We can use this chart in a presentation to show whether stock prices have been increasing, decreasing, or remaining constant over the designated period of time.

Before we create the line chart, it is important to identify why it is an appropriate chart type given the message we wish to communicate and the data we have. When presenting the trend for any data over a designated period of time, the most commonly used chart types are the line chart and the column chart. With the column chart, you are limited to a certain number of bars or data points. As you increase the number of bars on a column chart, it becomes increasingly difficult to read. As you scroll through the data on the worksheet shown in Figure 2.61 you will see that there are 24 points of data used to construct the chart. This is generally too many data points to put on a column chart, which is why we are using a line chart. Our line chart will show the volume of sales for the NASDAQ on the Y axis and the Month number on the X axis. The following steps explain how to construct this chart:

If you wish to follow along, download Data file: CH4 Data

1. Open data file CH4 Data and save a file to your computer as CH4 Charting.
2. Navigate to the Stock Trend worksheet.
3. Highlight the range B4:C28 on the Stock Trend worksheet. (Note – you have selected a label in the first row and more labels in column B. Watch where they show up in your completed chart.)
4. Click the Insert tab of the ribbon.
5. Click the Line button in the Charts group of commands. Click the first option from the list, which is a basic 2D Line Chart (see Figure 4.2).

This adds, or embeds, the line chart to the worksheet, as shown in Figure 2.63

Figure 2.63 shows the embedded line chart in the Stock Trend worksheet. Do you see where your labels showed up on the chart?

Notice that additional tabs, or contextual tabs, are added to the ribbon. We will demonstrate the commands in these tabs throughout this chapter. These tabs appear only when the chart is activated.

As shown in Figure 2.63, the embedded chart is not placed in an ideal location on the worksheet since it is covering several cell locations that contain data. The following steps demonstrate common adjustments that are made when working with embedded charts:

1. Moving a chart: Click and drag the upper left corner of the chart to the corner of cell B30.
   Note: Keep an eye on your pointer. It will change into when you are in the right place to move your chart.

    

2. Resizing a chart: Place the mouse pointer over the right upper corner sizing handle, hold down the ALT key on your keyboard, and click and drag the chart so it “snaps” to the right side of Column I.
   Note: keep an eye on your pointer. It will change into when you are in the right place to resize your chart
3. Repeat step 2 to resize the chart so the top “snaps” to the top of Row 30, the bottom “snaps” to the bottom of Row 45, and the left side “snaps” to the left side of Column B. Make sure the right side of the chart snaps to the line between column I and J.
4. Adjusting the chart title: Click the chart title once. Then click in front of the first letter. You should see a blinking cursor in front of the letter. This allows you to modify the title of the chart.
5. Type the following in front of the first letter in the chart title: May 2014-2016 Trend for NASDAQ Sales.
6. Click anywhere outside of the chart to deactivate it.
7. Save your work.

Figure 2.64 shows the line chart after it is moved and resized. You can also see that the title of the chart has been edited to read May 2014-2016 Trend for NASDAQ Sales Volume. Notice that the sizing handles do not appear around the perimeter of the chart. This is because the chart has been deactivated. To activate the chart, click anywhere inside the chart perimeter.

Adjusting the Y Axis Scale

After creating an Excel chart, you may find it necessary to adjust the scale of the Y axis. Excel automatically sets the maximum value for the Y axis based on the data used to create the chart. The minimum value is usually set to zero. That is usually a good thing. However, depending on the data you are using to create the chart, setting the minimum value to zero can substantially minimize the graphical presentation of a trend. For example, the trend shown in Figure 4.4 appears to be increasing slightly in recent months. The presentation of this trend can be improved if the minimum value started at 500,000. The following steps explain how to make this adjustment to the Y axis:

1. Click anywhere on the Y (value or vertical) axis on the May 2014-2016 Trend for NASDAQ Sales Volume line chart (Stock Trend worksheet).
2. Right Click and select Format Axis. The Format Axis Pane should appear, as shown in Figure 2.65.

1. In the Format Axis Pane, click the input box for the “Minimum” axis option and delete the zero. Then type the number 500000 and hit Enter. As soon as you make this change, the Y axis on the chart adjusts.
2. Click the X in the upper right corner of the Format Axis pane to close it.
3. Save your work.

Figure 2.66 shows the change in the presentation of the trend line. Notice that with the Y axis starting at 500,000, the trend for the NASDAQ is more pronounced. This adjustment makes it easier for the audience to see the magnitude of the trend.

Frequency Distribution: Column Chart

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:

1. Select the Grade Distribution worksheet.
2. Change the years in Row3 to the current academic term and year.
3. Highlight the range A3:A8 on the Grade Distribution worksheet. Column A shows the grade categories.
4. Hold down the Crtl key.
5. Without letting go of the Ctrl key, select C3:C8
6. 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.
7. Click and drag the chart so the upper left corner is in the middle of cell H2.
8. 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.
9. 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.
10. 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).
11. Click any cell location on the Grade Distribution worksheet to deactivate the chart.
12. Save your work.

Figure 2.67 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.

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. More is considered a poor use of a column chart because it is difficult to identify meaningful trends or comparisons. 

Percent of Total: Pie Chart

The next chart we will demonstrate is a pie chart. A pie chart is used to show a percent of total for a data set at a specific point in time. It is an appropriate chart for qualitative data. The data we will use to demonstrate a pie chart is related to enrollment data for Portland Area Community Colleges for Fall of 2014. You will find that data on the Enrollment Statistics sheet.

1. Highlight the range A2:B6 on the Enrollment Statistics worksheet.
2. Click the Insert tab of the ribbon.
3. Click the Pie button in the Charts group of commands.
4. Select the first “2-D Pie” option from the drop-down list of options.
5. To make the “slices” stand out better, “explode” the pie chart.
   • Click and hold the mouse button down in any of the slices of the pie.
   • Note that you have selection handles on all of the pie slices.
   • Without letting go of your mouse button; drag one of the slices away from the center.
   • All of the slices “explode” out from the center.

Note: if you let go of the mouse button before dragging, you may only get one slice to move when you drag it out from the center. This can be another option for displaying your data.  Use the Undo button to undo this if you want to try again.

1. Click off the slices and into the white canvas to deselect the pie and select the entire chart.
2. Click and drag the pie chart so the upper left corner is in the middle of cell E2.
3. 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 L, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 10 (see Figure 2.68).

1. Click the chart legend once and press the DELETE key on your keyboard. A pie chart typically shows labels next to each slice. Therefore, the legend is not needed.
2. Right click any of the slices in the pie chart, and select Add Data Labels from the list. This will add the values for each of the slices in the pie.
3. Now, you can right click one of the numbers and select Format Data Labels from the list. This will open the Format Data Labels pane on the right.
4. Check the boxes for Category Name and Percentage in the Label Options section in the Format Data Labels pane.  This will add the Race/ethnicity labels as well as the percentage data to the pie chart.
5. Uncheck the box next to the Value box. This will remove the numbers from the pie chart (see Figure 2.69).
6. Click the Close button at the top of the Format Data Labels pane.
7. Select the data labels again (if needed). Click the Home tab of the ribbon and then click the Bold button. This will bold the data labels on the pie chart.
8. Save your work.

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.

Skill Refresher

Inserting a Pie Chart

1. Highlight a range of cells that contain the data you will use to create the chart.
2. Click the Insert tab of the ribbon.
3. Click the Pie button in the Charts group.
4. Select a format option from the Pie Chart drop-down menu.

Percent of Total: Stacked Column Chart

You will not have to construct this chart in our course, but reading through how it is constructed will help you when we begin our statistics unit.  We use a stacked column chart to show a percent of a total . For example, the data on the Enrollment Statistics worksheet shows student enrollment by race for several colleges. We would like to see all of the data on all of the colleges.

1. Highlight the range A2:D6 on the Enrollment Statistics 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 2-D Column section in the drop-down list (see Figure 2.611).

Figure 2.612 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 enrollment of students by race. However, notice that Excel places the racial categories on the X axis. It would be more useful if the different colleges were there instead.

The reason that Excel organized the data this way is that there are more Race/ethnicity categories (data in column A) than there are colleges (data in row 2). Not a bad guess. But, not what we wanted in this case.

The remaining steps explain how to correct this problem and complete the chart:

1. 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.
   

   

2. Click and drag the chart so the upper left corner is in the middle of cell E12.
3. 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 12, and the bottom is locked to the bottom of Row 30.
4. Click the legend one time and press the DELETE key on your keyboard.
5. Add a Data Table. This is another way of displaying a legend for a column chart along with the numerical values that make up each component.
   • In earlier versions of Excel, find the Labels group of commands and select the Show Data Table with Legend Keys option from the drop-down menu.
   • In Excel 2016, find the Add Chart Element tool on the Design tab, select Data Table With Legend Keys
6. Change the Chart Title to Enrollment by Race.
   • If there is no chart title, you will need to add one using the Add Chart Element tool on the Design tab.
7. Save your work.

Figure 2.613 shows the final stacked column chart. Notice the similarities and differences in the enrollment at the local community colleges.

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.

Representing Quantitative Data: the Histogram

This is the last chart we will cover in this chapter, and perhaps the one we will use most in our class.  Histograms are used to display quantitative data. For example, the data on this math test is displayed in a frequency distribution and a histogram in Figure 2.614. Notice that the bins are ranges of numbers, not attributes:

 

While the directions for creating a histogram vary due to the version of Excel you have, these directions work for most versions:

1.  Use Max and Min functions to determine the class sizes of this data.
2. Set up class sizes, and then create a new ‘Bin’ column to record the upper limit of each class.
3. Click on ‘Data’ to see if ‘Data Analysis’ is in the upper right corner of the ribbon.  If it is not, you will need to upload the Data Analysis Toolpak (directions are in the video).
4. Click on ‘Data Analysis’ and select ‘Histogram.’
5. A pop-up window will appear.  You want to include the range of your data in ‘Input Range.’ To do that, select your data and the range of numbers will appear in that section of the pop-up.
6. For ‘Bin Range’ include the column of upper class limits.
7. Enable ‘Output Range’ and select the cell on your sheet where you would like the histogram and the frequency distribution to appear.
8. Click on ‘Chart Output’ so that the histogram will be pasted on this page.

Notice the histogram will appear with gaps between the bars and only the upper limit of each class showing.  You will want to edit your chart:

1. Use the original class limits and frequency to create a well-formatted frequency chart (if needed).
2. Copy the original class limits into the ‘Bin’ in the created frequency chart to change the horizontal labels on your histogram.
3. Right click on any bar, and then select ‘Format Data Point.’ You will see a slider for gap width.  Reduce the gap width to zero or less than 5% if you want a bit of a gap.
4. Replace or copy the entire range of each class size into the ‘bin’ in the frequency chart.
5. See Section 2.8 of this chapter for more formatting instructions.

This video explains the process in more detail:

There is also a histogram option under ‘Insert’ in the Excel ribbon.  Highlight your data first and then select this option.  You cannot set the class limits, but you can adjust the number of bins and the width of the bins by right-clicking or double-clicking on the bars of the created histogram.  You can also add labels by clicking once on the histogram chart and then selecting ‘Chart Design’ tab.  If you do not mind where the classes start numerically, this may be a viable option!

Skill Refresher:

 Inserting a Histogram

1. Click on the Data tab of the ribbon to be sure that the Data Analysis Toolpak has been installed. If not, follow the directions above to install this add-in.
2. Be sure you have set up a column of the upper range of each class in your histogram.
3. Click on Data Analysis in the upper right corner of the ribbon and select histogram.
4. Select the data you want to be analyzed (input range).
5. Click on bin range and then select the upper range of values from Step 2.
6. Check off Output Range and select a cell from the area where you would like the data to display.
7. Check of Chart Output and then click OK.

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.
• 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 categorical (or qualitative) data set.
• A stacked column chart is used to show how a percent total changes over time.
• A histogram is used to represent quantitative data.

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.