How to Normalize Data in Excel: Min-Max Scaling 0 to 1

To normalize data in Excel, you rescale a column so its smallest value becomes 0 and its largest becomes 1. This is called min-max scaling, and it takes one formula built from MIN and MAX. It is useful when you want to compare or combine numbers measured on different scales, such as test scores and years of experience. This guide shows the formula, how to fill it down safely, and how to handle the common problems.

What does it mean to normalize data?#

Normalizing puts numbers from different scales on the same scale. A score out of 100 and a salary in thousands can't be added fairly, but two columns that both run from 0 to 1 can.

Min-max normalization uses this formula:

normalized = (value − minimum) ÷ (maximum − minimum)

• The minimum becomes 0, because value − minimum is 0.
• The maximum becomes 1, because the top and bottom of the fraction are equal.
• Everything else lands in between, in the same order as before.

The gaps between values keep their proportions. A value halfway between the minimum and maximum becomes 0.5.

How to normalize a column from 0 to 1#

The example uses six candidates' test scores in A1:B7:

To build column C:

1. Click C2 and type:

   Excel formula

   =(B2-MIN($B$2:$B$7))/(MAX($B$2:$B$7)-MIN($B$2:$B$7))

2. Press Enter. The result is 0, because 40 is the lowest score.

3. Select C2:C7 and fill down (Windows: Ctrl+D; Mac: ⌘D).

For Chen, the formula works out (70 − 40) ÷ (100 − 40) = 30 ÷ 60 = 0.5. The minimum is 40, the maximum is 100, and the range is 60. The MIN function guide and MAX function guide cover both functions in detail.

The brackets matter. Without them, Excel divides before it subtracts and gives a wrong answer.

To check your work, look at the new column as a whole. =MIN(C2:C7) should return exactly 0 and =MAX(C2:C7) exactly 1. Every other value should sit between them, and the order of the rows should be the same as in the original column: Dina is still the highest and Amara the lowest. If any value is below 0 or above 1, a range wasn't locked.

Why the $ signs matter#

The $ signs lock the range so it stays B2:B7 in every row. B2 has no $, so it changes to B3, B4 and so on as you fill down.

If you forget the $ signs, the range shifts too. In C3 the formula would look at B3:B8, which skips Amara's 40 and includes an empty cell, so the minimum and maximum would be wrong.

To add the signs quickly, click inside the range in the formula bar and press F4 (Mac: ⌘T). The guide on absolute references and the dollar sign explains the full rule, and the absolute references lesson lets you practise it.

A neat option is to calculate the minimum and maximum once, in their own cells. If F1 holds =MIN(B2:B7) and F2 holds =MAX(B2:B7), the formula becomes =(B2-$F$1)/($F$2-$F$1), which is shorter and easier to check.

How to scale to 0–100 or any other range#

Multiply by 100 for a 0 to 100 scale:

=(B2-MIN($B$2:$B$7))/(MAX($B$2:$B$7)-MIN($B$2:$B$7))*100

Chen becomes 50. For any other range, use this pattern:

new_min + normalized × (new_max − new_min)

For a 1 to 10 scale, that is =1+C2*9. Chen's 0.5 becomes 5.5.

When lower is better: reverse the scale#

Sometimes a small number is the good one, such as cost, delivery days or error count. Flip the formula so the lowest value scores 1:

=(MAX($B$2:$B$7)-B2)/(MAX($B$2:$B$7)-MIN($B$2:$B$7))

Now the smallest value returns 1 and the largest returns 0. This is useful when you combine several measures into one score. For example, if column C is a normalized test score and column D is a reversed, normalized cost, a weighted score could be =0.6*C2+0.4*D2.

Common problems when you normalize data#

Min-max scaling is simple, but a few things can trip it up:

• #DIV/0! error. If every value is the same, MAX − MIN is 0. Guard against it: =IF(MAX($B$2:$B$7)=MIN($B$2:$B$7),0,(B2-MIN($B$2:$B$7))/(MAX($B$2:$B$7)-MIN($B$2:$B$7))).
• Outliers. One extreme value squashes everything else into a small part of the scale. If the top score were 400, most results would fall below 0.1.
• New data. Adding a value outside the old range changes the minimum or maximum, so every normalized number changes. That is expected, but be careful when you compare old reports with new ones.
• Blanks and text. MIN and MAX skip text and empty cells, but the formula in a blank row returns a negative number. Use =IF(B2="","",…) for rows that may be empty.

Normalization vs standardization (z-scores)#

Standardization is a different rescaling. It turns each value into a z-score: how many standard deviations it is above or below the average. Z-scores have no fixed 0 to 1 limits.

=STANDARDIZE(B2,AVERAGE($B$2:$B$7),STDEV.S($B$2:$B$7))

For Dina's 100, the average is about 68.3 and the sample standard deviation about 21.6, so the z-score is about 1.47. Use z-scores when you care about distance from the average. Use min-max when you need a fixed scale. The guide to standard deviation in Excel explains STDEV.S and STDEV.P.

If you only need to know where a value sits in the list, not how far apart values are, a percentile or quartile or a rank may be the simpler choice.

Practise it#

Try the exercise on this page to normalize Chen's score yourself. Then practise AVERAGE, MIN and MAX in a live spreadsheet, and use the absolute references lesson to make sure your ranges stay locked when you fill a formula down. Each lesson checks your answer instantly. The guide to AVERAGE, MIN and MAX is a good refresher too.