For a simple linear regression in Excel, use =SLOPE(known_y's, known_x's) and =INTERCEPT(known_y's, known_x's) to get the best-fit line, y = mx + b, then =RSQ(known_y's, known_x's) to see how well it fits. LINEST does all of that in one go and adds the regression statistics, but it returns an array.
Linear regression finds the straight line that best fits your data. Microsoft's LINEST page says Excel uses the least squares method to do it. In the line's equation, y = mx + b, m is the slope (how much y changes for each 1 of x) and b is the intercept (the value of y when x is 0). Once you have those two numbers, you can predict y for any x.
The four functions you need
| Function | What it answers | Microsoft example result |
|---|---|---|
| SLOPE | How much y changes for each 1 of x | 0.305556 |
| INTERCEPT | The value of y when x is 0 | 0.0483871 |
| RSQ | The share of the variance in y explained by x | 0.05795 |
| LINEST | Slope, intercept and statistics, as an array | An array formula |
Microsoft's SLOPE example, =SLOPE(A3:A9,B3:B9), returns 0.305556. Its INTERCEPT example, =INTERCEPT(A2:A6, B2:B6), returns 0.0483871. Microsoft describes the slope as the vertical distance divided by the horizontal distance between any two points on the line, and the intercept as the point where the line crosses the y-axis.
How to run a simple regression, step by step
- Put your x-values in one column and your y-values in the next.
- Enter =SLOPE(y_range, x_range) to get the slope.
- Enter =INTERCEPT(y_range, x_range) to get the intercept.
- Predict any y with slope x your x value + intercept.
- Enter =RSQ(y_range, x_range) to see how well the line fits.
- If you need standard errors or the F statistic, use LINEST with stats set to TRUE.
The catch: y comes first
Every one of these functions wants the y-values first and the x-values second. It's the opposite of how most of us think about a chart, where x comes first, and swapping them gives you a different line with no warning. Get it into your head now: known_y's, then known_x's.
CFI flags a second, rarer trap. SLOPE and LINEST use different algorithms, so they can disagree when the data is undetermined and collinear. CFI's example: if every y is 0 and every x is 1, SLOPE returns #DIV/0! while LINEST returns 0. CFI also lists the rules SLOPE needs: the x and y ranges must be the same length, or you get #N/A, and the x-values can't all be the same, or you get #DIV/0!. Text, logical values and empty cells are ignored, but zeros are included.
How to read RSQ
RSQ returns the square of the Pearson correlation coefficient. Microsoft says you can read it as the proportion of the variance in y that's explained by the variance in x. Its example, =RSQ(A3:A9, B3:B9), returns 0.05795. That means x explains only a small share of the movement in y in that data, and Microsoft's example is a weak fit.
If you'd rather see the correlation itself, CORREL(array1, array2) returns it. Microsoft's example is the link between a location's average temperature and its use of air conditioners. Because RSQ is the square of that coefficient, an RSQ of 0.05795 matches a correlation of about 0.24. That's my own square root, not a figure from Microsoft's page.
Using LINEST for the full picture
The syntax is LINEST(known_y's, known_x's, const, stats), and only the y-values are required. Microsoft explains the options:
- If you leave out known_x's, Excel assumes they're 1, 2, 3 and so on.
- Set const to FALSE to force the intercept, b, to be 0.
- Set stats to TRUE to get extra statistics: the standard errors, r squared, the standard error for the y estimate, the F statistic, the degrees of freedom, and the regression and residual sums of squares.
- Give it several x ranges and it fits y = m1x1 + m2x2 + ... + b, a multiple regression.
Because LINEST returns an array, Microsoft says it has to be entered as an array formula. If you only want one number from it, Microsoft's shortcut is to wrap it in INDEX: =INDEX(LINEST(known_y's,known_x's),1) gives the slope and =INDEX(LINEST(known_y's,known_x's),2) gives the intercept. Microsoft adds that LINEST can be combined with other functions to model polynomial, logarithmic, exponential and power series, and that the more linear your data is, the more accurate the model.
The Analysis ToolPak option
For heavier statistics, Microsoft's Analysis ToolPak add-in builds output tables from the data and settings you give it. To load it on Windows: File, Options, Add-Ins, then in the Manage box select Excel Add-ins and click Go, tick Analysis ToolPak and click OK. Microsoft notes it analyses one worksheet at a time.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Where regression shows up in business
CFI points out that in financial analysis, SLOPE is used to calculate the beta of a stock. For most small businesses the use is simpler: does spending more on one thing go with more of another? Plot it first with a scatter plot in Excel, and if you'd rather see the line on the chart, add a trendline to it.
My verdict
A line is only as good as its r-squared, so check RSQ before you forecast anything with it. Microsoft's own example fits a line with an RSQ of 0.05795, and that line would be useless for prediction. Start with SLOPE, INTERCEPT and RSQ, which are easy to read. Move to LINEST or the ToolPak only when you need the statistics behind them. And always put y first.
Here's the related guide: my guide to compound interest in Excel.
Frequently asked questions
How do I do a linear regression in Excel?
Use =SLOPE(known_y's, known_x's) and =INTERCEPT(known_y's, known_x's) to get the line y = mx + b, then =RSQ to check the fit. LINEST gives all of it plus the statistics.
What is the difference between SLOPE and LINEST?
SLOPE returns one number, the slope. LINEST returns an array with the slope, the intercept and, with stats set to TRUE, the regression statistics. CFI notes they can disagree on degenerate data.
What does RSQ mean in Excel?
RSQ is the square of the Pearson correlation coefficient. Microsoft says it shows the proportion of the variance in y explained by the variance in x.
Why does SLOPE return #DIV/0!?
CFI says it happens when the x-values have zero variance, for example when they're all the same. Ranges of different lengths return #N/A instead.
Do I need the Analysis ToolPak for regression?
No. SLOPE, INTERCEPT, RSQ and LINEST work without it. The ToolPak builds full output tables for more complex analyses.
