Often you may want to find the equation that best fits some curve for a dataset in Excel.

Fortunately this is fairly easy to do using the **Trendline** function in Excel.

This tutorial provides a step-by-step example of how to fit an equation to a curve in Excel.

**Step 1: Create the Data**

First, let’s create a fake dataset to work with:

**Step 2: Create a Scatterplot**

Next, let’s create a scatterplot to visualize the dataset.

First, highlight cells **A2:B16** as follows:

Next, click the **Insert** tab along the top ribbon, and then click the first plot option under **Scatter**:

This produces the following scatterplot:

**Step 3: Add a Trendline**

Next, click anywhere on the scatterplot. Then click the **+** sign in the top right corner. In the dropdown menu, click the arrow next to **Trendline** and then click **More Options**:

In the window that appears to the right, click the button next to **Polynomial**. Then check the boxes next to **Display Equation on chart** and **Display R-squared value on chart**.

This produces the following curve on the scatterplot:

The equation of the curve is as follows:

y = 0.3302x^{2} – 3.6682x + 21.653

The R-squared tells us the percentage of the variation in the response variable that can be explained by the predictor variables. The R-squared for this particular curve is **0.5874**.

**Step 4: Choose the Best Trendline**

We can also increase the order of the Polynomial that we use to see if a more flexible curve does a better job of fitting the dataset.

For example, we could choose to set the Polynomial Order to be 4:

This results in the following curve:

The equation of the curve is as follows:

y = -0.0192x^{4} + 0.7081x^{3} – 8.3649x^{2} + 35.823x – 26.516

The R-squared for this particular curve is **0.9707**.

This R-squared is considerably higher than that of the previous curve, which indicates that it fits the dataset much more closely.

We can also use this equation of the curve to predict the value of the response variable based on the predictor variable. For example if *x* = 4 then we would predict that *y* = **23.34**:

y = -0.0192(4)^{4} + 0.7081(4)^{3} – 8.3649(4)^{2} + 35.823(4) – 26.516 = 23.34

You can find more Excel tutorials on this page.

Excel may show you a default curve to fit these point data, however, no one can answer me the exact numerical method was used in Excel.

I have tried to check by the cubic spline interpolation and Bessel interpolation, but it seems Excel does not use these methods.

Let’s dig it out!

thanks

Thanks Zach. I needed a quickie method to drop a curve, fitted to a simple data set, into a presentation. This got me going quickly and worked perfectly.