How to Calculate Normalized Data in Excel

Excel is a powerful tool for analyzing data, but the data you work with might not be in the ideal form. If there's a lot of variation, it can be hard to relate any particular value to the mean of the whole dataset and the standard deviation. A normalized (or standardized) dataset makes this much easier to do. Finding out how to normalize data in Excel is an invaluable skill in situations such as this.

Image Credit: Rawpixel/iStock/GettyImages

What Is Normalized Data?

Normalized data is a loosely defined term, but in most cases, it refers to standardized data, where the data is transformed using the mean and standard deviation for the whole set, so it ends up in a standard distribution with a mean of 0 and a variance of 1. When you're looking at a normalized dataset, the positive values represent values above the mean, and the negative values represent values below the mean. A result of +1 means that a particular value is one standard deviation above the mean, and −1 means it is one standard deviation below the mean.

What You Need for Normalization

When you're looking to normalize a set of data, you need two additional pieces of information. Imagine you have some data running from cell A2 to cell A51. Before you normalize data in Excel, you need the average (or "arithmetic mean") and standard deviation of the data. Find the average of the data by choosing an empty cell, which you can label in a neighboring cell as "Mean," and entering "=AVERAGE(A2:A51)" without quotations.

Note that this uses the example data running from cell A2 to A51. Change these two numbers to suit your specific dataset. For example, if your data runs from B4 to B55, type "=AVERAGE(B4:B55)" instead.

Find the standard deviation by choosing another empty cell, adding a label in a neighboring cell as before, such as "Standard deviation," for example, and typing "=STDEV(A2:A51)" into it. Adjust the cell coordinates to suit your dataset.

For the remainder of the examples, imagine that you've added your mean into cell C2 and your standard deviation into cell D2. Replace these numbers in subsequent formulas with the cells that contain your mean and standard deviation.

Normalize Data in Excel

The final stage of how to normalize data in Excel involves the Standardize function. This function has three "arguments" or bits of information inside it with the format: STANDARDIZE(value, mean, standard deviation).

Type "Normalized data" or another label into cell B1 or the column beside your data or another convenient place, so that the rows match up. In cell B2, type "=STANDARDIZE(A2, $C$2, $D$2)" to tell Excel to normalize the data point in cell A2 using the mean in cell C2 and the standard deviation in cell D2. The "$" signs make it easier to drag the same formula down in the next step.

Hover your mouse pointer over the bottom right corner of the cell you just filled in, so the cursor turns to a thin black cross. Click the corner and hold the mouse button down before dragging it all the way down the column so that it lines up with the last cell of your data. In the example, you would drag the corner down to cell B51. This duplicates the formula in each cell, allowing the input data location to change to match the row of the cell but telling Excel to still take the mean and standard deviation from the same place.

references