While working in Microsoft Excel Sometimes we might need to create equation from the data points plotted in a chart. Sometimes it might feel difficult. But from now on it won’t be a problem to make the equation from the data points. Today in this article, I am sharing with you how to create equation from data points in Excel.

## How to Create Equation from Data Points in Excel: 2 Quick Methods

In the following, I have explained 2 simple and quick methods to create equations from data points in Excel.

Suppose we have a dataset of a manufacturing company’s **Total Production** and **Target Production **month-wise. Now we will make a chart and create equation from data points inside the chart.

### 1. Use Trendline Option to Create Equation from Data Points

One of the easiest ways to create equation from data points is to draw a trendline and then use it to form the equation. Follow the steps below-

**Step 1:**

- First, select numeric data from the list and draw a “
**Scatter Chart**” from the “**Insert**” option.

- Secondly, you will get the scatter chart with data points inside the worksheet.

- Hence, select any data point from the graph and right right-click the mouse button, and choose “
**Add Trendline**” to draw the trendline.

- Thereafter, choose “
**Linear**” to draw a linear trendline and checkmark the “**Display Equation on chart**” to get the equation.

- Therefore, you will get the equation just like the following screenshot inside the graph. Now we will use the equation to calculate the data points.

**Step 2:**

- Starting with, select a cell (
**E5**) and apply the following formula which we got from the chart-

`=0.9026*C5 + 71.303`

- Gently, press
**Enter**and drag the “**fill handle**” down to fill all the cells. - Finally, we have successfully created equation from data points in Excel.

**Read More: **How to Show Equation in Excel Graph

### 2. Utilize Solver Add-in to Create Equation from Data Points

If you want you can also utilize the **Solver Add-in** to create equations from data points and minimize the gap. Follow the steps below-

**Step 1:**

- Just like the previous method, draw a scatter chart with the numeric values from the list.

- Here, this time we will create a “
**Polynomial Trendline**” for a better understanding of the data. To do that, inside the chart click the right button and choose “**Format****trendline**” from the options.

- Then, select “
**Polynomial**” and checkmark the “**Display Equation on chart**”.

- Thus we will get the equation from the data points plotted in the chart. Now without wasting time we will apply the equation for the data points and minimize the difference with the “
**Solver**” feature.

**Step 2:**

- First, choose a cell (
**E5**) and put the formula down-

`=-0.0017*C5^2 + 5.7099*C5 - 3372.4`

- Simply, press
**Enter**and drag down the “**fill****handle**” to fill. - Thereafter we have the output in our hands. Now we will calculate the difference between the variable and data point value.

- In the same fashion, choose a cell (
**F5**) and apply the formula to determine the difference-

`=D5-E5`

- Similarly, hit the
**Enter**button and drag down the “**fill****handle**” to fill with precious output. - From the result, we can see the difference is a bit much. To minimize it follow the instructions below-

**Step 3:**

- Above all, we will calculate the total difference with the
**SUMSQ**function. To do that select a cell (**F14**) and write the formula down-

`=SUMSQ(F5:F12)`

Where,

- The
**SUMSQ**function returns the sum of the squares value provided in the string.

- Hit the
**Enter**button and the result is in your hands.

- In order to minimize the difference go to the “
**Solver**” option from the “**Data**” option.

- In the new window, select the total difference output cell in the “
**Set****Objective**” cell. Select the changing variables and remove the check mark from the “**Make Unconstrained Variables Non-Negative**” option. - Simply, press “
**Solve**” to get the final output.

- In conclusion, we have completely minimized the differences thus the chart has plotted a trendline over all the data points.

**Read More: **How to Make a Polynomial Trendline in Excel

## Things to Remember

- If you are not getting the “
**Solver**” option in your “**Data**” feature just follow the instructions to get it. Go to**File > Options > Add-ins**. Then checkmark the “**Solver Add-ins**” option and get it inside the “**Data**” ribbon.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## Conclusion

In this article, I have tried to cover all the methods to create equation from data points in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. Stay tuned and keep learning.

## Related Articles

- How to Find the Equation of a Line in Excel
- How to Find the Equation of a Trendline in Excel
- How to Get Y Equation on Excel Graph
- How to Use Trendline Equation in Excel
- How to Find Slope of Trendline in Excel
- How to Find Intersection of Two Trend Lines in Excel
- How to Find Slope of Polynomial Trendline in Excel

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

You just modified the original data set to match the trendline that excel has created in first place. What is the use of this?

It will be useful only when you are able to fine tune the equation excel gave to suit your data points and not change the data points to match the excel equation.

Dear Yogiraj,

Thank you for your comment.

You are absolutely right about your opinion regarding its use.

By creating equation from data points you can also see the difference between the plotted and actual data points.

Thanks