Excel is a powerful tool for analyzing data, but the data you work with might not be in the best shape. If there are a lot of differences, it can be hard to figure out how a certain value relates to the mean of the whole set of data and the standard deviation. This is much easier to do with a normalized (or standardized) dataset. In situations like this, being able to normalize data in Excel is a very useful skill to have.
What does “normalized data” mean?
Normalized data is a vague term, but it usually refers to standardized data, where the data is changed by using the mean and standard deviation for the whole set to create a standard distribution with a mean of 0 and a variance of 1. When you look at a normalized dataset, the values that are above the mean are positive, and the values that are below the mean are negative. A value is one standard deviation above the mean if the result is +1, and one standard deviation below the mean if the result is 1.
What You Need to Get Back on Track
When you want to normalize a set of data, you need two more pieces of information. Imagine you have some data running from cell A2 to cell A51. Before you can normalize data in Excel, you need to know its average (also called the “arithmetic mean”) and standard deviation. Choose an empty cell that you can label “Mean” in a nearby cell and type “=AVERAGE(A2:A51)” without the quotes. This will give you the average of the data.
This uses the example data that goes from cell A2 to cell A51. Change these two numbers to fit the data you’re working with. If your data goes from B4 to B55, for example, type “=AVERAGE(B4:B55)” instead.
Find the standard deviation by picking another empty cell, writing “Standard deviation” in a neighboring cell like you did before, and typing “=STDEV(A2:A51)” into it. Change the cell coordinates so that they work with your data.
For the rest of the examples, imagine that you’ve put your mean in cell C2 and your standard deviation in cell D2. Change these numbers in the next formulas to the mean and standard deviation from the cells they are in.
Normalize Data in Excel
The Standardize function is the last step in the process of normalizing data in Excel. This function has three “arguments,” or pieces of information, with the following format: (value, mean, standard deviation).
Type “Normalized data” or another label in cell B1 or in the column next to your data or somewhere else convenient, so that the rows match up. Type “=STANDARDIZE(A2, $C2, $D2)” in cell B2 to tell Excel to use the mean in cell C2 and the standard deviation in cell D2 to normalize the data point in cell A2. The “$” signs make it easier to move the same formula down in the next step.
Move your mouse pointer over the bottom right corner of the cell you just filled in so that the cursor changes to a thin black cross. Click the corner and hold down the mouse button while you drag it all the way down the column until it lines up with the last cell of your data. In the example, you would move the corner down to cell B51. This makes a copy of the formula in each cell, so the row of the cell can change where the data comes from. However, Excel will still get the mean and standard deviation from the same place.