# How to Use Trendline Equation in Excel (8 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

An Excel trendline represents the primary pattern or direction of the data on an Excel chart. The trendline can be a straight or curved line depending on the trendline type. A trendline equation is an equation for the trendline on an Excel curve. The main focus of this article is to explain how to use trendline equation in Excel.

To explain this article I have taken the following dataset that contains Years and Sales.

I have also taken a column chart of the dataset to show the use of the trendline equation in Excel.

## 1. Adding Trendline Equation in Excel

In this first example, I will explain how to add a trendline equation in Excel.

Letâ€™s see the steps.

Steps:

• Firstly, select the chart in which you want to add the trendline.
• Secondly, go to Chart Elements.
• Thirdly, select Trendline.

Now, you will see a trendline has been added to your chart.

After that, you can change the color of the trendline to make it more visible.

• Firstly, Right-click on the trendline.
• Secondly, select Format Trendline.

Here, you will see that the Format Timeline option will appear on the right side of the screen.

• Firstly, go to Fill & Line.
• Secondly, select Color.

Now, you will see a list of colors will appear.

• Next, select the color you want. Here, I selected Red.

After that, you will see that you have got your desired trendline.

Here, you can also change the trendline type.

• Firstly, Right-click on the trendline.
• Secondly, select Format Trendline.

Here, you will see that the Format Timeline option will appear on the right side of the screen.

• Firstly, go to the Trendline Options.
• Secondly, select the trendline type you want. Here, I kept it Linear which was set by default.Â  But you can select any other option you want.

Now, I will add the trendline equation.

• Firstly, Right-click on the trendline.
• Secondly, select Format Trendline.

Here, you will see that the Format Timeline option will appear on the right side of the screen.

• Firstly, go to the Trendline Options.
• Secondly, select Display Equation on chart.

Finally, you will see the trendline equation on the chart.

## Â 2. Using Trendline Equation in Excel to Forecast Data

To explain this example, I have taken a dataset that contains the Temperature (K) and Volume (Liter) of a gas. I also have a column chart of the dataset with a Linear Trendline and trendline equation. Here, I will show you how to use trendline equation in Excel to forecast data.

Letâ€™s see the steps.

Steps:

• Firstly, select the cell where you want to forecast the data. Here, I selected cell C10.
• Secondly, in cell C10 write the following formula.
`=0.114*B10-2.8`

Here, In this formula, I used the trendline equation from the chart. I took B10 as X. Now, the formula will return the Y value.

• After that, press ENTER to get the Y

Now, you will see that you have got your forecasted data using the trendline equation in Excel.

## 3. Using Trendline Equation in Excel to Get Values for Any Given X

For this example, I have taken a dataset that contains the Temperature (K) and Volume (Liter) of a gas. I also have a column chart of the dataset with a Linear Trendline and trendline equation. Here, I will explain how to use trendline equation in Excel to get values for any given X.

Letâ€™s see the steps.

Steps:

• Firstly, select the cell where you want to get the Y value for a given X. Here, I selected cell C12.
• Secondly, in cell C12 write the following formula.
`=0.114*B12-0.8`

Here, In this formula, I used the trendline equation from the chart. I took B12 as X. Now, the formula will return the Y value.

• After that, press ENTER to get the Y

• Now, drag the Fill Handle to copy the formula.

Here, you will see that you have got your Y values for any given X using the trendline equation in Excel.

Read More: How to Find Unknown Value on Excel Graph

## 4. Inserting Linear Trendline Equation to Find Slope and Intercept

To explain this example, I have taken a dataset that contains Years and Sales. I also have a column chart of the dataset with a linear trendline and trendline equation. Here, I will use the linear trendline equation to find the Slope and Intercept. I will also use functions to find the Slope and Intercept for the dataset to see if it matches the trendline equation.

In the following Image, you can see that from the linear trendline equation I have got the Slope and Intercept. Now, I will use functions to find the Slope and Intercept for the dataset.

Letâ€™s see the steps.

Steps:

• Firstly, select the cell where you want to calculate the Slope. Here, I selected cell G5.
• Secondly, in cell G5 write the following formula.
`=SLOPE(C5:C9,B5:B9)`

Here, in the SLOPEÂ  function, I selected C5:C9 as known_ys and B5:B9 as known_xs. This function will return the value of the Slope for the dataset.

• Finally, press ENTER to get the value of the Slope.

Now, I will calculate the Intercept.

• Firstly, select the cell where you want to calculate the Intercept. Here, I selected cell G6.
• Secondly, in cell G6 write the following formula.
`=INTERCEPT(C5:C9,B5:B9)`

Here, in the INTERCEPT function, I selected C5:C9 as known_ys and B5:B9 as known_xs. This function will return the value of the Intercept for the dataset.

• Finally, press ENTER to get the value of the Intercept.

Now, you will see that the values of the Slope and Intercept from the equation match the values that we got using functions. This means the values from the trendline equation are correct.

Read More: How to Find Slope of Trendline in Excel

## 5. Using Exponential Trendline Equation in Excel to Find the Coefficients

To explain this example, I have taken a dataset that contains Years and Sales. I also have a column chart of the dataset with an exponential trendline and trendline equation. Here, I will use the exponential trendline equation to find Coefficient a and Coefficient b. I will also use functions to find Coefficient a and Coefficient b for the dataset to see if they match the trendline equation.

In the following Image, you can see that from the exponential trendline equation I have got Coefficient a and Coefficient b. Now, I will use functions to find Coefficient a and Coefficient b for the dataset.

Letâ€™s see the steps.

Steps:

• Firstly, select the cell where you want to calculate the Coefficient a. Here, I selected cell G5.
• Secondly, in cell G5 write the following formula.
`=EXP(INDEX(LINEST(LN(C5:C9),B5:B9),1,2))`

Formula Breakdown

• Here, in the EXP function, I used the INDEX function as a number that will return the value from a range.
• Now, in the INDEX function, I used the LINEST function as array 1 as row_num, and 2 as column_num.
• Next, In the LINEST function, I selected LN(C5:C9) as known_ysÂ the LN function will return the natural logarithm of the given range, and B5:B9 as known_xs.
• Finally, the EXP function will return the result of the constant e raised to the power of the number which will be our coefficient a.
• Thirdly, press ENTER to get the coefficient a.

Now, I will calculate Coefficient b.

• Firstly, select the cell where you want to calculate the Coefficient b. Here, I selected cell G6.
• Secondly, in cell G6 write the following formula.
`=INDEX(LINEST(LN(C5:C9),B5:B9),1)`

Formula Breakdown

• Here, in the INDEX function, I used a LINEST function as array and 1 as row_num.
• Next, In the LINEST function, I selected LN(C5:C9) as known_ys which will return the natural logarithm of the given range, and B5:B9 as known_xs.
• Finally, the INDEX function will return the value from the table which will be Coefficient b.
• Thirdly, press ENTER to get the Coefficient b.

Now, you will see that the values of Coefficient a and Coefficient b from the equation match the values that we got using functions. This means the values from the trendline equation are correct.

## 6. Applying Logarithmic Trendline Equation in Excel

To explain this example, I have taken a dataset that contains Years and Sales. I also have a column chart of the dataset with a logarithmic trendline and trendline equation. Here, I will use the logarithmic trendline equation to find Constant a and Constant b. I will also use functions to find Constant a and Constant b for the dataset to see if they match the trendline equation.

In the following Image, you can see that from the logarithmic trendline equation I have got Constant a and Constant b. Now, I will use functions to find Constant a and Constant b for the dataset.

Letâ€™s see the steps.

Steps:

• Firstly, select the cell where you want to calculate the Constant a. Here, I selected cell G5.
• Secondly, in cell G5 write the following formula.
`=INDEX(LINEST(C5:C9,LN(B5:B9)),1)`

Formula Breakdown

• Here, in the INDEX function, I used a LINEST function as array and 1 as row_num.
• Next, In the LINEST function, I selected C5:C9 as known_ys, and LN(B5:B9) as known_xs which will return the natural logarithm of the given range.
• Finally, the INDEX function will return the value from the table which will be Constant a.
• Thirdly, press ENTER to get the Constant a.

Now, I will calculate Constant b.

• Firstly, select the cell where you want to calculate the Constant b. Here, I selected cell G6.
• Secondly, in cell G6 write the following formula.
`=INDEX(LINEST(C5:C9,LN(B5:B9)),1,2)`

Formula Breakdown

• Here, in the INDEX function, I used a LINEST function as array and 1 as row_num and 2 as column_num.
• Next, In the LINEST function, I selected C5:C9 as known_ys, and LN(B5:B9) as known_xs which will return the natural logarithm of the given range.
• Finally, the INDEX function will return the value from the table which will be Constant b.
• Thirdly, press ENTER to get the Constant b.

Now, you will see that the values of Constant a and Constant b from the equation match the values that we got using functions. This means the values from the trendline equation are correct.

## 7. Using Polynomial Trendline Equation in Excel

To explain this example, I have taken a dataset that contains Years and Sales. I also have a column chart of the dataset with a Polynomial trendline and trendline equation. Here, I will use the Polynomial trendline equation to find Coefficient b1, Coefficient b2, and Constant a. I will also use functions to find Coefficient b1, Coefficient b2, and Constant a for the dataset to see if they match the trendline equation.

In the following Image, you can see that from the Polynomial trendline equation I have got Coefficient b1, Coefficient b2, and Constant a. Now, I will use functions to find Coefficient b1, Coefficient b2, and Constant a for the dataset.

Letâ€™s see the steps.

Steps:

• Firstly, select the cell where you want to calculate the Coefficient b1. Here, I selected cell G5.
• Secondly, in cell G5 write the following formula.
`=INDEX(LINEST(C5:C9,B5:B9^{1,2}),1)`

Formula Breakdown

• Here, in the INDEX function, I used a LINEST function as array and 1 as row_num.
• Next, In the LINEST function, I selected C5:C9 as known_ys, and B5:B9^{1,2} as known_xs which will raise the range B5:B9 to the power of {1,2}.
• Finally, the INDEX function will return the value from the table which will be Coefficient b1.
• Thirdly, press ENTER to get Coefficient b1.

Now, I will calculate Coefficient b2.

• Firstly, select the cell where you want to calculate the Coefficient b2. Here, I selected cell G6.
• Secondly, in cell G6 write the following formula.
`=INDEX(LINEST(C5:C9,B5:B9^{1,2}),1,2)`

Formula Breakdown

• Here, in the INDEX function, I used a LINEST function as array, 1 as row_num and, 2 as column_num.
• Next, In the LINEST function, I selected C5:C9 as known_ys, and B5:B9^{1,2} as known_xs which will raise the range B5:B9 to the power of {1,2}.
• Finally, the INDEX function will return the value from the table which will be Coefficient b2.
• Thirdly, press ENTER to get Coefficient b2.

Here, I will calculate Constant a.

• Firstly, select the cell where you want to calculate the Constant a. Here, I selected cell G7.
• Secondly, in cell G7 write the following formula.
`=INDEX(LINEST(C5:C9,B5:B9^{1,2}),1,3)`

Formula Breakdown

• Here, in the INDEX function, I used a LINEST function as array, 1 as row_num and, 3 as column_num.
• Next, In the LINEST function, I selected C5:C9 as known_ys, and B5:B9^{1,2} as known_xs which will raise the range B5:B9 to the power of {1,2}.
• Finally, the INDEX function will return the value from the table which will be Constant a.
• Thirdly, press ENTER to get Constant a.

Now, you will see that the values of Coefficient b1, Coefficient b2, and Constant a from the equation match the values that we got using functions. This means the values from the trendline equation are correct.

## 8. Using Power Trendline Equation in Excel

To explain this example, I have taken a dataset that contains Years and Sales. I also have a column chart of the dataset with a power trendline and trendline equation. Here, I will use the power trendline equation to find Constant a and Constant b. I will also use functions to find Constant a and Constant b for the dataset to see if they match the trendline equation.

In the following Image, you can see that from the power trendline equation I have got Constant a and Constant b. Now, I will use functions to find Constant a and Constant b for the dataset.

Letâ€™s see the steps.

Steps:

• Firstly, select the cell where you want to calculate the Constant a. Here, I selected cell G5.
• Secondly, in cell G5 write the following formula.
`=EXP(INDEX(LINEST(LN(C5:C9),LN(B5:B9), ,),1,2))`

Formula Breakdown

• Here, in the EXP function, I used an INDEX function as a number that will return the value from a range.
• Now, in the INDEX function, I used a LINEST function as an array and 1 as row_num, and 2 as column_num.
• Next, In the LINEST function, I selected LN(C5:C9) as known_ys which will return the natural logarithm of the given range, and LN(B5:B9) as known_xs which will return the natural logarithm of the given range, and left const and stats
• Finally, the EXP function will return the result of the constant e raised to the power of the number which will be our Constant a.
• Thirdly, press ENTER to get the Constant a.

Now, I will calculate Constant b.

• Firstly, select the cell where you want to calculate the Constant a. Here, I selected cell G6.
• Secondly, in cell G6 write the following formula.
`=INDEX(LINEST(LN(C5:C9),LN(B5:B9),,),1)`

Formula Breakdown

• Here, in the INDEX function, I used a LINEST function as an array and 1 as row_num.
• Next, In the LINEST function, I selected LN(C5:C9) as known_ys which will return the natural logarithm of the given range, and LN(B5:B9) as known_xs which will also return the natural logarithm of the given range, and left const and stats
• Finally, the INDEX function will return the value from the table which will be Constant b.
• Thirdly, press ENTER to get the Constant b.

Now, you will see that the values of Constant a and Constant b from the equation match the values that we got using functions. This means the values from the trendline equation are correct.

## Practice Section

Here, I have provided a practice sheet for you to practice how to use trendline equation in Excel.

## Conclusion

To conclude, in this article, I tried to show different examples of how to use trendline equation in Excel. If you have any questions feel free to let me know in the comment section below.

## Related Articles

<< Go Back To Trendline Equation Excel | Trendline in ExcelÂ |Â Excel ChartsÂ |Â Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF