Do Trend Analysis in Excel

Trend analysis is taking past data and using it to project future results. Microsoft Excel gives you the ability to display your data in chart form and then add one or more trend lines to it to project how the data might appear in the future. The following steps break down how to do trend analysis in Excel 2003, 2007, and 2010.

Steps

Creating the Chart

  1. Enter the data into your Excel spreadsheet. You should have enough data to cover a reasonable period of time, such as two years or more. You should also have consistent intervals, such as weekly, monthly, or yearly entries.
    • If you're missing data for a given period of time, you can interpolate a reasonable estimate based on the figures you do have. For example, if you're missing April's sales figure, and you have a sales figure of $200 for February, $250 for March, $350 for May, and $400 for June, you can reasonably conclude that April's sales were $300 and enter that figure. Likewise, if you have the figures for the previous year and notice that this year's sales run about 10 percent higher than last year's, and you have last April's figures but not this April's, you can enter a figure for this April 10 percent higher than the figure you have for last year.
  2. Select the data you want to include in the chart. You can select the data either by using your mouse or by selecting a single cell and pressing the "Ctrl" and "A" keys simultaneously to select all adjacent cells with data in them.
  3. Access the Chart feature. While Excel offers a number of chart options, you'll want to set up a line chart for your trendline.
    • In Excel 2003, select "Chart" from the Insert menu. Click the Standard Types tab, then choose "Line" under "Chart type," and then click "Finish."
    • In Excel 2007 and 2010, click the Insert tab, then click Line dropdown button in the Charts section of the Insert menu ribbon. Select the line chart you want from the options displayed.
  4. Remove the chart legend, if desired. The chart legend explains which colors represent which dataset in the chart. Removing the legend offers more space for the chart itself.
    • To remove the legend in Excel 2003, click the Legend button on the Chart toolbar.
    • To remove the legend in Excel 2007 or 2010, click the Legend dropdown button in the Labels group on the Layout menu ribbon and select "None."

Applying a Trendline

  1. Click on the chart. This displays the menus or menu tabs you need to access Excel's trendline features.
    • In Excel 2003, this displays the Chart menu.
    • In Excel 2007 and 2010, this displays the Design, Layout, and Format tabs.
  2. Select the data series you wish to have a trend line for. This is necessary only if you have more than one data series on your chart.
    • You can select the data series by clicking its line in the chart. Excel 2007 and 2010 also let you select the series from the dropdown list at the top of the Current Selection section of the Layout menu ribbon.
    • If you don't select a series, Excel will prompt you for the series once you choose to apply a trendline to your chart.
  3. Access the trendline feature.
    • In Excel 2003, select Add Trendline from the Chart menu. This displays the Add Trendline dialog.
    • In Excel 2007 and 2010, click the Trendline dropdown button in the Analysis group of the Layout menu ribbon.
  4. Choose the type of trendline you want. What type of trendline you want depends on how you want to analyze your data. See "Choosing the Right Trendline" for a discussion of the trendline types.
    • In Excel 2003's Add Trendline dialog box, click the Type tab of the Add Trendline dialog and select the trendline tab from the Trend/Regression type list.
    • In Excel 2007 and 2010, select the type of trendline from the Trendline button dropdown list. If you don't see the type of trendline you want, click More Trendline Options to select it from the Trendline Options section of the Format Trendline dialog.
  5. Establish the range you wish to forecast for. You can project a trend forward, backward, or both.
    • In Excel 2003, click the Options tab of the Add Trendline dialog and enter a number in the Forward field of the Forecast section to project a trend forward. (To project a trend backward, enter a number in the Backward field.)
    • In Excel 2007 and 2010, select More Trendline Options from the Trendline button dropdown list to display the Format Trendline dialog and enter a number in the Forward field of the Forecast section to project a trend forward.
  6. Display the R-squared value, if desired. The R-squared value indicates how closely your trendline follows your data; the closer its value is to 1, the closer it follows your data. To display this value, check the "Display R-squared value on chart" box.
    • You can move the R-squared value to another location on the chart by clicking on it to display a set of sizing handles. Move your cursor until it changes to a 4-headed arrow, then hold down your left mouse button and drag the value to a new location.
    • You may wish to try several of the trendline types discussed below to find which trendline best fits your data.

Choosing the Right Trendline

  1. Use a linear trendline for simple data. If your data fits best around a straight line, you'll want to use a linear trendline. This trendline represents a steady rate of increase or decrease.
  2. Use a logarithmic trendline for data that begins with a sharp change and levels out. Data such as that for a startup business that experiences a flurry of initial sales that levels off over time can best be represented with this curved line.
  3. Use a moving average trendline for rapidly fluctuating data. Data that cycles up and down, such as stock prices, can best be represented with a moving average trendline that smooths out the fluctuations to show the overall trend.
    • Moving average trendlines require setting a number of data points to be averaged; setting the period to two averages of each two successive points (first and second, second and third, third and fourth, etc.) to produce the points in the trendline.
  4. Use a polynomial trendline for gradually fluctuating data. While a moving average trendline suppresses individual fluctuations, a polynomial trendline brings them out. This can be important for such things as analyzing fuel consumption at a given speed, where a peak (or bottom) value needs to be found.
    • Polynomial trendlines are classed according to the maximum number of peaks or valleys they contain. An Order 2 polynomial trendline has a single peak or bottom value, while an Order 3 trendline can have two such values, an Order 4 trendline can have up to three such values, and so on.
  5. Use a power trendline when working with measurements that increase at specific rates. This works with data sets such as those representing vehicle acceleration.
    • Power trendlines are valid only for data sets where all values are greater than zero.
  6. Use an exponential trendline for data that rises or falls at rates that increase constantly. This trendline can be used with such things as radioactive decay, which progressively slows down over time.
    • Exponential trendlines are valid only for data sets where all values are greater than zero.

Warnings

  • Be sure to have enough past data to make a meaningful trend analysis. Having insufficient data, or failing to take normal fluctuations for a seasonal business into account, may skew your results. At least two years' worth of data is recommended, longer if you have it.

Sources and Citations

Related Articles

Retrieved from "https://kipkis.com/index.php?title=Do_Trend_Analysis_in_Excel&oldid=273704"