Run a Regression with Python in Excel

Excel can fit a regression with LINEST or the Analysis ToolPak, but both give a block of unlabelled numbers and nothing to check the fit with. Python in Excel puts statsmodels one =PY( away: the same least-squares fit, with named coefficients, standard errors, p-values, prediction intervals and residual plots, recalculating whenever the table changes. This page fits a sales model on a monthly table step by step, returns each result to the grid in a shape people can read, and checks the numbers against LINEST. It is part of Using Python in Excel.

Prerequisites

Python in Excel is part of Microsoft 365 on Windows, Mac and the web for supported plans; statsmodels, pandas and matplotlib are provided in Microsoft's cloud runtime, with statsmodels.api already imported as sm. The workbook has an Excel table named Sales with three years of monthly data: advertising spend in thousands, average selling price, a 0/1 flag for months with a promotion, and units sold.

The question: how many extra units does each thousand of advertising buy, what does a price rise cost, and is the promotion worth running? The code below runs unchanged in =PY cells; each block is one cell, entered in order from left to right or top to bottom.

Step 1 — Read the table

In an empty cell, type =PY(, read the whole table with its headers, and commit with Ctrl+Enter:

sales = xl("Sales[#All]", headers=True)
print(sales.shape)
print(sales.dtypes.to_string())

In a PY cell, print() output goes to the Diagnostics pane and the cell itself shows the value of the last expression; the examples print so their output can be shown here. Numeric columns arrive as float64 or int64; if one shows object or str instead, some of its cells hold text, and the fit will fail or silently drop rows. Fix that in the table or convert with pd.to_numeric(..., errors="coerce") before fitting. Reference Cells, Ranges and Tables with xl() in Python in Excel covers the other ways to point xl() at data.

Step 2 — Fit the model

The formula interface names the outcome on the left of ~ and the predictors on the right, and adds the intercept for you:

import statsmodels.formula.api as smf

model = smf.ols("Units ~ AdSpend + Price + Promo", data=sales).fit()
print(f"R² {model.rsquared:.4f}   adjusted R² {model.rsquared_adj:.4f}   n={int(model.nobs)}")

In the cell, model shows as a Python object card. Keep the fit in its own cell so the cells that follow can use it without refitting; Python cells share one namespace per workbook and run in row-major order. Return DataFrames and Values from Python in Excel Cells explains the object card and how to switch a cell's output to values.

Step 3 — Return a readable coefficient table

model.summary() is a text report that looks fine in a notebook but lands as one cell of text in Excel. Build a DataFrame instead, switch the cell's output to Excel value (Ctrl+Alt+Shift+M), and it spills into a labelled table that formulas and charts can reference:

coef = model.summary2().tables[1].rename(columns={
    "Coef.": "Estimate", "Std.Err.": "Std error", "P>|t|": "p-value",
    "[0.025": "Low 95%", "0.975]": "High 95%",
})
coef = coef.round({"Estimate": 3, "Std error": 3, "t": 2, "p-value": 4, "Low 95%": 3, "High 95%": 3})
print(coef.to_string())

Read it as: each extra thousand of advertising adds about 14.2 units, a one-unit price rise loses about 64 units, and promotion months sell about 44 more units, holding the other two constant. The confidence intervals are the honest version of those numbers: the promotion effect could plausibly be anywhere between about 22 and 67 units. All three p-values are well below 0.05, so none of the effects is plausibly zero in this data.

Step 4 — Predict next month, with an interval

A point forecast on its own invites false precision. get_prediction gives both the interval for the average month with those inputs and the wider interval for a single month:

plan = pd.DataFrame({"AdSpend": [30.0, 30.0], "Price": [9.50, 9.99], "Promo": [1, 1]},
                    index=["Plan A", "Plan B"])
pred = model.get_prediction(plan).summary_frame(alpha=0.05)
forecast = pred[["mean", "obs_ci_lower", "obs_ci_upper"]].round(0)
forecast.columns = ["Forecast", "Low 95%", "High 95%"]
print(forecast.to_string())

obs_ci_lower and obs_ci_upper are the prediction interval for one new month; that is the range to plan stock against. The narrower mean_ci_* columns describe the average and are rarely what a planner wants. Point the plan's inputs at cells, with xl("B40:D41", headers=True), and the forecast updates whenever someone types a new scenario.

Step 5 — Check the residuals

A regression can report a high R² and still be wrong: a curve where the model assumes a line, a trend over time the predictors do not explain, a few outliers carrying the fit. Plot residuals against fitted values and against time. Return the figure from the cell and Excel shows it as an image:

fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(9, 3.2))
ax1.scatter(model.fittedvalues, model.resid, s=14)
ax1.axhline(0, color="grey", lw=1)
ax1.set(xlabel="Fitted units", ylabel="Residual", title="Residuals vs fitted")
ax2.plot(sales["Month"], model.resid, marker="o", ms=3)
ax2.axhline(0, color="grey", lw=1)
ax2.set(title="Residuals over time")
ax2.set_xticks(sales["Month"][::6])
fig.tight_layout()
print(f"residual std error: {np.sqrt(model.scale):.2f} units; largest miss: {model.resid.abs().max():.0f} units")
fig  # the last expression is what the cell returns

What you want to see is a shapeless band around zero in both panels. A funnel shape means the error grows with the level, a curve means a missing term such as I(Price**2), and a drift over time means something seasonal or trending is not in the model. Plot Charts with matplotlib in Python in Excel covers sizing the image in the cell.

Step 6 — Confirm against LINEST

It is worth checking once that the Python fit and Excel agree, especially before a model replaces a spreadsheet someone trusts. Enter =LINEST(Sales[Units], Sales[[AdSpend]:[Promo]], TRUE, TRUE) in an empty cell; it spills a five-row block. For this table the first two rows are:

These are the same numbers as the statsmodels table, but in reverse column order with the intercept last, which is the usual source of "the numbers don't match" when people compare. The third row starts with R² (0.9623) and the standard error of the estimate (31.98), matching model.rsquared and the residual standard error above. CORREL and LINEST Equivalents in Python goes through the rest of the block.

Categorical predictors and interactions

The formula interface handles what LINEST makes painful. A text column such as region becomes a set of indicator columns with C(), and an interaction asks whether the promotion works better when advertising is high:

sales["Quarter"] = "Q" + pd.PeriodIndex(sales["Month"], freq="M").quarter.astype(str)
m2 = smf.ols("Units ~ AdSpend + Price + Promo + C(Quarter) + Promo:AdSpend", data=sales).fit()
print(m2.params.round(2).to_string())
print(f"adjusted R² {m2.rsquared_adj:.4f} vs {model.rsquared_adj:.4f} without the extra terms")

Adjusted R² is the fair comparison here, because plain R² always rises when terms are added. It rose a little, from 0.9588 to 0.9654, but that is not the whole story: print m2.conf_int() and the 95% intervals for every quarter term and for Promo:AdSpend (about -4.5 to 0.7) include zero. With three years of data there is no reliable evidence of a seasonal pattern or of the promotion interacting with advertising, so the simpler model is the one to report. Note too that with an interaction in the model, the Promo coefficient on its own means the promotion effect at zero advertising, not the average effect.

Common pitfalls

• Text in numeric columns. A single "n/a" makes the column object; convert and count what became missing before fitting.
• Missing values dropped silently. statsmodels' formula interface drops rows with missing values by default; compare model.nobs with the table's row count.
• Reading causation into coefficients. The model shows association in this data. Advertising may have been raised in months that were going to be strong anyway.
• Extrapolating. Predictions for inputs outside the range in the table (here advertising 10–40, price 8–12) are guesses the model cannot check.
• Comparing with LINEST column by column. LINEST reverses the predictor order; match by name.
• Returning summary(). It lands as text in one cell; return DataFrames and switch the output to values.

Conclusion

Read the table with xl(), fit with smf.ols("y ~ x1 + x2", data=...) in its own cell, and return results as DataFrames — the summary2() coefficient table, get_prediction(...).summary_frame() for forecasts with intervals — spilled as Excel values. Check the fit with residual plots before trusting it, and confirm once against LINEST, remembering its reversed order. Add categorical terms with C() and judge extra terms with adjusted R².

Frequently asked questions

Is statsmodels available in Python in Excel? Yes. It is part of the Anaconda runtime Microsoft provides, and statsmodels.api is imported as sm by default. Import statsmodels.formula.api as smf yourself to use formulas.

Why do my Python and LINEST coefficients look different? LINEST returns coefficients in reverse order of the predictor columns, with the intercept last. Matched by name, the estimates, standard errors and R² are the same for ordinary least squares.

How do I show the regression output in cells instead of a Python object? Return a DataFrame, such as model.summary2().tables1, and switch the PY cell's output to Excel value. The table spills into the grid with labels.

Can the regression update when the data changes? Yes. PY cells recalculate like formulas, so adding a month to the Sales table refits the model and updates every cell that uses it.

Related

• Up one level: Using Python in Excel — the =PY function and how cells work together.
• CORREL and LINEST Equivalents in Python — the same statistics outside Excel.
• Return DataFrames and Values from Python in Excel Cells — spilling results into the grid.
• Plot Charts with matplotlib in Python in Excel — charts from PY cells.
• Python in Excel vs openpyxl and xlwings — when a script is the better home.