• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

Regression in Excel

March 10, 2021 by Andrew Childress
Excel regression setup

Linear regressions help you identify the strength of relationships of at least two variables. Let’s learn how to perform a regression in Excel.

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.

Data in list for regression

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.

Regression Excel menu

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).

Excel regression setup

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.

Regression Excel output

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.

Category: Excel FormulasTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:T test in Microsoft ExcelT-Test in Excel
Next Post:Scatter Plot in ExcelCustomize Excel scatter plot

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top