Nazmul Hossain Shovon
In this tutorial, I am going to show you 3 suitable ways to find the equation of a trendline in excel. A trendline equation is basically a formula of a line that represents the best trend in your data. There are many options available in Excel to add a trendline. You should try your best to choose a suitable type of trendline that best fits your dataset.
Download Practice Workbook
You can download the practice workbook from here.
3 Suitable Ways to Find the Equation of a Trendline in Excel
1. Find Equation with Single Trendline in Excel
For simple excel datasets, you can use a single trendline to fit your data and then find its equation. This type of trendline is applicable to various real-life cases. Let us see how to find the equation from a single trendline.
Steps:
- First, select the dataset with the help of the mouse.
- Next, go to the Insert tab and click on the Insert Scatter (X, Y) or Bubble Chart dropdown.
- Then, from the available options select Scatter.
- As a result, you will see a scattered chart of your selected dataset.
- Now, right-click on any of the points of the chart and select Add Trendline.
- Next, a window will open with the name Format Trendline.
- Here, from the Trendline Options select Linear.
- Then, from more options below select Display Equation on Chart.
- After doing the previous steps properly, you should see a trendline on your chart with the equation.
Read More: How to Calculate Trend Analysis in Excel (3 Easy Methods)
2. Use Double Trendline
When you have an excel dataset that has different categories of data points, then you can definitely try this method to find the equation of a trendline. For instance, you might have data points for various time ranges. In that case, you can easily pass a suitable trendline through each time period. Let me take you through the steps to make things clear.
Steps:
- To begin with, select the dataset and create a scatter plot from them as we did just a while ago.
- Here, you should see a scatter plot similar to the one I have shown below.
- Next, right-click on any part of the chart and choose Select Data.
- Now, in the new Select Data Source window, select the data series Y and click Remove.
- Then, click on the Add button.
- Consequently, a new Edit Series window will open.
- Now, in this window, set the Series name to Linear.
- Next, select cells B5 to B7 as the Series X values.
- Similarly, select cells C5 to C7 for the Series Y values field.
- After that press OK.
- Here, you will see a new data series called Linear.
- Likewise, create another dataset with the name Polynomial.
- Also, for the Series X values select cells B8 to B10, and for Series Y values select cells C8 to C10.
- After that, you will see a scatter plot of the two data series we just created.
- Now, right-click on any of the blue data points and select Add Trendline.
- Then, following the steps I showed in the first method, create a Linear trendline and display the equation.
- Again, create another trendline for the orange data points using the Polynomial option and also display the equation.
Read More: How to Add Multiple Trendlines in Excel (With Quick Steps)
3. Find Equation from Excel Column Chart Trendline
Although a trendline is not fitted to excel column charts generally, you can try adding one and find its equation. Follow the steps below to add a trendline to the column chart.
Steps:
- Firstly, select your data dataset and go to the Insert tab.
- Next, click on the Insert Column or Bar Chart dropdown and select the Clustered Column option.
- Thus, excel will generate a 2-D clustered column chart with your dataset.
- Now, right-click on any of the orange-colored columns and select Add Trendline.
- Then, in the new Format Trendline window, set Polynomial as the Trendline Options.
- Also, check the Display Equation on chart option.
- Finally, you will get a polynomial trendline through your data points and also the equation.
Read More: How to Find Slope of Polynomial Trendline in Excel (with Detailed Steps)
Conclusion
I hope that you fully grasped the techniques I demonstrated in this tutorial to find the equation of a trendline in excel. Indeed a trendline equation is a powerful tool for making predictions with your dataset. I would encourage you to extend your trendline to a future period of your interest and see what results it gives. And lastly, to learn more Excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.
