Normalize Values to a 0–1 Scale

Excel Formulas › Statistics

All versionsMIN

Put values on a common 0–1 scale so different metrics are comparable — for scoring, heat maps, or feeding a model. Subtract the min and divide by the range.

Quick formula: normalize A2 against the range of A2:A100:
=(A2 - MIN($A$2:$A$100)) / (MAX($A$2:$A$100) - MIN($A$2:$A$100))
The smallest value maps to 0, the largest to 1, everything else in between.

Functions used (tap for the full reference guide):

MINMAX

The example

Scores rescaled to 0–1.

AB
1ValueNormalized
220 (min)0.00
3500.50
480 (max)1.00

The formula

The formula:

=(A2 - MIN(rng)) / (MAX(rng) - MIN(rng)) // min→0, max→1

How it works

How it works:

  1. Subtract the minimum so the smallest value becomes 0.
  2. Divide by the range (max − min) so the largest becomes 1.
  3. Lock the range with $ so every row scales against the same min/max.
  4. To scale to 0–100 instead, multiply the result by 100.

Min-max vs z-score: min-max bounds everything to 0–1 (good for fixed scales and heat maps); the z-score centers on the mean and isn’t bounded (good for outlier detection). Pick by what the number feeds.

Try it: interactive demo

Live demo

Values → 0-1.

Variations

Scale to 0-100

Multiply by 100:

=(A2-MIN(rng))/(MAX(rng)-MIN(rng))*100

Reverse (1 best)

Invert:

=1 - normalized

Z-score instead

Center on mean:

=(A2-AVERAGE(rng))/STDEV(rng)

Pitfalls & errors

All-equal values. If max = min, the range is 0 and it divides by zero. Guard with IFERROR.

Outliers compress the rest. One extreme value pushes everything else toward 0 — consider clipping or a z-score.

Lock the range. Use absolute refs so rows share one min/max.

Practice workbook

📊
Download the free Normalize Values to a 0–1 Scale practice workbook
A normalize sheet with the 0-100, reverse, and z-score variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I normalize values to a 0-1 scale in Excel?
Use =(value - MIN(range)) / (MAX(range) - MIN(range)). The minimum maps to 0 and the maximum to 1.
How do I scale to 0-100 instead?
Multiply the normalized result by 100.
When should I use a z-score instead of min-max?
Use min-max for a bounded 0-1 scale (scoring, heat maps); use a z-score when you want values centered on the mean and unbounded (outlier detection).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Z-score (standardize) · Percentile rank · Color-scale heatmap

Function references: MIN · MAX