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.
Video of the Day
Create a Chart
Insert the data you want to analyze in the worksheet and select it. Open the Insert tab.
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
Select the chart to open editing shortcuts. Select the Chart Elements icon and then the arrow next to Trendline.
Select More Options.
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:
- Exponential: An exponential trendline is curved and suits data that increases or decreases at a constant rate.
- Linear: A linear line best suits simple data that works in a straight line, increasing or decreasing in a regular pattern.
- Logarithmic: A logarithm line is useful if your data curves with a significant increase or decrease before leveling out to a regular pattern.
- Polynomial: A polynomial trendline works best with data that has peaks and troughs.
- Power: A power line is best used on data that increases at predetermined rates.
- 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.
Select a trendline from the list. The line previews on the chart.
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.
To remove a trendline, select the chart and open the Chart Elements tool. Uncheck the Trendline box to delete the line.
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.