Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. With the help of various charts and graphs, you can effectively present survey results. Sometimes, we perform mathematical operations in Excel. They can include Statistics at times. The Normal Distribution comes along whenever dealing with Statistics. This article will show you the step-by-step procedures to Plot Normal Distribution in Excel.
Introduction to Normal Distribution
The Normal Distribution is also known as the Gaussian Distribution. It is a probability distribution of data that is symmetric to the mean. We need the Mean and Standard Deviation of the data first to calculate the Normal Distribution. The Mean is the average value of all data. The Standard Deviation measures the deviation of a particular data from the mean value.
How to Plot Normal Distribution in Excel: Step-by-Step Procedures
We’ll use the NORM.DIST function to find the Normal Distribution in Excel. This function needs 4 arguments.
x: The data for which we’ll determine the normal distribution.
mean: The average value of the dataset.
standard_dev: Measured standard deviation of the dataset.
cumulative: TRUE represents the cumulative distribution function. FALSE represents the probability mass function.
To illustrate, we’ll consider the below dataset as an example. For instance, the dataset contains Student Names and the obtained Marks. Now, go through the steps below carefully to determine the Normal Distribution of the Marks in Excel. Then, we’ll plot it.
STEP 1: Find Mean & Standard Deviation
Let’s calculate the Mean & Standard Deviation in our first step. We’ll apply the AVERAGE function to find the mean. Then we’ll insert the STDEV function to get the standard deviation.
- First, merge the cell range D5:D12.
- Then, in cell C5, type the formula:
=AVERAGE(C5:C12)
- Subsequently, press Enter.
- Thus, it’ll return the Mean.
- Similarly, merge the range E5:E12.
- After that, type the below formula in cell E5:
=STDEV(C5:C12)
- Press Enter.
- Hence, you’ll get the Standard Deviation.
Read More: Plot Normal Distribution in Excel with Mean and Standard Deviation
STEP 2: Create Normal Distribution Chart
We have to find the normal distribution points for each mark.
- Firstly, click cell F5.
- Now, insert the formula:
=NORM.DIST(C5,$D$5,$E$5,FALSE)
- Next, press Enter.
- You’ll get the normal distribution point for cell C5.
- Afterward, use the AutoFill tool to complete the series.
- As a result, it’ll return all the values.
Read More: How to Create a Distribution Chart in ExcelÂ
STEP 3: Plot Normal Distribution in Excel
In this step, we’ll demonstrate how to plot normal distribution in Excel.
- In the beginning, select the Marks range C5:C12.
- Next, choose Home ➤ Editing ➤ Sort & Filter ➤ Sort Smallest to Largest.
- Consequently, a warning dialog box will pop out.
- Check the circle for the second option.
- Then, press Sort.
- See the following picture to understand better.
- Thus, it’ll return the sorted marks column.
- Again, select Insert ➤ Insert Scatter or Bubble Chart ➤ Scatter with Smooth Lines.
- As a result, you’ll get the normal distribution graph.
- The below figure demonstrates the outcome.
STEP 4: Modify Chart
To make the Normal Distribution chart more presentable, we’ll perform some changes.
- Double-click on the Chart Title. Rename it as Normal Distribution Graph.
- Click on the chart. Press the + symbol present beside the chart as shown below.
- Check the boxes for Axes, Axis Title and Chart Title.
- To remove the gridlines, uncheck the Gridlines box.
- Now, double-click the Y-axis.
- As a result, the Format Axis pane will appear.
- Set the Minimum Bounds as 60.
- Similarly, double-click the normal distribution curve line.
- Consequently, you’ll get the Format Data Series pane.
- Check the circle for Built-in Marker Options.
Final Output
After performing all the modifications, our Normal Distribution graph is finally ready to demonstrate. The below figure is the final output.
Read More: How to Create a Probability Distribution Graph in ExcelÂ
Things to Remember
- To avoid errors, sort the data before plotting the normal distribution.
- The mean and standard deviation should be in numbers.
- To avoid #NUM! Error, make sure the standard deviation is greater than zero.
Download Practice Workbook
Download the following workbook to practice by yourself.
Conclusion
Henceforth, you will be able to Plot Normal Distribution in Excel following the above-described procedures. Keep using them. Let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.