How To Normalize Two Data Sets In Excel

In this simple guide, we are going to explain how to normalize data in Excel by using the built in STANDARDIZE function.

This guide volition embrace:

Why we normalize data
Calculating the mean
Calculating the standard deviation
The STANDARDIZE formula
Case output

Why we normalize data

When working with data sets, the overall range and scale of values we are working with tin can vary greatly – peculiarly when working with multiple types of measurements we are looking to compare.

This is where normalization comes in. Past changing the overall scale of the values we are working with it tin become far easier to analyse the results.

By normalizing a set of data, we are looking to convert each value into a z-score.

A z-score is a value that provides information on how far an original value is from the overall mean of the dataset. Every bit the mean is set up to zero in this output, this is washed in terms of positives and negatives. For example, a z-score of i.04 is a value that is in a higher place the boilerplate score, while -0.65 is below the average.

To normalize information into a prepare of z-scores, nosotros will need two boosted values:

Mean: The mean value is simply the overall average value of the numbers we are working with.

Standard Departure: The Standard Deviation is a value that represents the corporeality of variation that sits within a dataset, and provides an indication of how much the numbers differ from one another.

Now that we have summarized the nuts of what each value means and what is required, lets accept a look at how we become most calculating how to normalize data in Excel.

Excel is surprisingly powerful when it comes to statistical analysis, with a series of built in functions to make this procedure quick and easy. At that place are three key steps involved all using simple formulas which volition be covered below. We will as well explicate the manual approaches to these formulas.

Computing the hateful

The mean value is squeamish and easy to calculate in Excel.

Lets say we have a single column worth of values as seen in our example below, all we need to do is utilize the Boilerplate formula beyond all of these cells, with the resulting value being the mean.

In our information prepare we take 13 values, sitting across cells B3 down to B15. The formula in this case will be:

=AVERAGE(B3:B15)

How to normalize data in excel - calculate the mean

Using the average formula in excel makes this a very elementary formula, however we tin can likewise calculate this manually using improver and segmentation.

In this case the formula would be –

=SUM(B3:B15)/thirteen

Either of these formulas volition do the job, although the Boilerplate part is a bit less attempt and has less room for fault by typing in the incorrect number to split by. In this instance we had thirteen values and therefore entered thirteen in the formula.

At present that we have the mean for our prepare of numbers, lets move on to the Standard Deviation

Calculating the Standard Deviation

The approach for calculating the standard difference within our dataset is also quite easy, and like to the procedure used to calculate the mean thanks to Excel's built in STDEV function.

Using the same values as our previous case, the formula we will need to utilize is:

=STDEV.P(B3:B15)

Annotation the .P within this formula. There is also the STDEV.Due south selection which takes a sample of the information rather than the full amount.

The output of this formula will await like this:

How to normalize data in excel - calculate the standard deviation

We now accept both of the required values in our dataset to move on to the next stride – normalizing the data.

The Excel STANDARDIZE function

To normalize our data in Excel, we will be using the built in STANDARDIZE part, which takes our original values, and brings in both the mean and standard deviation of the information we calculated earlier and outputs a z-score. This makes the process quick and easy.

The formula for STANDARDIZE is as follows:

=STANDARDIZE (value, mean, standard_deviation)

To summate the z-scores nosotros simply select the original numbers nether value, and and so select the cells where our mean and standard deviation are sitting.

The formulas and resulting z-scores for our ready of numbers will wait like the below:

How to normalize data in excel - calculate the z-score

Every bit we can see, all 13 of our numbers take now been converted into a far smaller range, with every number being based on the mean. Anything above our hateful of 254 is shown as a positive value, with anything below that as a negative value. This will go far far easier to analyse the data further, and compare to unlike data sets.

At that place are ii additional points worth discussing for this formula:

As seen in the to a higher place image – nosotros have used a dollar sign in a few spots. This is but locking the formula to the F3 and F7 cells, so when we drag the formula downwardly through the rows those values dont modify to F4, F5 etc and look in blank cells or use incorrect values. The dollar sign earlier the letter locks the cavalcade, and the dollar sign before the number locks the row.

Secondly, this can also exist calculated in a slightly more manual mode by simply using the z-score adding. The mathematical formula for a z-score is as follows:

In the above formula, the values represent:

x: The original value

μ: The Mean

σ: The Standard Departure

With this in mind, to calculate the aforementioned z-score of -0.68 that we saw for the value of 54 in cell B3, we could also use the beneath formula:

=(B3-F3)/F7

The STANDARDIZE part is easier, and has less room for error, only its useful to know the process behind this, and allows for quick calculations when using other spreadsheet software outside of Excel where the function is non available.

This sums up our guide on how to normalize data in Excel using the STANDARDIZE function. Every bit nosotros can run into, Excel provides some surprisingly powerful statistical analysis that tin can be performed quickly and easily using a few basic formulas.

For more than tips and tutorials on using Excels congenital in functions be sure to check out our Excel Tips page