How to Normalize in Excel
In statistics, "normalization" refers to the transformation of arbitrary data into a standard distribution, typically a normal distribution with a mean of 0 and variance of 1. Normalizing your data allows you to compare the effects of different factors in your business without regard to scale: the highs are high and the lows are low. In Microsoft Excel, normalization involves a few simple calculations.
Using the STANDARDIZE Function
Open an existing spreadsheet in Microsoft Excel containing the data you wish to normalize.
Select an empty cell and label it "Data Mean." Enter "=AVERAGE(A1:A100)" and replace "A1:A100" with the range of the data you wish to normalize.
Select another empty cell and label it "Data Standard Deviation." Enter "=STDEV(A1:A100)" and replace "A1:A100" with the range of the data you wish to normalize.
Select an empty column and label it "Normalized Data." Enter ="STANDARDIZE(A1:A100,B1,C1)". Replace "A1:A100" with the range of the data you wish to normalize, "B1" with the cell containing the data mean and "C1" with the cell containing the data standard deviation.
- Not all data normalizes well. If your data is highly variable or irregular, transform values to a nonparametric rank rather than normalizing. A nonparametric rank is literally the value's position in an ordered set of your data.
- Goodshoot/Goodshoot/Getty Images