PRDV007: Spreadsheets III: Presenting Data

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 value for the S&P 500 and the Microsoft common stock. The S&P 500 is a benchmark that is commonly used to judge the performance of individual stocks. The purpose and message of this chart is to show whether Microsoft is performing better or worse than the S&P 500 index. This type of analysis can be used to determine whether a stock should be sold, purchased, or held.

Before creating the chart to compare the S&P 500 and Microsoft, it is important to review the data in the range E4:F55 on the Stock Trend worksheet. We cannot use the price data for Microsoft and the S&P 500 because the values are not comparable. That is, the data for Microsoft is in a range of $22.00 to $28.00, but the data for the S&P 500 is in a range of 1,022 to 1,363. If we used these values to create a chart, we would not be able to see any substantial change in the trend for either the S&P 500 or Microsoft. Therefore, formulas were used to calculate the percent change in value for the S&P 500 and Microsoft for each week. For example, looking at cells E5 and F5 on the Stock Trend worksheet, you see that the S&P 500 increased 2.4% in week 2, whereas Microsoft increased 3.1%. The percent change calculations now provide an appropriate method of comparison. This is a very important step to consider when comparing trends.

The construction of this second line chart will be similar to the first line chart. The X-axis will be the 52 weeks in the range A4:A55. However, the Y-axis will be the percentages in the range E4:F55. This creates a problem because Columns B, C, and D will not be used in this chart. Therefore, we cannot simply highlight one contiguous range of cells to create the chart. In this chapter, we will demonstrate two options for charting data that is not in a contiguous range. The following steps demonstrate the first option:

1. Highlight the range A3:A55 on the Stock Trend worksheet.
2. Hold down the CTRL key on your keyboard and highlight the range E3:F55.
3. Click the Insert tab of the Ribbon.
4. Click the Line button in the Charts group of commands.
5. Click the first option from the list, which is a basic line chart.

   Figure 7 Trend Comparison Line Chart shows the appearance of the line chart comparing the S&P 500 and Microsoft before it is moved and resized. Notice that Excel does not add a title to the chart.
   
   

   

   Figure 7 Trend Comparison Line Chart

6. Move the chart so the upper left corner is in the middle of cell H20.
7. 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 20, and the bottom is locked to the bottom of Row 35.
8. Click the Layout tab in the Chart Tools section of the Ribbon.
9. Click the Chart Title button in the Labels group of commands. Select the Above Chart option from the drop-down list (see Figure 8 Adding a Title to a Chart). This adds a generic title above the plot area of the chart.
10. Click in the text box containing the chart title. Delete the generic chart title and replace it with the following: 52 Week Trend Comparison.
    
    

Figure 8 Adding a Title to a Chart

Figure 9 Final Trend Comparison Line Chart shows that Microsoft has not performed as well as the S&P 500 benchmark. From week 31 to week 52, Microsoft is showing a significant decline compared to the S&P 500, which continues to grow. What makes this chart effective is that an audience can quickly see how Microsoft compares with the S&P 500 over the 52-week period.

Figure 9 Final Trend Comparison Line Chart