How to Create Gaussian Distribution Chart in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

excel gaussian distribution chart


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.

excel gaussian distribution chart sample dataset


📌 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)

Calculate Mean and Standard Deviation to Create Gaussian Distribution Chart in Excel

  • 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)

Determine Normal Distribution to Create Gaussian Distribution Chart in Excel

  • 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.

Create Gaussian Distribution Chart in Excel


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.

excel gaussian distribution chart


💬 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.


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.


Related Articles


<< Go Back to Excel Distribution Chart | Excel Charts | Learn 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.
Mehedi Hasan
Mehedi Hasan

Mehedi Hasan, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a profound passion for research and innovation, he actively engages with Excel. In his capacity, Mehedi not only adeptly tackles intricate challenges but also showcases enthusiasm and expertise in navigating tough situations with finesse, underscoring his unwavering dedication to consistently delivering exceptional and high-quality content. He... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo