How to Use Multiple Regression in Excel
Multiple regression is an advanced analytic technique available in Excel. Prior to Excel 2007, it required installation of the Analysis Toolpak. From Excel 2007 through 2013, the Analysis Toolpak is installed by default. Multiple regression analysis lets you link multiple independent variables to a single dependent variable; changes to the predictors will alter the output on the dependent variable. Regression analysis will build a table showing the coefficient -- or relative effectiveness -- of each variable.
Enter your data, or load your data if it's already present in an Excel readable file. Make sure your data is loaded in columns, with your dependent variables in the left-most column in the data area. Each of your predictor columns should also have a label in the top cell.
Click the "Data" tab, click "Data Analysis," and then select "Regression" to pull up the Regression dialog box. If the Data Analysis option is not present on the Data tab, see the Tips section.
Click the icon next to the label "Input Y Range," and then select your first column of data -- these are your dependent variables. Be sure to include the title cell at the top of the column.
Click the icon next to the label "Input X Range," and then select all of the columns from your predictor data sets, including their title cells.
Check the box for "Labels," and then check the boxes for particular data modeling methods, like "Residuals" and "Normal probability." If you're not sure what those options do, check "Normal probability," which uses the Normal distribution for generating the data. Nothing says you can't re-generate the plots with different options checked -- data analysis is often about exploring different views of the relationships between variables.
Click the radio buttons for where you want the output data placed. To put your data into a new worksheet tab, select "New Worksheet Ply," and then enter a name in the space provided. To have the analysis appear in the same tab as the original data, select the "Output Range" text box, and then click the cell you want the data range to start at; the selected cell will be the upper left-most cell in the area. Make sure there's plenty of space to the right and below this cell for the data to be written.
Click "OK" to run the analysis.
Tips & Warnings
- If you need to load the Analysis Toolpak, click the "File" tab, select "Options," and then click the "Add-Ins" category. Select "Excel Add-Ins," and then click "Go." Check "Analysis Toolpak" in the Excel Add-Ins pane, and then click "OK." You may need to browse for the file, and may be prompted to confirm the installation.