How to Graph a Trendline Analysis in Microsoft Excel
Use a trendline tool to map a trend on data in an Excel chart, analyzing current data and adding a future forecast.
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
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.
Choose a 2-D chart that is stacked, such as a bar, line, column, area, scatter, stock or bubble design. Excel doesn't allow you to use trendlines on 3-D graphs or designs that are not stacked.
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.
- A polynomial trendline uses an order command to set curves based on the data's peaks or troughs. The default order of two typically covers one curve. Change the order to three if you have two curves and four if you have up to three curves.
- A moving average trendline uses periods to average set intervals in the data. The default period of two covers two data points in each average. To experiment, increase the number.
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 check the trendline's accuracy, select the Display R-squared Value on Chart box to insert the R-squared value on the trendline. This value shows how well the trendline matches your data. It has a numeric value of 0 through 1; the closer the value is to 1, the better the fit.
- To change the format of the trendline, use the Fill & Line and Effects buttons on the Format Trendline menu.
- To remove a trendline, select the chart and open the Chart Elements tool. Uncheck the Trendline box to delete the line.
- You can also insert small trendline charts into worksheet cells with Excel's Sparklines tool.
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.
If you try to add a trendline to an unsupported design, the Trendline button is grayed out. To switch to a different format, open the Design tab and use the Change Chart Type button to choose a new chart.