Excel Trendline (Table of Contents)
Trendline in Excel
To know the general trend of data or in other words to know the growth of the business is going on which direction, we use trend lines in Excel. Trend lines can be used with the various type of charts like Bar charts, Column Charts, Line charts etc.
Trend line looks like a line chart although it works differently with it.
Types of Trendline in Excel
There are various type of trendlines in excel:
- Exponential trendline
- Linear trendline
- Logarithmic trendline
- Polynomial trendline
- Power trendline
- Moving Average trendline
Now we will explain one by one the types of Trendline in excel.
Exponential:
This trendline is mostly used where the data rise or falls constantly. Avoid this trendline, if the data contains zero or negative values.
Linear:
This trendline is useful for creating a straight line to show the increasing or decreasing of data values at a straight line.
Logarithmic:
This trendline is useful where the data suddenly increases or decreases and then becomes stable at some point. In this trendline, you can include the negative values in the dataset.
Polynomial:
This trendline is useful where you see gain or loss in the business. The degree of this trendline shows the number of fluctuations in the data.
Power:
This trendline is useful where the dataset is used to compare results that increase at a fixed rate. Avoid this trendline if your dataset contains Zero or negative values.
Moving Average:
This trendline shows the pattern clearly in the dataset which smoothes the line. This trendline is mostly used in Forex Market.
How to Create a Trendline in Excel?
To create a Trendline in Excel is very simple and easy. Let’s understand the creation of Trendline in Excel with some examples.
Example #1
Suppose we have given the fruits name & their production number month wise.
For creating trendline in excel, follow below steps:
- Select the whole data including the headings.
- Go to INSERT tab.
- Click on Column Charts under the Charts section and then select 2-D Column Chart as shown in below screenshot.
- It will create a Column chart on the given data as shown below.
- After formatting Legend and adding chart title the chart will look as below:
- Click on Chart Area and do a right click. It will open a drop-down list of some options as shown in the below screenshot.
- Click on Add Trendline option from the drop-down list.
- It will open a Format Trendline box as shown in below screenshot.
- Make sure that the Linear option is selected. Refer below screenshot.
- By using the Fill & Line option, you can format the trend line.
- It will create the linear trendline in your chart as shown below.
- Through this trendline, we can predict the growth of the business.
Example #2
We have given the sales amount period wise.
For creating trendline in excel, follow below steps:
- Select the whole data including Column headings.
- Go to the Insert tab and choose the Line chart and click on OK. Refer below screenshot.
- The chart is shown below:
- Now do a right click on the line of a line chart and choose the option Add Trendline. Refer below screenshot.
- It will open the Format Trendline window.
- Make sure that the Linear option is selected.
- Now close the window and it will insert the trendline in your chart. Refer below screenshot.
Now from the trendline, you can predict the flow of business growth.
Things to Remember About the Trendline in Excel
- To know which is the most suitable trendline for your dataset is, Check for the R-squared value.
- When R-value is at or near 1, it’s most reliable.
- When you create a trendline for your chart, it automatically calculates R squared value.
Recommended Articles
This has been a guide to Trendline in Excel. Here we discuss its types and how to create a trendline in Excel along with excel examples and downloadable excel template. You may also look at these useful charts in excel –
