4.1.1 Choosing a Chart Type: Line Charts

LEARNING OBJECTIVES

  1. Construct a line chart to show a time series trend.
  2. Learn how to adjust the Y axis scale.
  3. Construct a line chart to present a comparison of two trends.
  4. Learn how to use a column chart to show a frequency distribution.
  5. Create a separate chart sheet for a chart embedded in a worksheet.
  6. Construct a column chart that compares two frequency distributions.
  7. Learn how to use a pie chart to show the percent of total for a data set.
  8. Construct a stacked column chart to show how a percent of total changes over time.

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

Integrity Check

Carefully Select Data When Creating a Chart

Just because you have data in a worksheet does not mean it must all be placed onto a chart. When creating a chart, it is common for only specific data points to be used. To determine what data should be used when creating a chart, you must first identify the message or idea that you want to communicate to an audience.

 

Table 4.1 Key Steps before Constructing an Excel Chart

Step Description
Define your message. Identify the main idea you are trying to communicate to an audience. If there is no main point or important message that can be revealed by a chart, you might want to question the necessity of creating a chart.
Identify the data you need. Once you have a clear message, identify the data on a worksheet that you will need to construct a chart. In some cases, you may need to create formulas or consolidate items into broader categories.
 

Select a chart type.

The type of chart you select will depend on the message you are communicating and the data you are using.
Identify the values for the X and Y axes. After you have selected a chart type, you may find that drawing a sketch is helpful in identifying which values should be on the X and Y axes. In Excel, the axes are:

The “category” axis. Usually the horizontal axis – where the labels are found

The “value” axis. Usually the vertical axis – where the numbers are found.

Time Series Trend: Line Chart 1

The first chart we will demonstrate is a line chart. Figure 4.1 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.

Figure 4.1 Stock Trends

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 4.1 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:

Download Data file:  CH4-Data_File

  1. Open data file CH4 Data File and save a file to your computer as CH4_YourName.
  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).
Figure 4.2 Selecting the Basic Line Chart

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

Why?

Line Chart vs. Column Chart

We can use both a line chart and a column chart to illustrate a trend over time. However, a line chart is far more effective when there are many periods of time being measured. For example, if we are measuring fifty-two weeks, a column chart would require fifty-two bars. A general rule of thumb is to use a column chart when twenty bars or less are required. A column chart becomes difficult to read as the number of bars exceeds twenty.

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

Note: Excel 2010 uses three contextual tabs for charts. Later versions use only two. Each has all the same tools. They are just organized a little differently.

 

Embedded 2D Line Chart with sizing handles. Vertical data on left side is Y or "value" axis. Horizontal data on bottom is X or "category" axis.
Figure 4.3 Embedded Line Chart in the Stock Trend Worksheet

As shown in Figure 4.3, 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 4.4 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.

Figure 4.4 Line Chart Moved and Resized

Integrity Check

When using line charts in Excel, keep in mind that anything placed on the X axis is considered a descriptive label, not a numeric value. This is an example of a category axis. This is important because there will never be a change in the spacing of any items placed on the X axis of a line chart. If you need to create a chart using numeric data on the category axis, you will have to modify the chart. We will do that later in the chapter.

 

Skill Refresher

Inserting a Line Chart

  1. Highlight a range of cells that contain data that will be used to create the chart. Be sure to include labels in your selection.
  2. Click the Insert tab of the ribbon.
  3. Click the Line button in the Charts group.
  4. Select a format option from the Line Chart drop-down menu.

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 4.5.
 Note: If you do not see “Format Axis . . . on your menu, you have not right clicked in the correct spot. Press “Escape” to turn the menu off and try again
Format Axis Pane with 0 in "Minimum" field under "Axis Options".
Figure 4.5 Format Axis Pane
  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 4.6 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.

2D Line chart shows Y axis adjusted to 500,000 as minimum value.
Figure 4.6 Adjusted Y Axis for the S&P 500 Chart

Skill Refresher

Adjusting the Y Axis Scale

  1. Click anywhere along the Y axis to activate it.
  2. Right Click.
    (Note, you can also select the Format tab in the Chart Tools section of the ribbon.)
  3. Select Format Axis . . .
  4. In the Format Axis pane, make your changes to the Axis Options.
  5. Click in the input box next to the desired axis option and then type the new scale value.
  6. Click the Close button at the top right of the Format Axis pane to close it.

Trend Comparisons: Line Chart 2

We will now create a second line chart using the data in the Stock Trend worksheet. The purpose of this chart is to compare two trends: the change in volume for the NASDAQ and the change in the Closing price.

Before creating the chart to compare the NASDAQ volume and sales price, it is important to review the data in the range B4:D28 on the Stock Trend worksheet. We cannot use the volume of sales and the closing price because the values are not comparable. That is, the closing price is in a range of $45.00 to $115.00, but the data for the volume of Sales is in a range of 684,000 to 3,711,000. If we used these values – without making changes to the chart — we would not be able to see the closing price at all.

The construction of this second line chart will be similar to the first line chart. The X axis will be the months in the range B4:D28.

  1. Highlight the range B4:D28 on the Stock Trend worksheet.
  2. Click the Insert tab of the ribbon.
  3. Click the Line button in the Charts group of commands.
  4. Click the first option from the list, which is a basic line chart.

Figure 4.6.5 shows the appearance of the line chart comparing both the volume and the closing price before it is moved and resized. Notice that the line for the closing price (Close) appears as a straight line at the bottom of the chart.  Also, the chart is covering the data again, and the title needs to be changed.

Trend Comparison Line Chart before move and resizing.
Figure 4.6 Trend Comparison Line Chart
  1. Move the chart so the upper left corner is in the middle of cell M3.
    Note: The line representing the closing values is flat along the bottom of the chart. This is hard to see and not very useful as is. Fear not. We will fix that.
  2. Resize the chart, using the resizing handles and the ALT key, so the left side is locked to the left side of Column M, the right side is locked to the right side of Column U, the top is locked to the top of Row 3, and the bottom is locked to the bottom of Row 17.
  3. Click in the text box that says “Chart Title.” Delete the text and replace it with the following: 24 Month Trend Comparison.

Good. But, we still cannot really see the Closing Price data. It is the flat red line at the very bottom of the chart.

  1. Right click the red line across the bottom of the chart that represents the Closing Price.
  2. On the menu, select Format Data Series. This will open the Format Data Series pane.
  3. In the Series Options, select Secondary Axis.
Format Data Series pane and options with Secondary Axis selected.
Figure 4.7 Adding a Secondary Axis

Better! But, it would be nice to be able to see that the values on the right represent prices.

  1. Right click the Secondary Vertical Axis. (The vertical axis on the right that goes from 0 to 140.)
  2. From the menu, select Format Axis.
  3. In Axis Options, select Number. (You may have to scroll down to see it.)
  4. Use the Symbol list box to add the $.
  5. Press the Close button to close the Format Axis pane.
  6. Save your work.
Format Axis Pane options Number and Symbol.
Figure 4.8 Modifying the Secondary Axis

 

Final 24 Month Comparison Line Chart shows Y axis value range 500,000 to 4,000,000, with secondary Y axis value range $20.00 - $140.00 and X axis value range 1-24 in months. Two lines on chart show values for Volume and Close.
Figure 4.9 Final Comparison Line Chart

“Instant” Chart – F11

On the Stock Trend worksheet:

  1. Select A4:A28.
  2. Hold down the Ctrl key and select D4:D28. Figure 4.10 shows what that will look like.
    Stock Trend sheet with D4:D28 and A4:A28 highlighted gray.
    Figure 4.10 Range Selection
  3. Press F11. (The F11 function key is on the top row of the keyboard.)  If the factory default settings haven’t been changed, Excel will create a column chart and place it on a separate chart sheet. (See Figure 4.11).
  4. Change the name of the chart sheet by double-clicking the worksheet name Chart1. Type Closing Prices as the new name and hit Enter.
  5. Save your work.

 

Stock Trend instant chart shows column chart Date data on X axis and Close data on Y axis.

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

  • 4_1
  • fig4.2
  • fig4-3
  • image4
  • image5
  • Figure-4-4
  • Figure 4-5
  • Figure 4-6
  • fig4-7
  • fig4-8
  • Figure 4-9
  • fig10

License

Icon for the Creative Commons Attribution 4.0 International License

4.1.1 Choosing a Chart Type: Line Charts by Emese Felvegi, Noreen Brown, Barbara Lave, Julie Romey, Mary Schatz, Diane Shingledecker, and Robert McCarn is licensed under a Creative Commons Attribution 4.0 International License, except where otherwise noted.