How to Calculate Normalized Data in Excel

By David Weedmark

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.

Tip

In Excel, average is synonymous with mean, and standardize is synonymous with normalize.

Step 1

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.

Step 2

Calculate the set's average.

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).

Step 3

Calculate the set's standard deviation.

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).

Step 4

Calculate the normalized value for the first number in the set.

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:

  1. Type A1 for the cell being normalized, then a comma.
  2. Type B$ and then the cell number containing the AVERAGE formula, such as B$15, followed by a comma.
  3. 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).

Tip

Inserting the $ between the cell letter and a number allows you to copy and paste this formula to calculate all the other normalized numbers.

Step 5

Copy and paste the formula to calculate the rest of the normalized values.

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.