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.
Read More: How to Add Trendline Equation in Excel
 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
- Next, drag the Fill Handle to copy the formula.
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.
Read More: How to Find Slope of Polynomial Trendline in Excel
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.
Download Practice workbook
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
- How to Find the Equation of a Line in Excel
- How to Find the Equation of a Trendline in Excel
- How to Show Equation in Excel Graph
- How to Get Y Equation on Excel Graph
- How to Create Equation from Data Points in Excel
- How to Find Intersection of Two Trend Lines in Excel
<< Go Back To Trendline Equation Excel | Trendline in Excel | Excel Charts | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
