How to normalize data in Excel

Min-max: scale a column to 0-1

The sheet compares six suppliers on unit price, delivery days and a quality score. The columns are on different scales (dollars, days, points out of 100), so you can't just add them. Normalizing puts each column on the same 0 to 1 scale.

Min-max takes how far each value is above the minimum and divides by the full range. Quality runs from 68 (Orbit Supply) to 91 (Kessler & Co), so Kessler gets 1, Orbit gets 0, and Northline's 82 becomes 0.61. Lock MIN and MAX with $ so they still point at the whole column when you fill down.

In Excel 365 one formula does the column: =(D2:D7-MIN(D2:D7))/(MAX(D2:D7)-MIN(D2:D7)) spills 0.61, 0.26, 1, 0, 0.87 and 0.48. Multiply by 100 for a 0-100 score.

When lower is better: reverse the scale

For price and delivery days, the smallest value is the best, so it should score 1. Swap the order in the numerator: subtract each value from the maximum instead of subtracting the minimum from each value.

Brightway is cheapest at 3.65 and scores 1; Kessler is the most expensive at 5.10 and scores 0. For delivery, Kessler's 3 days scores 1 and Brightway's 9 days scores 0. Don't use 1 minus the normalized value as a shortcut in one column and the swapped formula in another; pick one way and use it everywhere so the scores are easy to audit.

Scale to 1-5 (or any range)

To land on a different range, multiply the 0-1 result by the width of the new range and add the bottom. For 1 to 5: 1 + 4 x the normalized value. Quality becomes 3.4, 2, 5, 1, 4.5 and 2.9.

The general form is new_min + (new_max - new_min) x (x - MIN) / (MAX - MIN), so 0-10 is 10 x the normalized value and 1-10 is 1 + 9 x it.

Divide by max, or z-scores: which to pick

Dividing by the maximum keeps zero meaningful: =D2/MAX($D$2:$D$7) gives 0.9 for Northline, "90% of the best". Scores stay close together (0.75 to 1 here), because the lowest value doesn't become 0. For lower-is-better columns, use MIN/value: =MIN($B$2:$B$7)/B2 gives the cheapest supplier 1 and Kessler 0.72.

A z-score says how many standard deviations a value is from the average: =STANDARDIZE(D2,AVERAGE($D$2:$D$7),STDEV.S($D$2:$D$7)). Quality gives 0.19, -0.74, 1.24, -1.44, 0.89 and -0.16. Z-scores have no fixed range and can be negative, which makes them right for statistics and outlier checks, and awkward for a scorecard people read.

Which to use: min-max for scorecards and dashboards where 0 is worst and 1 is best. Divide by max when "percent of the best" is the story. Z-scores for statistics, or when one extreme value would squash everyone else's min-max score, because min-max depends entirely on the single lowest and highest value.

Combine normalized columns into one score

Once every column is 0-1 with 1 as best, you can average them. The LET formula below normalizes price and delivery (reversed) and quality, averages the three and sorts the suppliers: Kessler & Co 0.67, Vantage 0.65, Northline 0.63, Pemberton 0.57, Brightway 0.42, Orbit Supply 0.39.

Weights change the result, so put them in cells and decide them before you look at the ranking. With 50% on price, 20% on delivery and 30% on quality, Northline leads with 0.63 and Kessler drops to 0.5, because it is the most expensive. SUMPRODUCT of the normalized columns and a weights row does the same in older Excel.

#DIV/0! when every value is the same

Every supplier offers a 2-year warranty, so MAX minus MIN is 0 and min-max divides by zero. A column that doesn't vary can't separate the suppliers anyway, so the cleanest fix is to leave it out of the score. If it must stay, wrap the formula in IFERROR and give everyone the same value: =IFERROR((E2-MIN($E$2:$E$7))/(MAX($E$2:$E$7)-MIN($E$2:$E$7)),1).