How can I perform regression analysis in Excel?



I used to get problems related to linear regression like find the relation between number of vehicles required to service customers over the year. The idea is to find a regression curve between the two so we can see if we have the right number of vehicles at the right time in a given year.

I used to perform this kind of problem using SAS but due to limited license of SAS, some of the team members are not able to perform this. Can you guide me the way to perform this in excel?



You can perform regression analysis in two ways:

  1. Using Data analysis tool
  2. Using Scatter Plot

Let’s look at these two methods in detail.

Data Analysis Tool: First check data analysis tool pack is installed or not. You can check it by activating data ribbon and at right hand side Data Analysis tool is available or not. If it is not then follow the below process:

  1. Go to Excel Options and Click on Add-Ins on the left side of the page.

  2. Find Analysis tool pack. If it’s on your list of active add-ins, you’re set. If it’s on your list of inactive add-ins, look at the bottom of the window for the drop-down list next to Manage, make sure Add-Ins is selected, and hit Go.

  3. In the next window that pops up,make sure Analysis tool pack is checked and hit OK to activate. Allow it to install if necessary.

Now to perform linear regression, go to the Tools Menu and select Data Analysis. From the Data Analysis window select Regression. That will open a wizard that will look like the picture below:

Now in this window, we can provide Input Y and X Range box and provide ranges of numbers, you want to analyze. After this you can select residuals, output options as you required.

In output sheet, you can see statistical measures to analyse the model fit.

Scatter Plot:

  1. Plot Scatter graph for both variables and enable trend line.
  2. Enable equation and R-square for trend line.

Hope this helps!