How To Create A Trend Line Graph In Excel

Trendline in Excel

Excel Trendline(Table of Contents)

Trendline in Excel
Types of Trendline in Excel
How to Create a Trendline in Excel?

Trendline in Excel

The trendline in Excel is the part of all the Charts available in the Charts section under the Insert menu tab, which is used to see the trend in the plotted data over any chart. This helps us to see whether there is an increase or decrease in data values. This is also helpful in seeing at which point the data is going up or down. It can be used in any type of chart (Except Pie, Donut). To apply trendline, first create a chart using the option available in the Charts section, then click right on any data on the chart and select Add Trendline. We can also insert Trendlines using the Chart Elements list, which can be accessed from the plus sign at the right top corner or any chart.

Types of Trendline in Excel

There is 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.

You can download this Trendline Excel Template here – Trendline Excel Template

Example #1

Suppose we have given the fruits name & their production number month wise.

Trendline (production number )

For creating a trendline in excel, follow the below steps:

Select the whole data, including the headings.
Go to the INSERT tab.
Click on Column Charts under the Charts section and then select 2-D Column Chart as shown in the below screenshot.

Insert Colums

It will create a Column chart on the given data as shown below.

Trendline (create a Column chart)

After formatting Legend and adding the chart title, the chart will look as below:

Trendline (formatting Legend)

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.

drop-down list

Click on Add Trendline option from the drop-down list.

Add Trendline

It will open a Format Trendline box, as shown in the below screenshot.

Format Trendline

Make sure that the Linear option is selected. Refer to the below screenshot.

Trendline (Linear)

By using the Fill & Line option, you can format the trend line.
It will create the linear trendline in your chart, as shown below.

Trendline (Chart title)

Through this trendline, we can predict the growth of the business.

growth of the business

Example #2

We have given the sales amount period-wise.

sales amount period

For creating a trendline in excel, follow the below steps:

Select the whole data, including Column headings.
Go to the Insert tab and choose the Line chart and click on OK. Refer to the below screenshot.

Insert and click ok

The chart is shown below:

Trendline salesamount

Now do a right-click on the line of a line chart and choose the option Add Trendline. Refer to the below screenshot.

option Add Trendline

It will open the Format Trendline window.
Make sure that the Linear option is selected.

Format Trendline window

Now close the window, and it will insert the trendline in your chart. Refer to the below screenshot.

in your chat

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.