To “normalize” a set of data values means to scale the values such that the mean of all of the values is 0 and the standard deviation is 1.

This tutorial explains how to normalize data in Excel.

**Example: How to Normalize Data in Excel**

Suppose we have the following dataset in Excel:

Perform the following steps to normalize this set of data values.

**Step 1: Find the mean.**

First, we will use the **=AVERAGE(range of values) **function to find the mean of the dataset.

**Step 2: Find the standard deviation.**

Next, we will use the **=STDEV(range of values) **function to find the standard deviation of the dataset.

**Step 3: Normalize the values.**

Lastly, we will use the **STANDARDIZE(x, mean, standard_dev) **function to normalize each of the values in the dataset.

NOTE:

The

STANDARDIZEfunction uses the following formula to normalize a given data value:

Normalized value = (x – x) / s

where:

- x = data value
- x = mean of dataset
- s = standard deviation of dataset

The following image shows the formula used to normalize the first value in the dataset:

Once we normalize the first value in cell B2, we can hover the mouse over the bottom right corner of cell B2 until a small **+ **appears. Double click the **+ **to copy the formula down to the remaining cells:

Now every value in the dataset is normalized.

**How to Interpret Normalized Data**

The formula that we used to normalize a given data value, x, was as follows:

Normalized value = (x – x) / s

where:

- x = data value
- x = mean of dataset
- s = standard deviation of dataset

If a particular data point has a normalized value greater than 0, it’s an indication that the data point is greater than the mean. Conversely, a normalized value less than 0 is an indication that the data point is less than the mean.

In particular, the normalized value tells us how many standard deviations the original data point is from the mean. For example, consider the data point “12” in our original dataset:

The normalized value for “12” turned out to be -1.288, which was calculated as:

Normalized value = (x – x) / s = (12 – 22.267) / 7.968 = **-1.288**

This tells us that the value “12” is **1.288 standard deviations below the mean **in the original dataset.

Each of the normalized values in the dataset can help us understand how close or far a particular data value is from the mean. A small normalized value indicates that a value is close to the mean while a large normalized value indicates that a value is far from the mean.

Thank you for this information Zach. However, which information can I use to make graphics as histograms using the std dev. I’ll appreciate your help.

Kind regards.

Adrián

This is a great article for standardization, but not normalization. The two terms mean something different.

Fantastic write-up, thank you so much for the clear and concise information!

Your demonstration on normalization here actually explains how to standardize data. Based on your other explanation, standardization, not normalizations, uses MEAN and STANDARD DEVIATION.

Am I missing something here?

how can I normalize it from 0 to 1?

Good stats

This explains standardisation, not normalisation…

HI Zack! I wanna ask if you could help me with normalized freqency in EXcel. I need to do a research: count the numbers of verbs divide it with number of words and then multiple with 1000. Normalized frequency at 1000. Can you help me please?