Linear regressions help you identify the strength of relationships of at least two variables. But rather than doing these calculations manually, you can compute them in sections. Let’s perform a **regression analysis in Microsoft Excel.**

## How to Perform Regression in Microsoft Excel

Consider these two sets of data: on the left are test scores, on the right are hours spent studying for the test. We want to determine the relationship between scores and study.

On the **Data** tab on Excel’s ribbon, click **Data Analysis.** You might have to enable this on Excel’s **Add-Ins **menu. From the list of** Analysis Tools, **click **Regression**, then **OK**.

On the **Regression** window, Excel asks for several inputs. **Input Y Range** is the data in Column A. This is your dependent variable. Click and drag inside the box, selecting your Column A data.

**Input X Range** is your Column B data (also known as your independent variable).

Check the **Labels **box, then choose an empty cell in your worksheet in the **Output Range** box. (Hint: Excel will need several empty cells, so ensure you’re choosing an empty section of your worksheet).

Click **OK.** Excel will return a **Summary Report** with several statistical values listed. The one of primary interest for regression is **R Square.** The closer it is to 1, the stronger the relationship. Here, **R Square **equals **0.961379291.**

This symbolizes the geometric regression line; in this case, that approximately 96.14% of scores are explained by study hours. If **R Square** was very low, it would indicate a less meaningful relationship between the two variables.

## How to Identify a Regression Line Formula in Excel

The **Summary Report** has also provided your regression line formula. You’ll find the numerical inputs listed in the **Coefficients** column.

regression-formula-in-excel.jpg

Thus, your regression formula here is:

**Test Score=49.392 + 5.017 * Study Hours**

Thus, if you study for 3 hours, and perform the formula as follows:

**Test Score = 49.392 + 5.017 * 3**

Your expected test score, according to the regression equation, is **64.443.**

As you can see, Excel makes regression analysis easy, thanks to powerful data tools.