Trendlines in Excel

In Basic by Garrett Dorman0 Comments

Share with:


Trendlines are an important tool to measure overall change in data. These lines are designed to track data movements in the long-term, ignoring the subtle changes in the short-term. There are many uses for trendlines, such as for finding the best-fit line for a data set, or for predicting the values of future data. An example of a trendline inserted in an Excel chart is shown in the below figure.

Example Trendline

A trendline can be added to any chart very easily. After your basic chart is created, first select Add Chart Element either on the Design toolbar or in the submenu which appears when the chart is selected. Next, simply select Trendline and then select which type of trendline you would like to add. Linear is the standard, showing a straight line along the data set, however, the others will be discussed shortly. An image showing the adding of a trendline can be seen below.

Inserting a Trendline

There are several different types of trendlines, with each having its own distinct characteristics. The standard type of trendline is the linear trendline. This simply applies a straight, best-fit line through the data set. Another type of standard trendline is the forecast type. This trendline acts in the same way as the linear, however, will continue to interpret past the data points, showing the expected result of future data. The exponential trendline is another of the primary types. Instead of displaying a straight line best-fit, this trendline will display the best-fit curve to the data. There is also the moving average trendline. This will produce a line which reflects the current average of the data, instead of a best-fit line. An example of each of these can be seen in the image below.

Trendline Types

Most of the best-fit type trendlines will use the Method of Least Squares to find the appropriate formula to the best-fit line. While the details to this method are mostly irrelevant to the average Excel user, it is important to talk about one aspect of this method, which is the variable R squared. The details of how this value is calculated is not important, but the actual value is. This is an estimate of how accurate the trendline is to the given data. An R-squared value of exactly 1 is a perfect fit to the data. Otherwise, the closer this value is to 1, the more accurate the trendline will be.

In order to display the R-squared value along with the trendline, first right click on the trendline and select Format Trendline. Next, select Trendline Options and check the box which says “Display R-squared value on chart”. This will cause the R-squared value to appear next to the trendline on the main page. An example of this can be seen in the below image.

R-Squared

Share with: