PRDV007: Spreadsheets III: Presenting Data

The first chart we will demonstrate is a line chart. Figure 1 52 Week Data for the S&P 500 and Microsoft shows part of the data that will be used to create two line charts. The first line chart will show the trend of the S&P 500 stock index. This is an aggregate price index of five hundred of the largest publicly traded companies. This chart will be used to communicate a simple message: to show how the index has performed over a fifty-two-week 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 1 52 Week Data for the S&P 500 and Microsoft

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 1 52 Week Data for the S&P 500 and Microsoft, you will see that there are fifty-two 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 closing price for the S&P 500 on the Y-axis and the week number on the X-axis. The following steps explain how to construct this chart:

1. Highlight the range A3:B55 on the Stock Trend worksheet.
2. Click the Insert tab of the Ribbon.
3. Click the Line button in the Charts group of commands (see Figure 2 Selecting the Basic Line Chart).
4. Click the first option from the list, which is a basic line chart (see Figure 2 Selecting the Basic Line Chart). This adds, or embeds, the line chart to the worksheet, as shown in Figure 3 Embedded Line Chart in the Stock Trend Worksheet.
   
   

Figure 2 Selecting the Basic Line Chart

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 3 Embedded Line Chart in the Stock Trend Worksheet shows the embedded line chart in the Stock Trend worksheet. Notice that three 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.

Figure 3 Embedded Line Chart in the Stock Trend Worksheet

As shown in Figure 3 Embedded Line Chart in the Stock Trend Worksheet, 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 center of cell H2.
2. Resizing a chart: Place the mouse pointer over the left middle sizing handle, hold down the ALT key on your keyboard, and click and drag the chart so it “snaps” to the left side of Column H.
3. Repeat step 2 to resize the chart so the top “snaps” to the top of Row 2, the bottom “snaps” to the bottom of Row 17, and the right side “snaps” to the right side of Column P.
4. Adjusting the chart title: Click the chart title once. Then click in front of the letter S. You should see a blinking cursor in front of the letter S. This allows you to modify the title of the chart.
5. Type the following in front of the letter S in the chart title: 52 Week Trend for the.
6. Removing the legend: Click the legend once and press the DELETE key on your keyboard. This removes the legend from the chart. Since the chart contains only one data series, the legend is not necessary. Once you remove the legend, the plot area automatically expands.

Figure 4 Line Chart Moved and Resized 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 52 Week Trend for the S&P 500. Also 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 Line Chart Moved and Resized