# How to normalize data to a 0-1 scale in Excel

Subtract the minimum and divide by the range: `=(B2-MIN($B$2:$B$7))/(MAX($B$2:$B$7)-MIN($B$2:$B$7))`.

**Excel:** `=(B2-MIN($B$2:$B$7))/(MAX($B$2:$B$7)-MIN($B$2:$B$7))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Candidate | Test score | Normalized |
| Ava | 62 |  |
| Ben | 88 |  |
| Cara | 75 |  |
| Dev | 94 |  |
| Eli | 58 |  |
| Fay | 81 |  |

Formula in C2, filled to C7: `=(B2-MIN($B$2:$B$7))/(MAX($B$2:$B$7)-MIN($B$2:$B$7))`

Result: 0.11; 0.83; 0.47; 1.00; 0.00; 0.64

## How it works

Min-max scaling moves the lowest score (58) to 0 and the highest (94) to 1; everything else lands in between in proportion. Ava's 62 is 4 points above the minimum out of a 36-point range, so 0.11. Scaled columns can be combined fairly even when their units differ.

## Other ways

- `=100*(B2-MIN($B$2:$B$7))/(MAX($B$2:$B$7)-MIN($B$2:$B$7))`: The same scaling stretched to 0–100, which reads better in reports. (Scores from 0 to 100 instead of 0 to 1.)

## Common mistakes

- If every value is the same, MAX − MIN is 0 and the formula returns #DIV/0!.
- One extreme value squeezes everyone else toward 0 or 1. For data with outliers, z-scores are often a better choice.

## Questions

**How do I scale to a different range, like 1 to 10?**

Stretch and shift: `=1+9*(B2-MIN($B$2:$B$7))/(MAX($B$2:$B$7)-MIN($B$2:$B$7))`.

**What is the difference from standardizing?**

Standardizing gives z-scores centered on 0 with a standard deviation of 1; normalizing squeezes into 0 to 1.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/normalize-data-0-to-1