How to Calculate Normalized Data in Excel
Use Excel to normalize a range of numbers within a set using the set's average and its standard deviation.
Normalized data refers to any number that is part of a distributed set of numbers that has been adjusted for the mean, or average, of that set and the set's standard deviation.
In Microsoft Excel, you can use the STANDARDIZE formula to normalize any number, provided you also have the average of its set and the standard deviation.
In Excel, average is synonymous with mean, and standardize is synonymous with normalize.
Open a new Excel workbook. Enter a set of data in column A, beginning at cell A2. Type a name for the data in cell A1.
Type Average below the data in column A. Beside the word Average in Column B, type =AVERAGE(). Place the cursor between the parenthesis and highlight the data in column A. Press Enter to calculate the formula. In the screenshot example, the formula is: =AVERAGE(A2:A13).
Type Standard Deviation below the word Average. Tab over to column B and type =STDEV(). Again, place the cursor between the parenthesis, highlight the data cells and press Enter. In the screenshot example, the formula is: =STDEV(A2:A13).
Place the cursor in cell B2, beside the first value in column A. Type =STANDARDIZE() in the cell and place the cursor between the parenthesis. Type three cell coordinates:
- Type A1 for the cell being normalized, then a comma.
- Type B$ and then the cell number containing the AVERAGE formula, such as B$15, followed by a comma.
- Type B$ and the cell number containing the STDEV formula, such as B$16.
Press Enter to calculate the formula, which should look like this: =STANDARDIZE(A2,B$15,B$16).
Inserting the $ between the cell letter and a number allows you to copy and paste this formula to calculate all the other normalized numbers.
Press Ctrl-C to copy cell B2. Highlight all the cells in B2 that are beside a value in the first column and press Ctrl-V to paste the formula into these cells. Now every value in column A has a normalized value in column B.