This example shows how to run regression in Excel by using a special tool included with the
Analysis ToolPak add-in.
Enable the Analysis ToolPak add-in
Analysis ToolPak is available in all versions of Excel 2019 to 2003 but is not enabled by default.
So, you need to turn it on manually. Here's how:
1. In your Excel, click File > Options
2. In the Excel Options dialog box, select Add-ins on the left sidebar, make sure Excel
Add-ins is selected in the Manage box, and click Go
3. In the Add-ins dialog box, tick off Analysis Toolpak, and click OK
4. This will add the Data Analysis tools to the Data tab of your Excel ribbon
Run regression analysis
In this example, we are going to do a simple linear regression in Excel. What we have is a list of
average monthly rainfall for the last 24 months in column B, which is our independent variable
(predictor), and the number of umbrellas sold in column C, which is the dependent variable. Of
course, there are many other factors that can affect sales, but for now we focus only on these two
variables:
With Analysis Toolpak added enabled, carry out these steps to perform regression analysis in
Excel:
1. On the Data tab, in the Analysis group, click the Data Analysis button
2. Select Regression and click OK
3. In the Regression dialog box, configure the following settings:
- Select the Input Y Range, which is your dependent variable. In our case, it's
umbrella sales (C1:C25).
- Select the Input X Range, i.e. your independent variable. In this example, it's the
average monthly rainfall (B1:B25).
If you are building a multiple regression model, select two or more adjacent columns
with different independent variables.
-Check the Labels box if there are headers at the top of your X and Y ranges.
-Choose your preferred Output option, a new worksheet in our case
-Optionally, select the Residuals checkbox to get the difference between the
predicted and actual values.
4. Click OK and observe the regression analysis output created by Excel.
