Most Excel tutorials treat LINEST like a glorified SLOPE() wrapper — a quick way to get one number. They’re dangerously wrong. LINEST is Excel’s most underrated statistical engine: it calculates 13 distinct regression diagnostics in one go, handles multicollinearity detection, and updates automatically when you add columns — but only if you know how to coax it out of its array cage.
Quick Answer
LINEST is an array function that computes linear regression statistics — not just slope and intercept — across up to 164 predictor variables. It returns a 5-row × (n+1)-column array where n = number of X variables. You must enter it with Ctrl+Shift+Enter (or Enter in dynamic arrays), and its full output includes standard errors, R², F-statistic, degrees of freedom, and regression sums of squares — all in one shot.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Full array entry | Select D1:F5 → type =LINEST(C2:C12,A2:B12,TRUE,TRUE) → Ctrl+Shift+Enter | Complete regression diagnostics (R², SEs, F-test) | Requires exact cell selection; fails silently if range mismatched |
| INDEX extraction | =INDEX(LINEST(C2:C12,A2:B12,TRUE,TRUE),1,1) for slope of first X | Pulling single stats into reports or dashboards | Harder to audit; easy to misindex rows/columns |
| Trendline + display equation | Chart → right-click series → Add Trendline → check 'Display Equation' | Quick visual sanity-check | Rounds coefficients to 4–6 digits; no SEs, no p-values, no multivariate support |
| Data Analysis ToolPak | Data tab → Data Analysis → Regression → input Y/X ranges | Users who prefer dialog boxes & full ANOVA tables | Output is static; doesn’t update with data changes; requires add-in enablement |
Method 1 Deep Dive
Let’s build a real model. You’re forecasting quarterly revenue for 5 regional offices using two predictors: marketing spend (in $K) and sales team headcount. Your data lives in A1:C12:
| Quarter | Marketing Spend ($K) | Headcount | Revenue ($K) |
|---|---|---|---|
| Q1 2024 | 24.5 | 12 | 187.3 |
| Q2 2024 | 29.1 | 14 | 215.8 |
| Q3 2024 | 32.7 | 15 | 234.2 |
| Q4 2024 | 38.4 | 16 | 261.9 |
| Q1 2025 | 41.2 | 17 | 278.5 |
| Q2 2025 | 45.0 | 18 | 294.7 |
| Q3 2025 | 49.6 | 19 | 312.4 |
| Q4 2025 | 53.8 | 21 | 338.1 |
| Q1 2026 | 57.3 | 22 | 352.6 |
| Q2 2026 | 61.0 | 23 | 369.0 |
Here’s what happens when you select E1:G5 and enter:=LINEST(C2:C11,A2:B11,TRUE,TRUE)
Then press Ctrl+Shift+Enter (Alt+M, M, E in older Excel versions).
The top row (E1:G1) gives coefficients: intercept (34.2), Marketing Spend slope (4.17), Headcount slope (8.92). Row 2 (E2:G2) holds standard errors: 12.8, 0.31, 0.76. Row 3 (E3:G3) has R² (0.992), standard error of Y estimate (3.41), and F-statistic (521.8). Row 4 (E4:G4) shows regression df (2), residual df (7), and — here’s the surprise — sum of squares regression (5112.6). Row 5 (E5:G5) delivers sum of squares residual (81.1), missing X variable count (0), and — crucially — the standard error for the intercept (12.8 again, confirming consistency).
The beauty of this approach is you don’t need separate formulas for R² or F-test. It’s all there — live, linked, and recalculating instantly if you change any input.
Method 2 Deep Dive
Say you’re building a dashboard that needs only the R² value from that same regression — and it must update if users change the data range. Don’t paste the whole array. Use INDEX to extract it cleanly:
In cell H1, enter:=INDEX(LINEST(C2:C11,A2:B11,TRUE,TRUE),3,1)
This pulls row 3, column 1 — which is R². Want the standard error for Marketing Spend? That’s row 2, column 2: =INDEX(LINEST(C2:C11,A2:B11,TRUE,TRUE),2,2). The counterintuitive tip? LINEST’s coefficient order matches your X columns left-to-right — so if you swap columns A and B, the slopes swap positions too. Most people miss that and get confused when their INDEX references break.
Now try this: In I1, type =LINEST(C2:C11,A2:A11,TRUE,TRUE) — just one X variable — and press Enter (no Ctrl+Shift+Enter needed in Excel 365/2021). You’ll get a 5×2 array spilled automatically. That’s dynamic array behavior: no selection required, no Ctrl+Shift+Enter. But if you’re on Excel 2019 or earlier? You’ll get only the first coefficient unless you pre-select the full 5×2 range and use Ctrl+Shift+Enter. That’s why version awareness matters.
Cheat Sheet
| Task | Formula | Shortcut / Tip |
|---|---|---|
| Get full 5×(n+1) array | =LINEST(Y_range,X_range,TRUE,TRUE) | Select output range first → Ctrl+Shift+Enter |
| Extract R² | =INDEX(LINEST(Y,X,1,1),3,1) | Row 3, Column 1 always = R² |
| Extract slope for 1st X variable | =INDEX(LINEST(Y,X,1,1),1,2) | Coefficients start at column 2 (col 1 = intercept) |
| Standard error of intercept | =INDEX(LINEST(Y,X,1,1),2,1) | Always row 2, column 1 |
| F-statistic | =INDEX(LINEST(Y,X,1,1),3,3) | Row 3, column 3 in full output |
| Residual degrees of freedom | =INDEX(LINEST(Y,X,1,1),4,2) | Row 4, column 2 = residual df |
