Excel Trendline

Creating a graph or chart using your existing data in Excel is easy. But sometimes, you want to visualize the general trends in your data. This operation can be easily performed by adding a trendline to your graph. It may sound new or complex, but inserting a trend line is very easy with newer Excel versions. All you need to know is some little tricks to make your work easy.

In this tutorial, we will discuss the various steps to quickly add a trendline in Excel, show its equation, fetch the slope of a trendline, and many more.

Before starting with its implementation let's understand what does a trendline means:

What is a Trendline?

"A trendline, or a line of best fit, is represented either by a straight or curved line in a graph. It displays the generic pattern or complete flow of the chart's data."

Trendline is an analytical tool used by Excel users to display data actions over a period of time or the relations between two variables.

When a trendline is created, it looks similar to a line chart, but the concept and functionality of both are different. The trendline doesn't connect the data points, whereas line points connect the points, showing the relationship between given data. A best-fit line displays the overall trend in all the data and implicitly ignores the statistical errors and insignificant exceptions.

How to add a trendline in Excel

In the latest versions of Excel, unlike Excel 2013, Excel 2016 and Excel 2019, forming a trend line is quick and super easy. All you need to do is to follow three simple steps:

1. The first step is adding a chart to your worksheet and selecting it by clicking anywhere on it.
   
2. Towards the right of chart, you will see three icons, click on the Chart Element icon (one aligned at the top with cross button).
   
3. The following window will appear. Tick on the Trendline checkbox to insert the default linear trendline. As show below, it will add a trendline in your Excel chart.
   

That it's! 3 steps and your trendline is applied.

If you don't want the default trendline and want to customise its look and appearance do the following:

1. Click on the arrow next to the trendline-> click on more options.
   
2. Excel will open the Format Trendline window. Click on the Trendline Options tab.
   
3. It will enable the user to see the various types of trendline available in Excel such as Exponential, Linear, Logarithmic, Polynomial, Moving Average, Power. Choose the any of the options and it will adapted your chart.
   

Note: You can also quickly add a trendline to an Excel chart by right-clicking the data series and from the options we will click on Add Trendline?.

How create a trendline in Excel 2010

In the above section we covered how to add a Trendline to latest Excel versions. But if you are still working on Excel 2010 don't get dishearten, as following the below steps can help you to insert a trendline in Excel 2010. Though the steps are different but the result will be same:

1. Create a Chart in your Excel worksheet. On the same chart, click on the data series where you want to add the trendline.
2. Click on the Chart Tools option -> Layout tab >Analysis group, under this group select the Trendline option and:
   • The following options will be displayed, select any one of the options, or
   • Click on the More Trendline Options?, and choose any preferred trendline type for your Excel chart.
   
   

Add multiple Trendline in your worksheet

Want to try more with Trendlines. Here you go!

Why to get satisfied with one Trendline when Microsoft Excel allows you to add more than one to your chart. We will cover two different methods that should be handled differently.

Add a trendline for two or more data series

In the above example, as you can see, we have two data series. To insert two trendlines on a chart separately for two data series, follow the below-given steps:

1. Select any one of the data points and right-click on it (we have selected the orange ones in this example). The context menu window will appear. Choose the Add Trendline option.
   
2. The Trendline Options tab will open on the right side of the worksheet pane. Choose the required type, and it will be applied to your chart.
   
3. To insert the trendline for other data points repeat the above steps.
4. As the result, both the bar charts will have its unique trendline.
   

Create different types trendline types for the same data series

We can also add two or more different trendlines for the same data series. To implement the same, firstly add the normal trendline by following the above steps, and add the next trendline with the help of the given below steps:

1. Select the data series and Right-click on it. The following context menu will be displayed, click on the Add Trendline... option.
   
2. Next, since we want to add another trendline, therefor from the trendline pane we will choose a different trend line type (we have selected moving average)
   
3. You will have the following result.
   

OR

1. Select the chart from the right side of the chart panel and click on the Chart Element icon (one aligned at the top with a cross button). Click on the arrow icon next to Trendline, and select any different type of Trendline that you want to add.

Either way you choose, Excel will show two different trendlines in your chart.

How to format a trendline in Excel

What if you want your trendline to be more attractive and understandable? Formatting a trendline in an Excel chart is super easy.

1. To implement this right, click on the Trendline, and the following window will be displayed. Click on the Format Trendline option.
   
2. The format pane window will be displayed towards the left of your worksheet window. You either add different effects to our trendline or can increase the line's thickness or change the Trendline's colour or width.
   
3. For instance, in our case, we have changed the dotted line to solid, increased the thickness of the line to 2.5 and converted its color to red.
   

Display the trendline equation

Sometimes adding the trendline is not enough as you want to show the equation behind the line within the same chart.

"Trendline equation is a mathematically formula that is computed to mathematically defines the line that best fits the data points. The resulted equations are different for different trendline types".

In this equation, we will compute the R-squared value that describes how well the trendline corresponds to the data?the closer the R² value to 1, the better the fit.

If you want to insert a trendline equation for your chart, do the following:

1. Double-click the trendline, which will open the format trendline window on the right side of the worksheet window.
2. Choose the third icon i.e., the Trendline Options tab and put tick the check of the below boxes:
   • Display Equation on chart
   • Display R-squared value on chart
   
   
3. As a result, Excel will automatically put the trendline formula in your chart. In my case, it put the equation in the bottom so I dragged it at the top of my graph.
   

In the above example, the R² value is coming to be 0.15, indicating that the trendline fits about 15% of our data.

Present more digits in the trendline equation

Sometimes the trendline equation return inaccurate output. It mostly happens if you provide the x values to it manually, most likely it's because of rounding. By default, the numbers supplied in the equation are rounded to 2 - 4 decimal places. However, if you take more digits this problem will be resolved.

Below given are steps to add more digits to your trendline equation:

1. Select the trendline formula that you have applied.
2. The following label pane will be displayed, click on the Label Options tab.
3. From the Category drop-down window, select the Number option.
   
4. In the Decimal places box, enter the number of decimal places you want to edit and press Enter to update the trendline equation.

NOTE: The decimal box can take only upto 30 decimal places.

Delete a trendline from Excel

So far we learnt how to add, format, edit a Trendline. Now what if we want to delete it? To quickly delete a trendline from your chart, follow the below given steps:

Right-click on the Trendline, a window dialog box will appear from there click on the Delete option:

Or

Select the chart, go to the Chart Elements button and unselect the checkbox for Trendline option

Both the options will immediately delete the applied trendline from your Excel chart.

That's it! We covered everything regarding a trendline in Excel from creating to formatting to creating multiple lines, and finally covering the steps to delete the lines as well if not required. Go ahead and give this Excel feature a try.