Standardization (Or Z-score normalization)

Standardization is a process in which we want to scale our data in such a way that the distribution of our data has its mean as 0 and standard deviation as 1. The mathematical formula for standardization is given as:

X^{'} = \frac{X - X_{mean}}{\sigma_{_{x}}}

where,

• X is the data point, 
• X_mean is the mean of the distribution
• σ_x is the standard deviation of the distribution.

The process of standardization is generally used when we know the distribution of data follows the gaussian distribution.

Method 1: Calculating z-score normalization manually 

Step 1: Calculate the mean/average of the distribution. It can be done using the AVERAGE() function. The mean value comes out to be 161.8 and is stored in the B14 cell.

Step 2: Calculate the standard deviation of the distribution which can be done using the STDEV() function. The standard deviation comes out to be 8.323994767 which is stored in the B15 cell.

Step 3:  For the first data stored in the A2 cell, we will calculate the standardized value as shown in the image given below.

Step 4: After manually calculating the first value, we can simply use the auto-fill feature of Excel to populate the standardized values for all other records.

Note: While calculating the first standardized value in the B2 cell, it should be made sure that the reference address for the B14 and B15 cells should be locked using  Fn+F4 button otherwise an error will be thrown.

Method 2: Calculating Z-score normalization using the STANDARDIZE() function

We can even use the built-in STANDARDIZE() function to find the standardized value of an element. The syntax for STANDARDIZE() function is given as:

=STANDARDIZE(x,mean,std_dev)

Where,

• x is the specific element/range of cells,
• mean is the average/arithmetic mean of all the elements in the record,
• std_dev is the standard deviation of all the elements in the record

Step 1: Calculate the mean/average of the distribution. It can be done using the AVERAGE() function. The mean value comes out to be 161.8 and is stored in the B14 cell.

Step 2: Calculate the standard deviation of the distribution which can be done using the STDEV() function. The standard deviation comes out to be 8.323994767 which is stored in the B15 cell.

Step 3: For the first data stored in the A2 cell, we will calculate the standardized value as shown in the below image.

Step 4: After manually calculating the first value, we can simply use the auto-fill feature of Excel to populate the standardized values for all other records.

The term “normalization” is a popular buzzword among professionals in fields like Machine Learning, Data Science, and Statistics. It refers to the process of scaling down values to fit within a specific range. The term is often misunderstood and is sometimes used interchangeably with “standardization,” which is another statistical concept.

Here, we are going to demystify both of these terms and later we will read how we can implement these techniques on a sample dataset in Excel.