Regression analysis in Excel, step by step: SLOPE, LINEST and the ToolPak, on one worked table

Regression analysis in Excel fits a straight line through your data, y = a + bx, so you can say how much y moves when x moves by one unit. On ten months of one sales team's data, =SLOPE says each extra quote goes with about $8,930 more revenue, and =RSQ says the line accounts for 97.2% of the month-to-month variation. Excel gives the same answer three ways: worksheet functions, LINEST and the Analysis ToolPak.

What a regression tells you, in one sentence

y = a + bx, where b = Σ(x − x̄)(y − ȳ) / Σ(x − x̄)² and a = ȳ − b x̄

R squared = 1 − SS residual / SS total

b, the slope, is the change in y that goes with one more unit of x. a, the intercept, is the value of y on the line when x is zero. R squared is the share of the variation in y that the line accounts for, from 0 to 1. Excel fits the line by least squares: in the NIST/SEMATECH e-Handbook's words, the parameters are chosen to "minimize the sum of the squared deviations between the observed responses and the functional portion of the model."

A regression measures association, not cause. More quotes going with more revenue does not show that issuing quotes creates revenue; association and cause sets out what a coefficient can and cannot claim.

The rows you need

One row per period or per customer. y in one column, each x in its own column, and nothing else in the ranges: no blanks, no text, no subtotal rows. Here, ten months for one sales team, with revenue in thousands of dollars:

Headers are in row 1, data in rows 2 to 11. One unusually large order in a single month can drag the whole line; find and treat it first, as in outliers in sales data.

Simple linear regression with four functions

Revenue (y) on quotes (x). Put the line in G1 and G2 so the residual formula below can refer to it:

G1  =INTERCEPT(D2:D11,B2:B11)          18.83
G2  =SLOPE(D2:D11,B2:B11)              8.9285
G3  =RSQ(D2:D11,B2:B11)                0.9723
G4  =STEYX(D2:D11,B2:B11)              18.83
G5  =FORECAST.LINEAR(65,D2:D11,B2:B11) 599.19

The fitted line is revenue = 18.83 + 8.9285 × quotes. Each extra quote goes with about $8,930 more revenue in the month. R squared of 0.972 says the line accounts for 97.2% of the variation in monthly revenue. STEYX, the standard error of the estimate, says a typical month sits about $18,830 off the line; that it equals the intercept to two decimals here is a coincidence of this data.

FORECAST.LINEAR reads the line at a new x: 18.83 + 8.9285 × 65 = 599.19, about $599,000 for a 65-quote month. Microsoft Support notes that in Excel 2016 FORECAST.LINEAR replaced FORECAST, which still works for backward compatibility. For the wider choice of forecasting methods, see four sales forecasting methods compared.

To see the same line on a chart, select B1:B11 and D1:D11, insert a scatter chart, right-click the points and choose Add Trendline, Linear, with "Display Equation on chart" and "Display R-squared value on chart" checked. The label reads y = 8.9285x + 18.83 and R² = 0.9723: the same numbers as the functions.

Multiple regression with LINEST

Add the second driver, average discount. Select an empty cell and enter:

=LINEST(D2:D11,B2:C11,TRUE,TRUE)

In Excel for Microsoft 365 it spills into a grid of five rows and three columns; in older versions select the 5 × 3 range first and press Ctrl+Shift+Enter. The LINEST documentation gives the order as {mn, mn-1, ..., m1, b}: the coefficients come back in reverse column order, so discount (column C) comes first and quotes (column B) second.

The fitted line is revenue = 130.09 + 8.23 × quotes − 13.30 × discount. Holding quotes constant, each extra point of average discount goes with about $13,300 less revenue. A 65-quote month at 5.0% discount reads 130.09 + 8.23 × 65 − 13.30 × 5 = 598.5, about $598,500.

The quotes coefficient fell from 8.93 to 8.23 when discount came in. Quotes and discount are correlated at −0.64 (=CORREL(B2:B11,C2:C11)): busy months were also low-discount months, so in the one-driver line part of the discount effect was credited to quotes. To pull one coefficient into a single cell, index the grid:

=INDEX(LINEST(D2:D11,B2:C11),1,2)

which returns 8.2295, the quotes coefficient.

The same thing with the Analysis ToolPak

Load the add-in once (the steps are in the Data Analysis ToolPak guide), then choose Data > Data Analysis > Regression. Input Y Range D1:D11, Input X Range B1:C11, check Labels, choose an output cell and check Residuals. The Microsoft Support page on the ToolPak confirms the tool fits the line by least squares, so it returns the LINEST figures with more labels:

How to read it. Multiple R is the square root of R Square. Adjusted R Square, 0.9754, discounts R Square for the number of drivers; it rose from 0.9688 in the one-driver line, so discount earns its place on that test. Standard Error is the typical miss, about $16,730. Each t Stat is the coefficient divided by its standard error, and each P-value is the two-tailed probability of a t that large if the true coefficient were zero.

Quotes is clear (P = 0.000003). Discount, at P = 0.12, is not significant at the usual 0.05 threshold: its 95% interval runs from −31.08 to +4.47 and includes zero. Ten rows and two correlated drivers do not pin it down. Report the discount effect as a direction worth testing on more data, not as a fact.

The check that proves it

Three identities hold for any least-squares line with an intercept. On the one-driver line, residual in E2 is =D2-($G$1+$G$2*B2), filled down:

1. Predicted plus residual equals actual on every row.
2. Residuals sum to zero. =SUM(E2:E11) returns 0 to rounding; the two-decimal table sums to 0.01.
3. The line passes through the two means. 18.83 + 8.9285 × 56.4 = 522.4, the mean revenue.

Also check R squared from its parts: 1 − 2,835.23 / 102,356.40 = 0.9723, where 2,835.23 is =SUMSQ(E2:E11) and 102,356.40 is =DEVSQ(D2:D11).

Where it goes wrong

• Reading the slope as cause. More quotes going with more revenue does not show that issuing more quotes creates revenue; both may follow demand.
• Reading LINEST left to right. The coefficients come back in reverse order of the x columns, so reading them left to right swaps the drivers.
• Too many drivers for the rows. With ten rows and five drivers, every extra x raises R squared. Use adjusted R squared and keep drivers few relative to rows.
• Predicting outside the data. A 120-quote month when the history runs 38 to 74 has no support in the fit.
• Stale ToolPak output. The ToolPak pastes values, not formulas, so it does not update when the data changes; rerun it. LINEST and SLOPE recalculate.

From one workbook to every product and region

A regression says revenue goes with quotes; a revenue bridge says which customers and products moved it, and by how much. When the same line has to be refit for every region each month, the bridge answers the question more directly. Covirage's tools compute the bridge from your sales file and reconcile it to the ledger; the external AI model explains it and never does the arithmetic. See revenue driver analysis, and Excel or an analytics tool for when the workbook stops being enough. For projecting the fitted trend forward, see the Excel FORECAST function; for testing a plan against a downside, scenario analysis in Excel.