How to Normalize Data in Excel

Q: How do I normalize data in Excel to a 0 to 1 range?

Use min-max normalization: =(A2-MIN($A$2:$A$11))/(MAX($A$2:$A$11)-MIN($A$2:$A$11)). Lock the MIN and MAX range with dollar signs so it stays fixed, then copy the formula down the column with the fill handle. The smallest value becomes 0, the largest becomes 1.

Q: What is the formula for min-max normalization in Excel?

The min-max formula is (x − MIN(range)) / (MAX(range) − MIN(range)). In Excel, that becomes =(A2-MIN($A$2:$A$11))/(MAX($A$2:$A$11)-MIN($A$2:$A$11)). The absolute references on the range are essential so MIN and MAX evaluate the full dataset for every row.

Q: How do I normalize data in Excel using z-score standardization?

Use =STANDARDIZE(A2, AVERAGE($A$2:$A$11), STDEV.S($A$2:$A$11)), which is identical to =(A2-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11). This rescales the data to a mean of 0 and a standard deviation of 1. Use STDEV.P instead of STDEV.S if your rows are the entire population.

Q: Why does my normalization formula return #DIV/0!?

#DIV/0! appears when every value in the range is identical, because MAX − MIN equals zero (or the standard deviation is zero for z-scores). A constant column has no spread to normalize. Either exclude it from the analysis or wrap the formula in IFERROR(...,0).

To normalize data in Excel to a 0–1 range, use =(A2-MIN($A$2:$A$11))/(MAX($A$2:$A$11)-MIN($A$2:$A$11)) and copy it down the column. The dollar signs lock the MIN and MAX to the full dataset so every row scales against the same minimum and maximum, while the A2 reference shifts row by row.

That single formula is min-max normalization, the most common technique. But “normalize” can mean two different things depending on what you’re doing, and choosing the wrong one quietly distorts your analysis. This guide walks through min-max scaling to 0–1, scaling to a custom range like 0–100, z-score standardization, and decimal scaling, with verified formulas for both Windows and Mac, a worked example, and a troubleshooting section.

Min-max normalization to a 0–1 range

Min-max normalization rescales every value so the smallest becomes 0, the largest becomes 1, and everything else lands proportionally in between. The formula is:

=(x - MIN(range)) / (MAX(range) - MIN(range))

Suppose your raw values sit in A2:A11. In cell B2, enter:

=(A2-MIN($A$2:$A$11))/(MAX($A$2:$A$11)-MIN($A$2:$A$11))

The key detail is the absolute references around the range. $A$2:$A$11 stays fixed when you copy the formula down, so MIN and MAX always evaluate the entire dataset. The bare A2 is relative, so it advances to A3, A4, and so on. Mixing these up is the single most common mistake, and it produces nonsense without throwing an error. The MIN and MAX functions work identically on Windows and Mac, so this formula is fully cross-platform.

To apply it to the whole column, select B2, then double-click the fill handle (the small square at the bottom-right of the cell) or drag it down. If you’re new to that shortcut, see our guide on the Excel fill handle.

Scaling to a custom range (e.g. 0–100)

Min-max output is locked to 0–1, but you often want a different range, such as 0–100 for a score or 1–5 for a rating. The general formula is:

=(x - MIN(range)) / (MAX(range) - MIN(range)) * (newMax - newMin) + newMin

For a 0–100 scale with data in A2:A11, you simply multiply the normalized value by 100:

=(A2-MIN($A$2:$A$11))/(MAX($A$2:$A$11)-MIN($A$2:$A$11))*100

For an arbitrary range like 1 to 5:

=(A2-MIN($A$2:$A$11))/(MAX($A$2:$A$11)-MIN($A$2:$A$11))*(5-1)+1

The first part computes the 0–1 position, then *(newMax-newMin)+newMin stretches and shifts it into your target band. This is the same arithmetic behind converting a percentage change into an indexed score.

Z-score standardization

Standardization (the z-score) rescales data so it has a mean of 0 and a standard deviation of 1. Instead of forcing values into a fixed range, it measures how many standard deviations each value sits from the average. The cleanest formula uses the dedicated STANDARDIZE function:

=STANDARDIZE(A2, AVERAGE($A$2:$A$11), STDEV.S($A$2:$A$11))

This is exactly equivalent to writing the subtraction out by hand:

=(A2-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11)

Use STDEV.S for a sample (the usual case, when your data is a subset of a larger population) and STDEV.P if the rows represent the entire population. The older STDEV function still works and is identical to STDEV.S, but the dot-suffixed versions are the modern standard on both Windows and Mac. For background, see how to calculate standard deviation and the AVERAGE function. Z-scores are also the foundation of outlier detection — our dedicated walkthrough on calculating a z-score covers interpretation in more depth.

Normalization vs. standardization: which to use

These two terms get used interchangeably, but they solve different problems:

Min-max normalization forces data into a bounded range (0–1 or whatever you choose). Use it when you need values inside fixed limits — feeding a chart axis, a progress bar, an image-pixel scale, or a machine-learning model that expects bounded inputs. Its weakness: it’s sensitive to outliers, because a single extreme value sets the MIN or MAX and squashes everything else into a narrow band.
Z-score standardization centers data on its mean and expresses spread in standard deviations. Output is unbounded (typically roughly −3 to +3). Use it when your data is roughly bell-shaped, when you’re comparing variables measured in different units, or when outliers are present — z-scores handle them far more gracefully than min-max.

A rough rule: if you need a hard ceiling and floor, normalize; if you care about relative position within a distribution, standardize.

Method comparison

Method	Formula core	Output range	Best for	Outlier sensitivity
Min-max	`(x-MIN)/(MAX-MIN)`	0 to 1	Bounded inputs, charts, sliders	High
Custom range	`...*(newMax-newMin)+newMin`	Your choice	Scores, ratings	High
Z-score	`(x-AVERAGE)/STDEV.S`	~ −3 to +3	Comparing units, bell-shaped data	Low
Decimal scaling	`x / 10^k`	−1 to 1 (typically)	Quick magnitude reduction	Medium

Decimal scaling (briefly)

Decimal scaling normalizes by moving the decimal point. You divide every value by a power of 10 large enough that the maximum absolute value falls below 1. If your largest absolute value is 4,820, that’s four digits, so divide by 10,000:

=A2/10000

This is the simplest method and keeps the data’s shape intact, but it’s coarse — values rarely fill the full −1 to 1 range. It’s handy for a fast magnitude reduction, less so for rigorous analysis.

A worked numeric example

Say you have five test scores: 40, 55, 70, 85, 100, sitting in A2:A6.

MIN = 40, MAX = 100, so the range (MAX − MIN) is 60.
For the value 70: (70 − 40) / 60 = 30 / 60 = 0.5. It sits exactly halfway.
The full normalized column is: 0.00, 0.25, 0.50, 0.75, 1.00.

Now the z-scores. The AVERAGE is 70 and the sample standard deviation (STDEV.S) is about 23.72.

For 70: (70 − 70) / 23.72 = 0. A value at the mean always has a z-score of 0.
For 40: (40 − 70) / 23.72 ≈ −1.26.
For 100: (100 − 70) / 23.72 ≈ 1.26.

Notice how both methods agree on ranking but express it differently: min-max says 70 is “halfway up,” while the z-score says 70 is “exactly average.”

Applying it down a whole column

Whichever formula you choose, the workflow is the same:

Put your raw data in one column, with a header row.
Enter the formula in the first data cell of an adjacent column, using absolute references for the range.
Press Enter, reselect that cell, and double-click the fill handle to copy it down to the last row of data automatically.

Excel adjusts the relative A2 reference for each row while the $A$2:$A$11 range stays anchored. If you’d rather convert the results into static numbers, copy the normalized column and use Paste Special → Values to drop the formulas.

Text and blanks will break it

Normalization only works on numbers. If your range includes text, error values, or stray characters, MIN, MAX, AVERAGE, and STDEV.S will either ignore them silently (skewing the result) or, in the case of text that looks numeric, produce wrong answers. Blank cells are treated as skipped, not as zero — which is usually what you want, but be aware of it. Before normalizing, clean the source data: remove text from numeric columns, strip stray spaces, and confirm every cell is a true number. Our messy-data cleanup checklist is a good pre-flight routine.

Optional: Power Query

For large or recurring datasets, Power Query (Data → Get & Transform) lets you build a repeatable normalization step instead of dragging formulas. You can add a custom column with the min-max expression and refresh it whenever the source updates. It’s overkill for a one-off, but valuable when the same report runs every month. Power Query is available on Windows and on Microsoft 365 for Mac.

Troubleshooting

#DIV/0! when all values are equal. If every value in your range is identical, MAX − MIN equals 0, and dividing by 0 throws #DIV/0!. The same happens with z-scores when the standard deviation is 0. There’s no meaningful way to normalize a constant column — there’s no spread to scale — so either exclude it or wrap the formula in IFERROR(...,0) to return 0.

Mixing absolute and relative references. If you forget the dollar signs on the range, copying the formula down shifts MIN and MAX along with each row, so every cell is normalized against a different (and shrinking) window. The result looks plausible but is wrong. Always lock the range: $A$2:$A$11.

Outliers distorting min-max. A single extreme value sets the MAX (or MIN) and compresses every other point into a tiny sub-range near 0. If your normalized values cluster suspiciously low, check for an outlier. Either remove it, cap it, or switch to z-score standardization, which is far more robust to extremes. Looking at the spread of a variable before and after — or running a correlation check between two normalized columns — can reveal when a stray value is dominating.

Frequently Asked Questions

How do I normalize data in Excel to a 0 to 1 range?

Use min-max normalization: =(A2-MIN($A$2:$A$11))/(MAX($A$2:$A$11)-MIN($A$2:$A$11)). Lock the MIN and MAX range with dollar signs so it stays fixed, then copy the formula down the column with the fill handle. The smallest value becomes 0, the largest becomes 1.

What is the formula for min-max normalization in Excel?

The min-max formula is (x − MIN(range)) / (MAX(range) − MIN(range)). In Excel, that becomes =(A2-MIN($A$2:$A$11))/(MAX($A$2:$A$11)-MIN($A$2:$A$11)). The absolute references on the range are essential so MIN and MAX evaluate the full dataset for every row.

How do I normalize data in Excel using z-score standardization?

Use =STANDARDIZE(A2, AVERAGE($A$2:$A$11), STDEV.S($A$2:$A$11)), which is identical to =(A2-AVERAGE($A$2:$A$11))/STDEV.S($A$2:$A$11). This rescales the data to a mean of 0 and a standard deviation of 1. Use STDEV.P instead of STDEV.S if your rows are the entire population.

What is the difference between normalization and standardization?

Min-max normalization forces values into a fixed range like 0–1, which is ideal for bounded inputs but sensitive to outliers. Z-score standardization centers data on its mean with unbounded output, making it better for comparing variables in different units or for data containing outliers.

How do I normalize data to a custom range like 0 to 100?

Multiply the 0–1 result by your target span and add the floor: =(A2-MIN($A$2:$A$11))/(MAX($A$2:$A$11)-MIN($A$2:$A$11))*(newMax-newMin)+newMin. For 0–100, that simplifies to multiplying by 100. For 1–5, use *(5-1)+1.

Why does my normalization formula return #DIV/0!?

#DIV/0! appears when every value in the range is identical, because MAX − MIN equals zero (or the standard deviation is zero for z-scores). A constant column has no spread to normalize. Either exclude it from the analysis or wrap the formula in IFERROR(...,0).