Beta in a linear regression is a standardised coefficient indicating the magnitude of the correlation between a certain independent variable and the dependent variable. The use of these standardised values allows you to directly compare the effects on the dependent variable of variables measured on different scales. Though a great tool for performing simple statistical calculations, Microsoft Excel does not automatically provide a way of finding these beta coefficients. To get around this, you need to install the "Analysis ToolPak" add-in (available in Excel) and perform a linear regression on your data.
Click on the Office button and select "Open," then select the data set with which you wish to work. Click on the "Open" button to open this file.
Open the Office menu again, then click on the "Excel Options" button at the bottom-right of the menu. In the window that appears, click on the "Add-Ins" tab in the sidebar on the left.
Select "Analysis ToolPak" from the list that appears. In the drop-down menu beside "Manage" at the bottom of the screen, select "Excel Add-Ins" and click on the "Go" button. Ensure that the radio box beside "Analysis ToolPak" has a check mark in it, then click "OK."
There will now be a "Data Analysis" option at the far right under the "Data" tab in your spreadsheet. Click on "Data Analysis" and select "Regression" from the list in the window that appears. Click on the "OK" button to proceed.
Click anywhere in the text box beside "Input Y Range," then click and drag to select the range for your dependent variable. Do the same for the independent variable(s) for which you would like beta coefficients in the "Input X Range" text box. Select any other information that you would like in the output (e.g., labels, residual plots), then click "OK" to proceed.
Excel will automatically produce a new worksheet with the output from your regression. There should be three tables, the last of which provides the beta values (along with other information) for your independent variable(s). These values are found under the "Coefficients" column in the row of your independent variable of interest.
Things you need
- Microsoft Excel 2007 or later
- Analysis ToolPak
- Data Set