How to Create Gaussian Distribution Chart in Excel

Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as needed and make databases for the future. Moreover, one interesting thing is that we can create a Gaussian distribution chart easily in Excel. However, I have used Microsoft Office 365 for demonstration, and you can use other versions according to your preferences. In this article, I will show you a step-by-step procedure to create a Gaussian Distribution Chart in Excel. Hence, read through the article to learn more and save time.

What is Gaussian Distribution Chart?

Usually, the Gaussian Distribution Chart is a graph that represents the normal distribution of a variable. Additionally, this is known as the Normal Distribution Curve or Bell Curve. In our nature, we see this distribution everywhere. For instance, if we survey marks from an exam, we will notice that most of the numbers are in the middle. However, the peak point of this curve signifies the mean of the distribution and it is lower on both sides. Moreover, this denotes the probability, which will be much lower for the extreme values (i.e. highest or lowest).

The features of the Gaussian Distribution Chart are –

First of all, 1 standard deviation of such mean is where 68.2% of the distribution falls.

Next, 95.5% of the distribution lies within the range of the average‘s two standard deviations.

Finally, 99.7% of the distribution is contained within a range of three standard deviations from the mean.

How to Create Gaussian Distribution Chart in Excel: Step-by-Step Procedure

Often, we need to create a Gaussian distribution chart for certain business analytics, and the process becomes more interesting with Excel. However, the task is easy and simple. But you will need an arrangement in order to perform the operation properly. Hence, read through the steps below in order to complete the task. In pursuit of these, I am going to use AVERAGE, STDEV.P, and NORM.DIST functions. In general, the AVERAGE function returns the average of the numbers it takes as arguments. On the other hand, the STDEV.P function also takes a series of numbers as arguments and returns the standard deviations. Additionally, while the NORM.DIST function takes a value from a range of data, mean and standard deviation for the dataset, and a boolean value as its arguments and returns the normal distribution of the number in the number.

📌 Step 1: Select Dataset

For the purpose of demonstration, I have used the following sample dataset. Here, the dataset contains information about the Increase in Sales of each Employee in two consecutive months. Here, I am going to make a Gaussian distribution chart for this increase in sales.

📌 Step 2: Calculate Mean and Standard Deviation

Initially, select cell D16 and write down the following formula in order to find the Mean increase in sales.

=AVERAGE(C5:C14)

Then, press Enter.

Similarly, select cell D17 and insert the formula mentioned below to calculate the standard deviation.

=STDEV.P(C5:C14)

Finally, hit the Enter key to get the standard deviation.

📌 Step 3: Determine Normal Distribution

In the beginning, click on cell D5 and insert the following formula.

=NORM.DIST(C5,$D$16,$D$17,FALSE)

In the end, hit the Enter key and utilize the AutoFill tool in order to apply the formula to the entire column.

📌 Step 4: Create Gaussian Distribution Chart

Firstly, select cell range D5:D14.
Secondly, click the Insert tab.
Thirdly, click on the Scatter command from the Charts group of commands.
Fourthly, choose Scatter with Smooth Lines.

Final Output

Last but not least, you will get your desired Gaussian Distribution Chart in Excel. However, the chart will appear as the image below after some modifications.

💬 Things to Remember

First of all, it is better to sort the data before plotting the normal distribution. Else, an irregular curve may occur.
Then, you need to select the chart to access the Chart Element menu and other chart editing options.
Next, you can modify the chart according to your personal preference.
Afterward, the mean and standard deviation of the data must be numeric. Else, it will show the #VALUE error.
Additionally, when you use the function carefully give all the required parentheses. Also, when you use the DIST function, you’ve to make mean and standard deviation as absolute cell references.
At last, The standard deviation must be greater than zero. Or else, it will show you the #NUM! error.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

Gaussian Distribution Chart.xlsx

Conclusion

These are all the steps you can follow to create a Gaussian distribution chart in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

<< Go Back to Excel Distribution Chart | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!