The tutorial explains how to do trend analysis in Excel: how to insert a trendline in a chart, display its equation and get the slope of a trendline.
When plotting data in a graph, you may often want to visualize the general trend in your data. This can be done by adding a trendline to a chart. Luckily, Microsoft Excel has made inserting a trend line very easy, especially in newer versions. Nevertheless, there are a few little secrets that make a big difference, and I will share them with you in a moment.
A trendline, also referred to as a line of best fit, is a straight or curved line in a chart that shows the general pattern or overall direction of the data.
This analytical tool is most often used to show data movements over a period of time or correlation between two variables.
Visually, a trendline looks somewhat similar to a line chart, but it doesn't connect the actual data points as a line chart does. A best-fit line shows the general trend in all the data, ignoring statistical errors and minor exceptions. In some cases, it can also be used to forecast trends.
A trendline can be added to a variety of Excel charts, including XY scatter, bubble, stock, as well as unstacked 2-D bar, column, area and line graphs.
You cannot add a trendline to 3-D or stacked charts, pie, radar and similar visuals.
In Excel 2019, Excel 2016 and Excel 2013, adding a trend line is a quick 3-step process:
Tip. Another quick way to add trendline to an Excel chart is to right-click the data series and then click Add Trendline….
To add a trendline in Excel 2010, you follow a different route:
Microsoft Excel allows adding more than one trendline to a chart. There are two scenarios that should be handled differently.
To put a trendline on a chart that has two or more data series, here's what you do:
As the result, each data series will have its own trendline of the matching color:
Alternatively, you can click the Chart Elements button, then click the arrow next to Trendline and choose the type you want. Excel will show a list of the data series plotted in your chart. You pick the needed one and click OK.
To make two or more different trendlines for the same data series, add the first trendline as usual, and then do one of the following:
To make your graph even more understandable and easily interpreted, you may want to change the default appearance of a trendline. For this, right-click it and then click Format Trendline… . Or simply double-click the trendline to open the Format Trendline pane.
On the pane, switch to the Fill & Line tab and choose the color, width and dash type for your trendline. For example, you can make it a solid line rather than a dashed line:
To project the data trends into the future or past, this is what you need to do:
In this example, we choose to extend the trendline for 8 periods beyond the last data point:
Trendline equation is a formula that mathematically describes the line that best fits the data points. The equations are different for different trendline types, though in every equation Excel uses the least squares method to find the best fit for a line though data points. You can find the equations for all Excel trendlines types in this tutorial.
When drawing the line of best fit in Excel, you can display its equation in a chart. Additionally, you can display the R-squared value.
R-squared value (Coefficient of Determination) indicates how well the trendline corresponds to the data. The closer the R2 value to 1, the better the fit.
To show the equation and R-squared value on a chart, do the following:
This will put the trendline formula and the R2 value at the top of your graph, and you are free to drag them wherever you see fit.
In this example, the R-squared value equals 0.957, which means that the trendline fits about 95% of data values.
Note. The equation displayed on an Excel chart is correct only for XY scatter plots. For more information, please see Why Excel trendline equation is wrong.
If the Excel trendline equation delivers inaccurate results when you supply x values to it manually, most likely it's because of rounding. By default, the numbers in the trendline equation are rounded to 2 - 4 decimal places. However, you can easily make more digits visible. Here's how:
To get the slope of the linear trendline, Microsoft Excel provides a special function of the same name:
With the x values in B2:B13 and the y values in C2:C13, the formula goes as follows:
The slope can also be calculated by using the LINEST function in a regular formula:
If entered as an array formula by pressing Ctrl + Shift + Enter, it would return the slope of the trendline and y-intercept into two adjacent cells in the same row. For more information, please see How to use the LINEST function in Excel.
As you can see in the following screenshot, the slope value returned by the formulas exactly matches the slope coefficient in the linear trendline equation displayed in our graph:
The coefficients of other trendline equation types (Exponential, Polynomial, Logarithmic, etc.) can also be calculated, but you'd need to use more complex formulas explained in Excel trendline equations.
To remove a trendline from your chart, right-click the line, and then click Delete:
Or click the Chart Elements button and unselect the Trendline box:
Either way, Excel will immediately remove the trendline from a chart.
That's how to do a trendline in Excel. I thank you for reading and hope to see you on our blog next week!
Table of contents