Excel Functions for Correlation, Covariance, and OLS

Quick reference for computing correlation, covariance, and a simple OLS line (ŷ = β·x + α) in Excel. Works with ranges like A2:A101 and B2:B101.

Metric / Output	Excel Function	What it means / tips
Slope (β)	`=SLOPE(known_y’s, known_x’s)`	Rise per unit X in the best-fit line ŷ = βx + α. Units: “Y per X”.
Intercept (α)	`=INTERCEPT(known_y’s, known_x’s)`	Predicted Y when X = 0 in the simple OLS line.
R² (goodness of fit)	`=RSQ(known_y’s, known_x’s)`	Share of Y variation explained by X (0–1). In simple regression, R² = ρ².
Correlation (ρ)	`=CORREL(array1, array2)`	Pearson correlation (−1 to +1). Unitless; strength + direction of linear association.
Covariance	`=COVARIANCE.P(array1, array2)` `=COVARIANCE.S(array1, array2)`	Population vs. sample version. Has units (Y·X). Sign matches correlation’s sign.
Variance	`=VAR.P(array)` (population) `=VAR.S(array)` (sample)	Use the `.S` form for samples (divides by n−1). Needed if you’re reproducing formulas by hand.
Std. deviation	`=STDEV.P(array)` (population) `=STDEV.S(array)` (sample)	Square-root of variance. Use with covariance to compute ρ by hand: ρ = cov/(s_Xs_Y).
Prediction (ŷ at X)	`=FORECAST.LINEAR(x, known_y’s, known_x’s)`	Returns ŷ on the fitted line for a given X. Equivalent to `=SLOPE(...)*x + INTERCEPT(...)`.
Full OLS output	`Data → Data Analysis → Regression`	Produces coefficients, standard errors, t-stats, ANOVA, residuals, etc. (Enable “Analysis ToolPak” if needed.)

Range setup. Put Y in one column (e.g., A2:A101) and X in another (e.g., B2:B101). Use the same-length ranges in all functions.

Sample vs. population: If your data are a sample (most homework/projects), prefer the .S versions (VAR.S, STDEV.S, COVARIANCE.S).

By-hand check: In simple OLS, you can verify the slope via =COVARIANCE.P(B:B,A:A)/VAR.P(B:B) (population form) or the matching .S pair for samples. Intercept = ȳ − β x̄.