Basic Regression with Excel

We'd like to examine the relationship between vehicle Weight and City MPG in the following data set. We'll use Weight as the explanatory variable, x, and City MPG as the response variable, y.

There are a couple of ways to do regression in Excel. You can start with a Chart or you can use the Data Analysis ToolPak's built-in Regression Tool.

We'll start with the Chart approach. You can see the ToolPak approach here.

Making a Scatter Plot

Figure01

Before we can make a scatter plot, we need to copy the data so that the explanatory variable is in the first column and the response variable is in the second. Copy column D from the original sheet to Column A in a new sheet. Then copy column C from the data to Column B of the same new sheet. The result should look like the following.

 

 

 

 

 

 

 

 

 

 

 

Copied Data

Now that the data is ready, select it and insert a scatter plot.

Selecting Scatter Plot

 

Next Page (see what you get...)