How to Create a Probability Distribution Graph in Excel (With 2 Examples)

In the modern world, most of our work depends on data or report analysis which helps to make future predictions, business proposals, thoughtful decision making etc. but these analyses would have been impossible with only numbers. This is why we use the probability distribution graph in excel to represent our analysis in a more defined, organized way. In this article, we will learn how to create a probability distribution graph in excel with 2 effective examples.


Download Workbook

Download the sample workbook from here to practice.


What Is Probability Distribution?

The term probability distribution is in general a representation of the frequency distribution of a specific data series. It illustrates the possibility of certain trials under specific conditions among the variable values. The basic rule of the probability distribution is the probability of a value, the higher the frequency is and vice versa.

Probability distribution can be shown with or without a graph based on the function used. It is a very useful Excel feature to project population, performance, climatic forecast, business proposition etc.


Types of Probability Distribution in Excel

There are basic 2 types of probability distribution which have some sub-division under these:

1. Discrete Probability Distribution

    • Binomial
    • Discrete Uniform
    • Poisson

2. Continuous Probability Distribution

    • Normal
    • Continuous Uniform
    • Log-Normal
    • Exponential

2 Examples of Creating a Probability Distribution Graph in Excel

Among all the types of probability distribution, here we will discuss the Binomial and Normal Probability Distribution Graphs in Excel.

1. Make Excel Normal Probability Distribution Graph

The normal probability distribution graph also known as the bell curve is a method to find the value distribution of a dataset. It is generated with the normal distribution function in excel. This function entirely depends on the mean and standard deviation values received from the dataset. Let’s see the process below to create a normal distribution graph in excel:

  • First, prepare a dataset with the information of 10 students’ names and their grades.

Make Excel Normal Probability Distribution Graph

=AVERAGE(D5:D14)

Make Excel Normal Probability Distribution Graph

  • Here, we have the average value of the grades in cells D5:D14.

=STDEV.S(D5:D14)

Make Excel Normal Probability Distribution Graph

  • Now, we have the standard deviation value which represents the deviation from the mean value we calculated earlier.

=NORM.DIST(D5,$E$5,$F$5,FALSE)

Make Excel Normal Probability Distribution Graph

  • Then, copy the same formula in cell G6:G14 by dragging down the corner of cell G5.

Make Excel Normal Probability Distribution Graph

Finally, we have our complete dataset to create the graph on normal probability distribution.

  • Next, sort the Grade and Normal Distribution values from smallest to largest from the Sort & Filter section in the Home tab.

Make Excel Normal Probability Distribution Graph

  • Select the values of Grade and Normal Distribution columns like the image below:

How to Graph a Probability Distribution in Excel

  • Further, select Recommended Charts from the Charts section in the Insert tab.

Make Excel Normal Probability Distribution Graph

  • Consequently, we can see a window named Insert Chart pops up.
  • Here, select any of the Scatter with Smooth Line options from the XY (Scatter) chart in the All Charts section.

  • Finally, we have our graph on normal probability distribution.

Make Excel Normal Probability Distribution Graph

Read More: Plot Normal Distribution in Excel with Mean and Standard Deviation


2. Create Binomial Probability Distribution Graph in Excel

The binomial probability distribution graph is a statistical measure to calculate the probability of the number of successes from a specified number of trials. Follow the steps below to graph the binomial distribution:

  • In the beginning, insert values of the Number of Trials and the Probability of Success in cells C5 and C6 respectively.

Create Binomial Probability Distribution Graph in Excel

  • Secondly, insert the value of each possible Number of Successes in cells B9:B18.

Create Binomial Probability Distribution Graph in Excel

=BINOM.DIST(B9,$C$5,$C$6,FALSE)

Create Binomial Probability Distribution Graph in Excel

  • After that, copy the same function in cells C10:C18 by dragging the corner of cell C9.

  • Now, select the data series of cells B8:C18.

  • Following, go to the Insert tab.
  • Further, select the Recommended Charts option from the Charts section.

  • Consequently, this will open an Insert Chart window.
  • Here, go to the All Charts section.
  • Therefore, select any of the Scatter with Smooth Line options from the XY (Scatter) chart.

  • In the end, you can see the graph based on binomial probability distribution in excel.

Create Binomial Probability Distribution Graph in Excel


Things to Remember

  • An error value #VALUE will return when the Mean or Standard Deviation is not in Number format in the Normal Distribution Graph.
  • When the Standard Deviation ≤0, the NORM.DIST function will return #NUM! error.
  • Each trial in a Binomial Distribution gives only two possible outcomes.
  • In Binomial Distribution, The probability of each outcome remains constant from trial to trial.

Conclusion

Finally, we have learned how to graph a probability distribution in excel with 2 examples here. Let us know if you have more methods or options regarding this. Don’t forget to follow ExcelDemy for excel blogs.


Related Articles

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo