Normalizing data converts data into a format that allows further processing and analysis.
Learn How To Normalize Data In Excel
Let’s say your data is from cell A3 to cell A60. Before you normalize data in Excel, you need the average and standard deviation. Find this by choosing an empty cell, which you can label in a neighboring cell as “Mean,” and entering “=AVERAGE(A3:A60)” without quotations.
Change the two numbers (A3 to A60) to suit your specific dataset, like if your data runs from B4 to B55, type “=AVERAGE(B4:B55)”.
Then, find the standard deviation by choosing another empty cell, adding a label in a neighboring cell as before, such as “Standard deviation,” and typing “=STDEV(A3:A60)”. Adjust the cell coordinates accordingly.
Now, 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
Lastly, use 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 the mouse pointer over the bottom right corner of the cell you just filled in, the cursor turns to a thin black cross. Click the mouse button and drag it down the column so that it lines up with your data’s last cell. So you would drag the corner down to cell B60. 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.