Multiple Regression in Excel: ToolPak and LINEST

Short answer: Excel fits a multiple regression in two ways. The Analysis ToolPak Regression tool (Data tab, Data Analysis) writes a formatted report, and the LINEST function returns the same least-squares fit as a live array of numbers. Both take one dependent variable (y) and several independent variables (x columns). This guide covers both routes, how to read the output, and the one trap that catches most users: LINEST lists coefficients in reverse order.

What it is used for in research

Multiple regression estimates how one outcome changes with several predictors at once, holding the others fixed. Microsoft describes the ToolPak Regression tool as performing linear regression analysis by the least squares method, showing how a single dependent variable is affected by the values of one or more independent variables. For the statistical ideas behind the output (assumptions, interpretation, reporting), see linear and multiple regression; this page is only about doing it in Excel.

Install or access (official source only)

The ToolPak is a Microsoft add-in that ships with Excel and is switched on from the Excel options. According to Microsoft’s “Load the Analysis ToolPak in Excel” page:

Excel for Windows: File › Options › Add-Ins; in the Manage box choose Excel Add-ins, select Go, tick Analysis ToolPak, then OK.
Excel for Mac: Tools › Excel Add-ins; tick Analysis ToolPak, then OK (restart Excel if the Data Analysis command does not appear; use Browse if the add-in is not listed).

Once loaded, Data Analysis appears on the Data tab. Microsoft also notes that the tools work on one worksheet at a time: if several sheets are grouped, results appear on the first sheet only. The LINEST route needs no add-in. A broader tour of the add-in is in the Excel Analysis ToolPak guide.

Step-by-step: Data Analysis Regression tool

Lay out the data with the outcome in one column and each predictor in its own column, one row per observation. The UC Davis handout states that Excel requires the regressor columns to be next to each other (contiguous), so copy columns into one block first if they are scattered.

Select Data › Data Analysis › Regression and choose OK.
Input Y Range: select the cells holding the dependent variable.
Input X Range: select all predictor columns together (for example three adjacent columns).
Leave Constant is Zero unticked unless you deliberately want to force the line through the origin (Oregon State’s handout describes the box this way).
Optionally set the Confidence Level (the UC Davis example uses 99%) and tick the Residuals options if you want residual output.
Under Output options, choose where the report goes, then OK.

Step-by-step: LINEST

Microsoft’s syntax is LINEST(known_y's, [known_x's], [const], [stats]). For several predictors, known_y's must be a single column (or row), and each column of known_x's is interpreted as a separate variable. Set const to FALSE only to force the intercept to zero, and stats to TRUE to get the full regression statistics.

Pick an empty block of cells with 5 rows and as many columns as predictors plus one.
Enter =LINEST(Y, X, TRUE, TRUE) using your ranges (for example =LINEST(B2:B41, C2:E41, TRUE, TRUE)).
Because the function returns many values, it is an array formula: in current Excel it spills automatically; in older versions confirm it as a multi-cell array formula.

Output and how to read it

The Regression tool report

The report has three parts. Regression Statistics gives Multiple R, R Square, Adjusted R Square, Standard Error and Observations. ANOVA has Regression, Residual and Total rows with df, SS, MS, F and Significance F; Significance F is the p-value of the overall F-test (UC Davis). The coefficient table lists the Intercept and each variable with Coefficient, Standard Error, t Stat, P-value and Lower/Upper 95% (UC Davis).

The LINEST layout and the reversed-order trap

With stats TRUE, Microsoft documents five rows:

LINEST row	Contents	Matching item in the Regression tool report
1	Coefficients, then the constant	Coefficient column
2	Standard errors of the coefficients and of the constant	Standard Error column of the coefficient table
3	r² and the standard error of the y estimate	R Square and Standard Error
4	F statistic and residual degrees of freedom	F and Residual df in ANOVA
5	Regression sum of squares and residual sum of squares	SS column of ANOVA

Trap: Microsoft states that the array is returned as {mn, mn-1, …, m1, b}. Coefficients therefore appear from the last x column back to the first, with the constant at the far right. If your X range is columns C, D, E, the first number in row 1 belongs to column E. The Regression tool lists variables in the order you selected them, so the two outputs look mirrored.

Worked reading (illustrative composite)

The numbers below are invented for illustration only; they are not from any real study. Suppose 40 observations (n = 40) and three predictors (k = 3), with R Square = 0.62.

Adjusted R Square penalises extra predictors: 1 − (1 − R²)(n − 1)/(n − k − 1) = 1 − 0.38 × 39/36 ≈ 0.588. R Square never falls when you add a predictor, but Adjusted R Square can, so compare models with the adjusted value. LINEST does not return it; compute it from row 3 and the degrees of freedom.
F and Significance F: F = (R²/k) / ((1 − R²)/(n − k − 1)) = (0.62/3)/(0.38/36) ≈ 19.6 with 3 and 36 degrees of freedom. This is the F that LINEST reports in row 4, next to the residual degrees of freedom. Significance F is its upper-tail probability, which you can compute from LINEST output with =F.DIST.RT(F, k, df); here it is far below 0.05.
Coefficient p-values test each predictor separately given the others, so a significant overall F does not mean every predictor is significant.

Troubleshooting

No Data Analysis button: the add-in is not loaded; follow the load steps above.
Coefficients look reversed or mismatched: you are reading LINEST output left to right; read it right to left against your X columns.
A coefficient and its standard error are both 0 in LINEST: Microsoft says LINEST checks for collinearity and removes redundant X columns, showing zero coefficients and zero standard errors for them. One predictor is then an exact combination of others; see perfect multicollinearity.
Intercept forced to zero: check that const is not FALSE (LINEST) and that Constant is Zero is unticked (Regression tool). With const FALSE, Microsoft notes the standard error of the constant is returned as #N/A.
Predictors not in adjacent columns: copy them into a contiguous block before running the Regression tool.

Free alternatives

If you need diagnostics beyond Excel’s report, these tools fit the same kind of model. The notes come from each project’s own documentation.

Tool	Function	Documentation note
R	`lm()`	The R manual says `lm` is used to fit linear models, including multivariate ones; in a formula, `first + second` includes all terms of both, and a formula has an implied intercept. `summary()` and `anova()` print a summary and the analysis of variance table.
Python statsmodels	`OLS`	Fits a linear regression by ordinary least squares for a response and a set of regressors. The documentation states no constant is added unless you use formulas, so add one yourself (`add_constant`).

For a first look at pairwise relationships before modelling, see correlation in Excel.

FAQ

Can Excel run multiple regression?

Yes. Use Data › Data Analysis › Regression after loading the Analysis ToolPak, or enter LINEST with several X columns. Both use least squares.

Why are my LINEST coefficients in the wrong order?

LINEST returns coefficients as {mn, …, m1, b}: last X column first, constant last.

Which is better, the Regression tool or LINEST?

The Regression tool gives a labelled report with p-values and confidence intervals at once; LINEST gives live numbers without labels. Microsoft states the tool uses LINEST.