How to Graph a Trendline Analysis in Microsoft Excel

Excel's Trendline tool allows you to analyze trends in your data and map them on a chart. When you set up a trendline, you can also extend it into the future, adding a future forecast to current data. To get started, create a chart and add a trendline to it.

Create a Chart

Step

Insert the data you want to analyze in the worksheet and select it. Open the Insert tab.

Select your data and open Insert.
credit: Image courtesy Microsoft
Select a recommended chart or a chart from the menu.
credit: Image courtesy Microsoft

Step

Go to the Charts area. Select Recommended Charts to see Excel's suggestions on suitable charts for the data or select the arrow by a chart icon to open layout options. Select a chart to insert it in the worksheet.

Add a Trendline

Step

Select the chart to open editing shortcuts. Select the Chart Elements icon and then the arrow next to Trendline.

Open Chart Elements and select the Trendline arrow.
credit: Image courtesy Microsoft
Choose More Options from the Trendline menu.
credit: Image courtesy Microsoft

Step

Select More Options.

Step

Open the Trendline Options menu on the Format Trendline menu. Before you choose a trendline, think about the type of analysis you should use. Excel has six types of trendlines; choose the best fit for your data:

Step

  1. Exponential: An exponential trendline is curved and suits data that increases or decreases at a constant rate.
  2. Linear: A linear line best suits simple data that works in a straight line, increasing or decreasing in a regular pattern.
  3. Logarithmic: A logarithm line is useful if your data curves with a significant increase or decrease before leveling out to a regular pattern.
  4. Polynomial: A polynomial trendline works best with data that has peaks and troughs.
  5. Power: A power line is best used on data that increases at predetermined rates.
  6. Moving Average: A moving average trendline helps analyze disparate data that may otherwise be hard to trend clearly, splitting data into periods and calculating their averages.
Choose a trendline to add to the chart.
credit: Image courtesy Microsoft

Step

Select a trendline from the list. The line previews on the chart.

Add a forecast to the trendline.
credit: Image courtesy Microsoft

Step

To add a forward or backward forecast to the trendline, change the periods in the Forward or Backward boxes. For example, to add a six-month future forecast to the trendline, enter 6 in the Forward box. Select the X on the Format Trendline menu to apply the line to the chart.

Step

To remove a trendline, select the chart and open the Chart Elements tool. Uncheck the Trendline box to delete the line.

Step

You can add multiple trendlines if you have more than one data set on the chart. When you highlight the chart and select the arrow next to Trendline from the Chart Ekements menu, you'll see a prompt asking you which data series to use for the trnedline. Choose a series and, when you;'ve added its trendline, quit the Format Trendline menu, select the chart and repeat the process for the next series.

Step

mulitple trendlines