How to Perform Polynomial Regression in Excel


Regression analysis is used to quantify the relationship between one or more explanatory variables and a response variable.

The most common type of regression analysis is simple linear regression, which is used when an explanatory variable and a response variable have a linear relationship.

Linear relationship between two variables

However, sometimes the relationship between an explanatory variable and a response variable is nonlinear. 

Quadratic relationship example

Cubic relationship example

In these cases it makes sense to use polynomial regression, which can account for the nonlinear relationship between the variables.

This tutorial explains how to perform polynomial regression in Excel.

Example: Polynomial Regression in Excel

Suppose we have the following dataset in Excel:

Example dataset in Excel

Use the following steps to fit a polynomial regression equation to this dataset:

Step 1: Create a scatterplot.

First, we need to create a scatterplot. Go to the Charts group in the Insert tab and click the first chart type in Scatter:

Scatterplot in Excel

A scatterplot will automatically appear:

Cubic relationship in scatterplot in Excel example

Step 2: Add a trendline.

Next, we need to add a trendline to the scatterplot. To do so, click on any of the individual points in the scatterplot. Then, right click and select Add Trendline…

Trendline for polynomial regression in Excel

A new window will pop up with the option to specify a trendline. Choose Polynomial and choose the number you’d like to use for Order. We will use 3. Then, check the box near the bottom that says Display Equation on chart

Polynomial trendline in Excel

A trendline with a polynomial regression equation will automatically appear on the scatterplot:

Polynomial regression in Excel

Step 3: Interpret the regression equation.

For this particular example, our fitted polynomial regression equation is:

y = -0.1265x3 + 2.6482x2 – 14.238x + 37.213

This equation can be used to find the expected value for the response variable based on a given value for the explanatory variable. For example, suppose x = 4. The expected value for the response variable, y, would be:

y = -0.1265(4)3 + 2.6482(4)2 – 14.238(4) + 37.213 = 14.5362.

4 Replies to “How to Perform Polynomial Regression in Excel”

  1. hello Mr. Zach,
    I am Antonio Dellisanti from Florence -Italy-
    Thanks alot for your “How to Perform Polynomial Regression in Excel” I found very very interesting and I learnt how to use Excel for polynomial regression.
    But… how and where do I have to put “the given value” as x= 4 in Excel to find the expected value in this case = 14.5362.?
    Thx so much
    Sincerely
    Antonio

  2. Hi, is there a way to retrieve the coefficients from the trendline function on the chart?

    E.g
    I want to have the values below in four cells to use in later calculations
    -0.1265
    2.6482
    – 14.238
    37.213

  3. Hello. Is the Polynomial Regression with Excel spreadsheet that you describe in the article “How To Perform Polynomial Regressions In Excel” free of charge? I not could you provide me with the price for it?

Leave a Reply

Your email address will not be published. Required fields are marked *