How to Transform Data to Normal Distribution in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

The article will show you how to transform data to the normal distribution in Excel. In statistics, it’s important to imply the normal distribution concept to understand the position of single or multiple data in the dataset. Calculating manually the normal distribution values of data is time-consuming and also a tiresome process. Fortunately, we have a function in Excel that can solve our problem in an instant. The name of this function is NORM.DIST. Let’s go through the rest of the article for a better understanding.


What Is Normal Distribution?

The normal distribution is also known as the Gaussian distribution. It is the probability distribution of a sample space. The distribution of these data is symmetric to the mean. It is also a measurement of how much data close to the mean appears more than data far from the mean. It’s important to know that the mean is zero and the Standard Deviation is 1 in a normal distribution. In practical life, normal distribution helps us to understand the significance of data in a group of data. The equation to find the normal distribution in a set of data is given below.

how to transform data to normal distribution in excel definition

Where,

  σ = Standard Deviation

           x = Sample Data

           μ = Average/Mean


How to Transform Data to Normal Distribution in Excel: 2 Easy Methods

In the dataset, we have information about some students’ performance by score in an examination. We will determine who did an outstanding performance in that exam by transforming their score into normal distribution using Excel.


1. Using Excel Function to Transform Data to Normal Distribution

The best way to transform your data to normal distribution will be to use the NORM.DIST function. It will automatically calculate the normal distribution data by using the aforementioned formula. Let’s go through the process below.

Steps:

  • First, make a column to store the normal distribution values and also select two cells to store your Mean and Standard Deviation.
  • Type the following formula to calculate the Mean and press ENTER. You will see the Mean of the Marks of these students.

=AVERAGE(C5:C11)

how to transform data to normal distribution in excel using function

Here, the AVERAGE function returns the Mean value of the Marks of the students.

  • After that, use the following formula to calculate the Standard Deviation.

=STDEV.P(C5:C11)

The STDEV.P function returns the Standard Deviation of this data.

  • Next, type the following formula in cell D5.

=NORM.DIST(C5,$C$13,$C$14,FALSE)

how to transform data to normal distribution in excel using function

  • Later, hit the ENTER button, and you will see the normal distribution value of the marks in cell C5.

  • After that, use the Fill Handle to AutoFill the lower cells.

how to transform data to normal distribution in excel using Excel function

Thus, you can transform all the data of your dataset to the normal distribution in Excel.

  • If you want to show your data in a normal distribution graph, please select the range C4:D11.
  • After that, go to Insert >> Scatter with Smooth Lines

Thereafter, you will see the data in a normal distribution graph.

how to transform data to normal distribution in excel graph

Keep in mind that, approximately 68% of your data are between the Average ± Standard Deviation range, 95% of your data are between Average ± 2*Standard Deviation and 99.7% of your data will fall between the Average ± 3*Standard Deviation range. That means, 68% of the students have a score between 74.57 ± 10.66 or 63.91 to 85.23. Similarly, you can determine the other ranges for 95% and 99.7%.


2. Applying Mathematical Formula to Transform Data to Normal Distribution

If you are good at statistics and not interested in using the built-in NORM.DIST Function, you can use the mathematical formula of the Normal Distribution. Let’s go through the process below.

Steps:

  • First, make a column to store the normal distribution values and also select two cells to store your Mean and Standard Deviation.
  • Calculate the Mean and Standard Deviation following the process of Section 1.
  • Next, type the following formula in cell D5.

=EXP(-0.5*((C5-$C$13)/$C$14)^2)/($C$14*SQRT(2*PI()))

Here, the formula uses EXP and SQRT functions to generate the mathematical formula of normal distribution transformation. It will return the normal distribution value of your data.

  • Later, hit the ENTER button and you will see the normal distribution value of the marks in cell C5.

how to transform data to normal distribution in excel using mathematical formula

  • After that, use the Fill Handle to AutoFill the lower cells.

Thus, you can transform all the data of your dataset to a normal distribution using the mathematical formula.

  • If you want to show your data in a normal distribution graph, please follow this link in Section 1.

Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

how to transform data to normal distribution in excel


Download Practice Workbook


Conclusion

Suffice it to say, that this article will help you to understand the basic methods of how to transform data to the normal distribution in Excel. If you have any better methods questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.


<< Go Back to Normal Distribution in Excel | Excel for StatisticsLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

2 Comments
  1. Y c13 and c14 was used for =normal dist

    • Dear Bemina, thank you so much for your query. To apply the NORM.DIST function as well as the arithmetic formula for calculating the Normal Distribution, it is required to calculate Mean and Standard Deviation first.

      The formula we have used for Normal Distribution is:
      =NORM.DIST(C5,$C$13,$C$14,FALSE)

      The generic syntax of the NORM.DIST function is:
      =NORM.DIST(x,mean,standard_dev,cumulative)
      Here,
      x = C5 = supplied value to calculate the distribution
      mean = $C$13 = arithmetic average of the distribution
      standard_dev = $C$14 = standard deviation of the distribution
      cumulative = FALSE = Returns probability mass function if the value is FALSE

      Related Articles:

      Thanks for reaching out. We, team ExcelDemy are here to assist you. Please feel free to let us know if you face any other shortcomings.

      Regards,
      MD Tanvir Rahman
      Excel and VBA Content Developer
      Exceldemy, Softeko.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo