Microsoft Excel has a few statistical functions that can help you to do linear regression
analysis such as LINEST, SLOPE, INTERCPET, and CORREL
The LINEST function uses the least squares regression method to calculate a straight line
that best explains the relationship between your variables and returns an array describing
that line. You can find the detailed explanation of the function's syntax in this tutorial.
For now, let's just make a formula for our sample dataset:
=LINEST(C2:C25, B2:B25)
Because the LINEST function returns an array of values, you must enter it as an array formula. Select two adjacent cells in the same row, E2:F2 in our case, type the formula,
and press Ctrl + Shift + Enter to complete it.
The formula returns the b coefficient (E1) and the a constant (F1) for the already familiar
linear regression equation:
y = bx + a
If you avoid using array formulas in your worksheets, you can calculate a and b
individually with regular formulas:
Get the Y-intercept (a):
=INTERCEPT(C2:C25, B2:B25)
Get the slope (b):
=SLOPE(C2:C25, B2:B25)
Additionally, you can find the correlation coefficient (Multiple R in the regression
analysis summary output) that indicates how strongly the two variables are related to
each other:
=CORREL(B2:B25,C2:C25)
The following screenshot shows all these Excel regression formulas in action:
Tip. If you'd like to get additional statistics for your regression analysis, use the LINEST
function with the stats parameter set to TRUE as shown in this example.
That's how you do linear regression in Excel. That said, please keep in mind that
Microsoft Excel is not a statistical program. If you need to perform regression analysis at
the professional level, you may want to use targeted software such as XLSTAT, RegressIt, etc
