How to Normalize Data in Excel: Min-Max and Z-Score

To normalize data in Excel, pick the meaning you need. Min-max normalization rescales a column to run from 0 to 1 using =(B2-MIN($B$2:$B$101))/(MAX($B$2:$B$101)-MIN($B$2:$B$101)). Z-score standardization converts each value to the number of standard deviations from the mean using =STANDARDIZE(B2, AVERAGE($B$2:$B$101), STDEV.S($B$2:$B$101)). Both are ordinary worksheet formulas, so no add-in is needed. The same steps apply whether you spell it normalise or normalize.

What it is used for in research

Normalizing puts variables measured on different scales on a comparable footing before you compare, plot or combine them. Typical research uses are putting a 0-100 survey score and a 1-7 rating on one axis, preparing inputs for distance-based methods such as cluster analysis, and expressing a measurement as a distance from the group mean.

“Normalize” is used for two different operations in Excel tutorials, and the choice matters:

• Rescaling to 0-1 (min-max). Keeps the shape of the distribution; the smallest value becomes 0 and the largest becomes 1.
• Standardizing to z-scores. Centres the column on 0 and measures every value in standard deviations. Microsoft Support describes the first argument of the STANDARDIZE function as the value you want to normalize.

Two other meanings are out of scope here: database normalization (splitting tables to remove redundancy) and the normal distribution itself. For the latter, see normal and standard normal distribution.

Install or access (official source only)

Nothing needs installing. AVERAGE, STDEV.S, STDEV.P, MIN, MAX and STANDARDIZE are built-in worksheet functions, entered in a cell that starts with an equals sign. The official reference for each is on Microsoft Support (links in the Sources section).

Step-by-step: normalize a column

These steps assume your raw numbers are in B2:B101. Adjust the range to your data. The dollar signs (absolute references) keep the range fixed when you copy the formula down.

Method 1: min-max rescaling to 0-1

1. Put the raw values in one column with no text in the range. In a range reference, MIN and MAX use only numbers and ignore empty cells, text and logical values.
2. In C2, enter =(B2-MIN($B$2:$B$101))/(MAX($B$2:$B$101)-MIN($B$2:$B$101)).
3. Copy C2 down to C101.
4. Check that the smallest raw value gives 0 and the largest gives 1.

This formula is plain arithmetic built from the MIN and MAX functions; it is not a named Excel function. Worked example with hypothetical numbers: for the values 10, 20 and 40, MIN is 10 and MAX is 40, so 20 becomes (20-10)/(40-10) = 0.3333.

Method 2: z-score standardization with STANDARDIZE

1. In E1, enter =AVERAGE($B$2:$B$101) for the mean.
2. In E2, enter =STDEV.S($B$2:$B$101) for the standard deviation of a sample. If your data are the entire population, use =STDEV.P($B$2:$B$101) instead.
3. In C2, enter =STANDARDIZE(B2,$E$1,$E$2).
4. Copy C2 down to C101.

STANDARDIZE takes three required arguments, STANDARDIZE(x, mean, standard_dev), and returns (x – mean) / standard_dev. You can also nest AVERAGE and STDEV.S directly, as in the first paragraph, but helper cells make the calculation easier to audit.

Sample or population standard deviation?

Microsoft documents STDEV.S as treating its arguments as a sample, using the “n-1” method, and STDEV.P as treating them as the entire population, using the “n” method. It also notes that for large samples the two return nearly equal values. Choose STDEV.S when your rows are a sample drawn from a larger group, and STDEV.P when they are the whole group. Whichever you pick, state it in your methods text so the z-scores can be reproduced.

Output and how to read it

Min-max results lie between 0 and 1 inclusive. A value of 0.5 sits halfway between the observed minimum and maximum. They depend entirely on those two extremes, so one new outlier changes every other normalized value.

Z-scores are centred on 0. A z-score of 1 means one standard deviation above the mean; -2 means two below. The official Microsoft example uses x = 42, mean = 40 and standard deviation = 1.5: =STANDARDIZE(A2,A3,A4) returns 1.33333333, because (42-40)/1.5 = 1.3333. For screening extreme values with z-scores, see how to find an outlier in statistics; to turn a z-score into a probability, see how to find a p-value from a z-score.

Troubleshooting

• #NUM! from STANDARDIZE. Microsoft states that if standard_dev is less than or equal to 0, STANDARDIZE returns #NUM!. A column where every value is identical has a standard deviation of 0.
• #DIV/0! from the min-max formula. If MAX equals MIN, the denominator is zero. This follows from the arithmetic: a constant column cannot be rescaled.
• Error values spread. Microsoft notes that error values or text that cannot be translated into numbers cause errors in AVERAGE, MIN and MAX. Fix the source cell and the whole column recovers.
• Text and blanks. In a cell range, AVERAGE, STDEV.S, STDEV.P, MIN and MAX ignore text, logical values and empty cells. AVERAGE does include zeros, so a placeholder 0 for a missing value pulls the mean down. Leave missing values blank instead.
• Results change when you add rows. Extend the ranges so the mean, standard deviation, MIN and MAX reflect the full dataset.

Free alternatives

Tool	Equivalent for z-scores	Source
Microsoft Excel	STANDARDIZE(x, mean, standard_dev)	Microsoft Support, STANDARDIZE function
Google Sheets	STANDARDIZE with the same three inputs: value, mean, standard deviation	Google Docs Editors Help, STANDARDIZE

Google’s help page says the mean can come from AVERAGE and the standard deviation from STDEV or related functions, the same pattern as Method 2. Min-max rescaling uses only MIN and MAX, which are standard spreadsheet functions. For scripted workflows beyond a spreadsheet, see cluster analysis in Python, which covers scaling before clustering.

For related spreadsheet analysis, see crosstabs in Excel.

FAQ

How do I normalize data in Excel?

Use a min-max formula, =(B2-MIN($B$2:$B$101))/(MAX($B$2:$B$101)-MIN($B$2:$B$101)), to rescale to 0-1, or use STANDARDIZE with AVERAGE and STDEV.S to get z-scores. See the step-by-step section above.

Is there an Excel function that normalizes data to 0-1?

Min-max rescaling is not a named function in this guide. Build it from MIN and MAX as shown in Method 1.

What does STANDARDIZE do in Excel?

It returns a normalized value, (x – mean) / standard deviation, for the x, mean and standard deviation you supply. All three arguments are required.

Should I use STDEV.S or STDEV.P to normalize?

Use STDEV.S when your data are a sample and STDEV.P when they are the entire population. For large samples the results are nearly equal.

Why do I get #NUM! when I standardize?

The standard deviation argument is 0 or negative. Check that the column is not constant and that the standard-deviation cell is correct.

Can I normalize several columns at once?

Yes. Write the formula once with absolute references to that column’s range, then copy it across; for the next column, point the references at its own range. Calculate each column’s statistics separately.

Sources (official docs)

Date checked: 2026-10-29.

• Microsoft Support: STANDARDIZE function
• Microsoft Support: AVERAGE function
• Microsoft Support: STDEV.S function
• Microsoft Support: STDEV.P function
• Microsoft Support: MIN function
• Microsoft Support: MAX function
• Google Docs Editors Help: STANDARDIZE