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.

How to Normalize Data in Excel?

Normalization (Or Min-Max scaling) of data in Excel

It is the process of scaling data in such a way that all data points lie in a range of 0 to 1. Thus, this technique, makes it possible to bring all data points to a common scale. The mathematical formula for normalization is given as:

X^{'} = \frac{X - X_{min}}{X_{max}-X_{min}}

where,

• X is the data point,
• X_max and X_min are the maximum and minimum values in the group of records respectively.

The process of normalization is generally used when the distribution of data does not follow the Gaussian distribution.

Example:

Let’s have a look at one example to see how can we perform normalization on a sample dataset. Suppose, we have a record of the height of 10 students inside a class as shown below:

Step 1: Calculate the minimum value in the distribution. It can be calculated using the MIN() function. The minimum value comes out to be 152 which is stored in the B14 cell.

Calculating the minimum value using the MIN() function

Step 2: Calculate the maximum value in the distribution. It can be calculated using the MAX() function. The maximum value comes out to be 175 which is stored in the B15 cell.

Calculating the maximum value using the MAX() function

Step 3: Find the difference between the maximum and minimum values. Their difference comes out to be 175 – 152 = 23 which is stored in the B16 cell.

Calculating the difference (Max-Min)

Step 4: For the first data stored in the A2 cell, we will calculate the normalized value as shown in the below video.

Calculating the normalized value for the first element

Step 5: We can manually calculate all values one by one for each data record or we can directly get values for all the other cells using the auto-fill feature of Excel. For this, go to the right corner of the B2 cell until a (+) symbol appears, and then drag the cursor to the bottom to auto-populate values inside all the cells.

Calculating the normalized value for the entire range

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

If we have a close look at the results, we can notice all the values lies in the range 0 to 1.

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.

Calculating the mean value using the AVERAGE() function

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.

Calculating the standard deviation using the STDEV() function

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

Calculating the standardized value for the first element

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.

Calculating the standardized value for the entire range using auto-fill

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.

Calculating the mean value using the AVERAGE() function

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.

Calculating the standard deviation using the STDEV() function

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

Calculating the standardized value for the first element using the STANDARDIZE() function

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.

Calculating the standardized value for the entire range using auto-fill

Conclusion

In conclusion, normalizing data in Excel is an essential skill for anyone working with large datasets, ensuring that your data analysis is accurate and meaningful. From using the Standardize function to applying the Min-Max normalization method, Excel offers a range of powerful tools to help you achieve accurate and normalized data. Remember, effective data normalization in Excel not only saves time but also enhances the overall quality of your data analysis.

How to Normalize Data in Excel – FAQs

How do you normalize data?

To normalize data in Excel, follow these steps:

1. Select the Data: Highlight the range of data you want to normalize.
2. Apply the Formula either using Min-Max Normalization or Z-Score Normalization
3. Copy the Formula: Drag the fill handle to apply the formula to the entire column.

How do I normalize text in Excel?

To normalize text in Excel:

1. Select the Data: Highlight the range of text data you want to normalize.
2. Apply the Formula:
   • TRIM: Removes extra spaces. Use =TRIM(A1).
   • UPPER/LOWER/PROPER: Changes text case. Use =UPPER(A1), =LOWER(A1), or =PROPER(A1).
3. Copy the Formula: Drag the fill handle to apply the formula to the entire column.

What is the best way to normalize data from a wide Excel file?

Use the MIN-MAX normalization method. It scales data to a range between 0 and 1, making it easier to compare and analyze.

How to use the MIN-MAX Normalization method in MS Excel?

Here are the Steps to Use MIN-MAX Methods in Normalization in MS Excel

1. Find Min and Max: Identify the minimum and maximum values in your data range.
2. Apply Formula: Use the formula =(A1 - MIN(range)) / (MAX(range) - MIN(range)) to normalize each value in the range.
3. Copy Formula: Drag the formula across your data range to normalize all values