ICE — Excel Data Analysis → Regression (ToolPak)

Do the whole regression via Excel ToolPak (Data → Data Analysis → Regression) and learn to read the Summary Output: Regression Statistics, ANOVA, and Coefficients.

Prefer building with Excel functions? 👉 Use the Functions version.

Step A — Data (X, Y)

Type/paste below, or click “Generate data.” Columns: X, Y.

#	X	Y

Excel paste tip: click cell A1 (not in edit mode), then Ctrl+V. If a column sticks together: Data → Text to Columns → Delimited → Tab.

Step B — Excel ToolPak: Data → Data Analysis → Regression

Excel: Data tab → Data Analysis. (If missing: File → Options → Add-ins → Manage “Excel Add-ins” → Go… → check Analysis ToolPak.)
Choose Regression → OK.
Input Y Range: B1:B… Input X Range: A1:A… (include headers).
Check Labels. Optional outputs: check Residuals & Standardized Residuals.
Output → New Worksheet Ply. Click OK. Compare Excel’s table to the panel below — numbers should match.

In simple linear regression, Multiple R = |r|. Standard Error in “Regression Statistics” equals √MSE (≈ STEYX) in Y-units.

Step C — What Excel will show (mirrors “Summary Output”)

Regression Statistics

Multiple R	–
R Square	–
Adjusted R Square	–
Standard Error	–
Observations	–

ANOVA

Source	df	SS	MS	F	Significance F
Regression	1	–	–	–	–
Residual	–	–	–	–	–
Total	–	–	–	–	–

Reading tip: SST = total variation in Y; SSR = explained by the line; SSE = leftover error. MS = SS/df. F = MSR/MSE tests if slope ≠ 0. In simple regression, F = t(b₁)².

ANOVA glossary: SST = Total Sum of Squares = Σ(y−ȳ)² (df = n−1) • SSR = Regression/Explained Sum of Squares = Σ(ŷ−ȳ)² (df = # predictors) • SSE = Error/Residual Sum of Squares = Σ(y−ŷ)² (df = n−(# predictors+1)) • MS = SS/df, F = MSR/MSE, R² = SSR/SST. With one X, F = t(b₁)². In Excel’s ANOVA: Regression SS = SSR, Residual SS = SSE, Total SS = SST.

Coefficients

	Coefficients	Standard Error	t Stat	P-value
Intercept	–	–	–	–
X	–	–	–	–

Intercept = expected Y when X=0 (may be outside observed X — interpret with caution).

Slope: +1 unit in X → about – units change in Y.

P-values: small p ⇒ strong evidence the coefficient ≠ 0 (in this one-X model).

Step D — “Explain Excel’s boxes” (student-friendly)

Regression Statistics — what each line means

Multiple R: absolute correlation between ŷ and y. In SLR it’s |r| between X and Y.
R Square: percent of Y’s variation explained by the straight line (SSR/SST).
Adjusted R Square: penalty for extra predictors (here k=1): 1 − (1−R²)·(n−1)/(n−k−1).
Standard Error: √MSE, typical vertical miss in Y-units (≈ STEYX).
Observations: sample size n.

ANOVA — how the F-test works

H₀: slope b₁ = 0 (no straight-line signal). H₁: b₁ ≠ 0.
SSR + SSE = SST. df: 1, n−2, n−1.
MSR=SSR/1, MSE=SSE/(n−2), F=MSR/MSE. Small p(F) ⇒ reject H₀ ⇒ line explains Y.
In simple regression F = t(b₁)² exactly.

Video — Estimating OLS Regressions using Excel (ToolPak)

If the embed doesn’t load, open on YouTube. (Requires internet.)