How to Create a Probability Distribution Graph in Excel

Insert information

How to Create a Probability Distribution Graph in Excel Picture 1

Open Microsoft Excel. The app has a white "X" icon on a green background. The Excel spreadsheet collection page opens.

On Mac, this step can open a new Excel sheet with no data. Then skip to the next step.

How to Create a Probability Distribution Graph in Excel Picture 2

Create a new document. Click Blank workbook in the upper-left corner of the window (Windows), or click File and choose New Workbook (Mac).

How to Create a Probability Distribution Graph in Excel Picture 3

Determine the smallest and largest data point. This is quite important in determining the counts in each drawer and the number of drawers required.

For example, if your data range spans 17 to 225, the smallest data point will be 17 and the largest 225.

How to Create a Probability Distribution Graph in Excel Picture 4

Determine the number of drawers you need. Boxes are used to organize data into groups on a probability distribution chart. The easiest way to calculate the number of bins is to take the largest data point (in the example, 225), divide it by the number of data points in the graph (for example, 10) and then round up or down to the nearest integer, however rarely do we have more than 20 or less than 10 numbers. You can use the formula if you are not familiar:

Sturge's formula: K = 1 + 3.322 * log(N) where K is the number of bins and N is the number of data points; after you find K, round up or down to an approximate integer. Sturge's formula is best suited for linear or "clean" datasets.

Rice's formula: cube root of (number of data points) * 2 (for a dataset with 200 points, you'll need to find the square root of 200, then multiply the result by 2). This formula is best suited for erratic or inconsistent data.

How to Create a Probability Distribution Graph in Excel Picture 5

Determine the counts in each drawer. Now that you know the number of bins, you can find the most uniform distribution. The counts in each bin consist of the largest and smallest data points, which will increase in a linear fashion.

For example, if you were to generate data for each box of a probability distribution representing test scores, you would most likely use the increment operator of 1 to represent different scales (e.g. 5, 6) , 7, 8, 9).

Increasing multiples of 10, 20, or even 100 is a commonly used standard for the count in each drawer.

If there are outliers that mutate, you can either put them outside the range of cells or increase/decrease the range of counts in each bin enough to include the outlier value.

How to Create a Probability Distribution Graph in Excel Picture 6

Add data to the column. Enter each data point in its own cell in column A.

For example, if you have 40 data, you can add each corresponding number to cells A1 through A40.

How to Create a Probability Distribution Graph in Excel Picture 7

Add the counts in each drawer to column C if you use a Mac. Starting in cell C1 and below, enter each number in each box into the cell. After completing this step, you can proceed to create the probability distribution graph.

Skip this step on a Windows computer.

Create charts on Windows

How to Create a Probability Distribution Graph in Excel Picture 8

Select data. Click the top cell in column A, then hold down the ⇧ Shift key while clicking the last cell containing the data in column A.

How to Create a Probability Distribution Graph in Excel Picture 9

Click the Insert tab located in the green ribbon at the top of the Excel window. The toolbar near the top of the window will switch to showing options in the Insert tab.

How to Create a Probability Distribution Graph in Excel Picture 10

Click Recommended Charts . This option is located in the "Charts" section of the Insert toolbar. A window will pop up.

How to Create a Probability Distribution Graph in Excel Picture 11

Click the All Charts tab . This tab is at the top of the pop-up window.

How to Create a Probability Distribution Graph in Excel Picture 12

Click the Histogram tab on the left side of the window.

How to Create a Probability Distribution Graph in Excel Picture 13

Select the Histogram template. Click the column chart icon to the left to select the Histogram sample (not the Pareto chart), then click OK. A simple probability distribution graph will be created using the data you choose.

How to Create a Probability Distribution Graph in Excel Picture 14

Open the horizontal axis menu. Right-click the horizontal axis (the horizontal axis containing the data ranges), click Format Axis. from the drop-down menu, and select the column graph icon in the "Format Axis" menu that appears. to the right of the window.

How to Create a Probability Distribution Graph in Excel Picture 15

Check the "Bin width" box in the middle of the menu.

How to Create a Probability Distribution Graph in Excel Picture 16

Enter the number of counts in each drawer. Enter the distance between the counts in each box in the "Bin width" box and then press ↵ Enter. Excel will rely on the data and automatically format it so that the histogram shows the appropriate number in the column.

For example, if you use 10 increments, enter 10 here.

How to Create a Probability Distribution Graph in Excel Picture 17

Label the chart. This is only necessary if you want to add titles to the axes or the whole chart:

Axis Titles - Click the green ＋ sign on the right side of the chart, check the "Axis Titles" box, click on any Axis Title text box located on the left or bottom of the graph and then click on it. Enter the title you want.

A title for the chart - Click the Chart Title text box at the top of the histogram, then enter the title you want to use.

How to Create a Probability Distribution Graph in Excel Picture 18

Save histogram. Press Ctrl+S, select a save location, enter the name you want, and click Save.

Create charts on Mac

How to Create a Probability Distribution Graph in Excel Picture 19

Select data and bins. Click on the top value cell in column A to select it, then hold down the ⇧ Shift key while clicking in cell C in the same row as cell A containing the last value. All data and counts in each respective bin will be highlighted.

How to Create a Probability Distribution Graph in Excel Picture 20

Click the Insert tab located in the green ribbon at the top of the Excel window.

How to Create a Probability Distribution Graph in Excel Picture 21

Click the column graph icon. This option is located in the "Charts" section of the Insert toolbar. A window will pop up.

How to Create a Probability Distribution Graph in Excel Picture 22

Click the "Histogram" icon. This set of blue columns is below the "Histogram" heading. A probability distribution plot will be generated according to the data and the counts in each of the existing bins.

Make sure you don't click the "Pareto" icon, a blue multi-column with an orange line.

How to Create a Probability Distribution Graph in Excel Picture 23

Review the probability distribution chart. Before saving, check if the graph is correct; if not then you need to adjust the counts in each drawer and recreate the chart.

How to Create a Probability Distribution Graph in Excel Picture 24

Save session. Press ⌘ Command+S, enter the name you want to give the file, choose a save location (if necessary) and click Save.

Samuel Daniel

Update 21 February 2022