How Do You Normalize Data In Excel

To "normalize" a set of information 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 take the following dataset in Excel:

Raw data in Excel in one column

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

Step 1: Find the mean.

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

Average function in Excel

Stride 2: Find the standard deviation.

Next, nosotros will employ the=STDEV(range of values)role to detect the standard deviation of the dataset.

Standard deviation function in Excel

Footstep 3: Normalize the values.

Lastly, we volition use theSTANDARDIZE(10, hateful, standard_dev)function to normalize each of the values in the dataset.

NOTE:

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

Normalized value = (x – x) / due south

where:

x = data value

x = mean of dataset

southward = standard deviation of dataset

The following prototype shows the formula used to normalize the outset value in the dataset:

Normalize data in Excel

Once we normalize the first value in cell B2, nosotros tin 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:

Normalized data in Excel

At present every value in the dataset is normalized.

How to Interpret Normalized Data

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

Normalized value = (x – 10) / due south

where:

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

If a detail information signal 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 usa how many standard deviations the original data point is from the mean. For example, consider the data signal "12" in our original dataset:

Normalized data formula in Excel

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

Normalized value = (ten – x) / south = (12 – 22.267) / 7.968 =-i.288

This tells us that the value "12" is1.288 standard deviations beneath the hatefulin the original dataset.

Each of the normalized values in the dataset tin can help u.s.a. empathize how close or far a item data value is from the mean. A modest normalized value indicates that a value is close to the mean while a large normalized value indicates that a value is far from the hateful.