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

In this article, we’ll explore how to transform data into a normal distribution using Excel. Understanding the normal distribution concept is crucial in statistics, as it helps us grasp the relative position of individual or multiple data points within a dataset. Manually calculating normal distribution values can be time-consuming and tedious, but fortunately, Excel provides a function called NORM.DIST that streamlines this process. Let’s delve into the details.

What Is Normal Distribution?

The normal distribution, also known as the Gaussian distribution, represents the probability distribution of a sample space. Key characteristics of the normal distribution include:

• Symmetry around the mean (average).
• A mean of zero.
• A standard deviation of 1.

The equation to find the normal distribution in a set of data is given below.

Where,

  σ = Standard Deviation

           x = Sample Data

           μ = Average/Mean

In practical terms, the normal distribution helps us understand the significance of data within a group. Now, let’s look at how to find the normal distribution for a set of data.

Dataset Overview

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.

Method 1 – Using Excel’s NORM.DIST Function

• Create a Column for Normal Distribution Values:
  • Set up a new column to store the normal distribution values.
  • Select two cells to store the Mean (μ) and Standard Deviation (σ).
• Calculate the Mean:
  • Enter the following formula to calculate the Mean (average) of the student marks (assuming the marks are in cells C5 to C11):

• • • This function returns the Mean value.

• Calculate the Standard Deviation:
  • Enter the following formula to calculate the Standard Deviation:

• • • The STDEV.P function returns the Standard Deviation for the data.

• Compute the Normal Distribution Value:
  • In cell D5, enter the following formula:

• • • Here, C5 represents the student’s score, $C$13 is the Mean, and $C$14 is the Standard Deviation.

• Press ENTER to see the normal distribution value for the student’s mark in cell C5.

• AutoFill for Other Cells:
  • Use the Fill Handle to AutoFill the formula down to cover all data points.

• Visualize the Data in a Normal Distribution Graph:
  • Select the range C4:D11.
  • Go to Insert > Scatter with Smooth Lines to create a normal distribution graph.

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

Interpretation:

• Approximately 68% of the data falls within the range of Mean ± Standard Deviation.
• About 95% of the data lies within Mean ± 2 * Standard Deviation.
• Nearly 99.7% of the data falls within Mean ± 3 * Standard Deviation.

For example, if the Mean is 74.57 and the Standard Deviation is 10.66, a student’s score between 63.91 and 85.23 is within the 68% range.

Remember to adjust these ranges accordingly for 95% and 99.7% confidence levels.

Method 2 – Applying Mathematical Formula to Transform Data to Normal Distribution

If you’re comfortable with statistics and prefer not to use the built-in NORM.DIST function, you can apply the mathematical formula for normal distribution. Let’s walk through the process step by step:

• Prepare Your Data:
  • Create a new column to store the normal distribution values.
  • Select two cells to store the Mean (μ) and Standard Deviation (σ).

• Calculate the Mean and Standard Deviation:
  • Follow the process outlined in Section 1 to compute the Mean and Standard Deviation for your dataset.

• Use the Mathematical Formula:
  • In cell D5, enter the following formula:

• Here’s what each part of the formula does:
  • EXP calculates the exponential value.
  • SQRT computes the square root.
  • The expression within the parentheses represents the z-score (standardized value).
  • The entire formula yields the normal distribution value for your data.

• View the Result:
  • Press ENTER to see the normal distribution value in cell C5.

• Use the Fill Handle to AutoFill the formula down to cover other data points.

• Graphical Representation (Optional):
  • If you want to visualize your data in a normal distribution graph, refer to the instructions in Section 1.

Practice Section

Herewith the dataset of this article so that you can practice these methods.

Download Practice Workbook

You can download the practice workbook from here:

<< Go Back to Normal Distribution in Excel | Excel for Statistics | Learn Excel