Saylor URL: http://www.saylor.org/books
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
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:
Highlight the range A3:A55 on the Stock Trend worksheet.
Hold down the CTRL key on your keyboard and highlight the range E3:F55.
Click the Insert tab of the Ribbon.
Click the Line button in the Charts group of commands.
Click the first option from the list, which is a basic line chart.
Figure 4.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.