After publishing the first article on machine learning in Excel, I received several questions asking: “Which specific formulas do you actually use?” and “Can you show concrete Excel functions that replicate ML algorithms?”

So, I put together a consolidated reference: a set of Excel functions I actively use, plus additional ones that are highly relevant for ML-style analysis even if I haven’t applied all these formulas in real scenarios.

Anyway, let’s go straight to the list:

1. Data Preparation (Cleaning, Normalization, Outliers, Missing Values)

Core cleaning functions

• TRIM() – removes extra spaces.
• CLEAN() – removes non-printable characters.
• TEXT() – formatting for consistent data types.
• VALUE() – converts text numbers to numeric.
• IFERROR(), IFNA() – handles invalid or missing inputs.

Regex functions (advanced parsing)

• REGEXEXTRACT() – extracts patterns (IDs, numbers, tokens).
• REGEXREPLACE() – cleans or restructures strings.
• REGEXMATCH() – boolean pattern detection.

Handling missing values

• AVERAGE(), MEDIAN(), MODE() – imputations.
• Custom rule:
  =IF(A2="", MEDIAN(A:A), A2)

Outlier detection

• PERCENTILE.EXC(), PERCENTILE.INC() – threshold flags.
• QUARTILE() – IQR logic.
• STDEV.P(), STDEV.S() – standard deviation anomalies.
• Outlier flag formula:
  =IF(OR(A2 < Q1 - 1.5*(Q3-Q1), A2 > Q3 + 1.5*(Q3-Q1)), 1, 0)


Normalization / scaling

• MIN(), MAX() – min-max scaling.
• STANDARDIZE() – z-score scaling.
• NORM.S.DIST(), NORM.DIST() – create Gaussian features.
• NORM.S.INV(), NORM.INV() – reverse transformations.
• Z.TEST() – test standardized values.

Sorting, filtering, deduplication

• SORT(), SORTBY() – reorganizing data for model training.
• FILTER() – removing noisy instances.
• UNIQUE() – finding class values.
• REMOVE.DUPLICATES() – critical to avoid data leakage.

Reshaping data

• TRANSPOSE() – switch orientation (wide ↔ long).
• WRAPROWS(), WRAPCOLS() – reshape into structured arrays.
• TAKE(), DROP() – extract dataset slices.

LET() and LAMBDA()

• LET() – cleaner formulas.
• LAMBDA() – define reusable ML functions:
  • loss function
  • distance function
  • prediction function
  • batch scoring

MAP(), REDUCE(), SCAN()

• Essential for vectorized ML pipelines:
  • MAP() – apply function to each row.
  • REDUCE() – accumulate results (e.g., loss).
  • SCAN() – cumulative transformations.

2. Feature Engineering (Numeric, Textual, Categorical, Time-Series)

Categorical encoding

• XLOOKUP() – label encoding.
• XMATCH() – numeric encoding from lists.
• CHOOSE() – simple mapping (e.g., Low/Med/High → 1/2/3).
• SWITCH() – multi-condition mapping.
• IF(), IFS() – target-based categories.

Text feature engineering

• TEXTSPLIT() – tokenization.
• TEXTJOIN() – recomposition of features.
• LEN() – length-based features.
• REGEXEXTRACT(), REGEXREPLACE(), REGEXMATCH() – pattern-based features.

Numeric feature engineering

• Interaction features:
  =A2 * B2
• Polynomial features:
  =A2^2, =A2^3
• Binary flags:
  =IF(A2 > threshold, 1, 0)

Rolling time-series features

• Rolling mean:
  =AVERAGE(OFFSET(A2,-6,0,7,1))
• Rolling sum:
  =SUM(OFFSET(A2,-6,0,7,1))

3. Core Statistical & Regression Functions (ML Modelling Basics)

Regression & correlation

• LINEST() – full regression stats (R², standard errors).
• LOGEST() – exponential regression.
• SLOPE(), INTERCEPT() – extract regression parameters.
• RSQ() – coefficient of determination.
• STEYX() – standard error of predicted y.
• CORREL(), PEARSON() – correlation between features.
• COVARIANCE.P(), COVARIANCE.S() – covariance matrices.

Probability distributions

• GAMMA(), GAMMALN()
• BETADIST()
• T.DIST(), CHISQ.DIST()

Used in statistical testing, ML evaluation, and uncertainty.

4. Matrix Algebra (for OLS, Multi-variable Regression, Custom Models)

These are essential for manually implementing linear models:

• MMULT() – matrix multiplication.
• MINVERSE() – matrix inverse (OLS solution).
• MDETERM() – determinant.
• TRANSPOSE() – orientation switching.
• SUMPRODUCT() – dot-product (manual prediction formula).

5. Model Building (Regression, Classification, Time-Series)

5.1 Linear regression (two options)

Quick method (1 line): =LINEST(y_range, x_range, TRUE, TRUE)

Manual matrix regression (multi-variable) using:

• MMULT()
• MINVERSE()
• TRANSPOSE()

5.2 Logistic regression

Probability formula: =1/(1+EXP(-(B0 + B1*x1 + B2*x2)))

5.3 Classification logic

• Threshold method:
  =IF(prob>0.5,1,0)
• Multi-class:
  =IFS(class1_prob>0.5,"A", class2_prob>0.5,"B", TRUE,"C")

5.4 Time-series forecasting

• FORECAST.LINEAR() – simple trend.
• FORECAST.ETS() – full ETS algorithm.
• FORECAST.ETS.SEASONALITY() – seasonality detection.
• FORECAST.ETS.CONFINT() – confidence intervals.
• FORECAST.ETS.STAT() – quality metrics (MASE, RMSE).
• MOVING AVERAGE (Toolpak) – smoothing.
• EXPONENTIAL SMOOTHING (Toolpak) – classical forecasting.

6. Model Evaluation & Scoring

Error metrics

• MSE: =AVERAGE((actual - predicted)^2)
• RMSE: =SQRT(AVERAGE((actual - predicted)^2))
• MAE: =AVERAGE(ABS(actual - predicted))
• R²: =RSQ(predicted, actual)

Confidence intervals

• CONFIDENCE.T()
• CONFIDENCE.NORM()

Classification metrics

• COUNTIF(), COUNTIFS(), SUMIFS()

Accuracy example: =COUNTIF(correct_predictions, TRUE)/COUNT(correct_predictions)

Batch scoring using MAP()

If you have a model defined as a LAMBDA: =MAP(A2:A500, B2:B500, C2:C500, PredictOne)

7. Optimization (Training Models with Solver)

The Solver Add-In is essential for ML tasks in Excel:

• training logistic regression
• fitting nonlinear models
• optimizing custom objective functions built with LAMBDA
• minimizing loss functions
• parameter search for forecasting models

It works with:

• linear optimization
• nonlinear optimization
• evolutionary optimization

8. Deployment in Excel (Turning a Model Into a Practical Tool)

Build a prediction function

Use LAMBDA() to create a reusable prediction model.

Vectorized scoring

Use MAP() to score thousands of rows instantly.

Create a mini-dashboard

• Slicers
• Conditional formatting
• Sparklines
• Trend charts
• Outlier flags
• Seasonality analysis

This transforms Excel into a practical ML environment.

Enjoy!