Content

• Steps
• Advice
• Warning

This wikiHow teaches you how to create a column probability distribution (histogram) chart in Microsoft Excel. A probability distribution chart is a column graph that shows frequency data that allows you to calculate metrics, such as the number of people scoring at a certain percentage on a test.

Steps

Part 1 of 3: Data entry

1. 

   Open Microsoft Excel. It has a white "X" symbol on a green background. The Excel spreadsheet collection page opens.
   • On a Mac, this step can open up a new Excel sheet with no data. Once there, skip to the next step.

2. 

   
   
   Create new documents. Click Blank workbook (Blank spreadsheet set) in the upper left corner of the window (Windows), or click File (File) and select New Workbook (Mac).

3. 

   Determine minimum and maximum data point. This is quite important in determining the count for each drawer and the number of drawers required.
   • For example, if your data range spans 17 to 225, the smallest data point would be 17 and the maximum would be 225.

4. 

   
   
   Determine the number of drawers you need. Bucket used to organize data into groups on a probability distribution chart. The easiest way to calculate the number of drawers is to take the largest data point (225 in our example), divide it by the number of data points in the graph (say: 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 with:
   • Sturge's formula: K = 1 + 3.322 * log (N) Inside K is the number of drawers and N is the number of data points; after you find K, round up or down to an integer close to it. Sturge's formula works best for linear or "clean" datasets.
   • Rice recipe: square root of (number of data points) * 2 (For a data set with 200 points, you will need to find the square root of 200 and multiply the result by 2). This formula is best suited for erratic or inconsistent data.

5. 

   
   
   Determine the numbers for each drawer. Now that you know the number of drawers, you can find the best uniform distribution. Counts in each pocket include the smallest and largest data points, which will increase in a linear fashion.
   • For example, if you created data for each pocket of a probability distribution chart that represents the test score, then you would most likely use the increment operator of 1 to represent different scales (e.g. 5, 6 , 7, 8, 9).
   • Multiples of 10, 20 or even 100 are a commonly used criterion for the count per bucket.
   • If there are mutant exception values, you can set them out of the range of the buckets or increase / decrease the count range in each bucket enough to include the exception value.

6. 

   Add data to the column. Enter each data point in a separate cell in the column A.
   • For example, if you have 40 data, you can add corresponding numbers to the word cells A1 come A40.

7. 

   Add the counts in each bucket to column C if you're on a Mac. Starting from cell C1 or lower, enter each number in the box in the box. After you have completed this step, you can proceed to create a probability distribution chart.
   • Skip this step on a Windows computer.
   advertisement

Part 2 of 3: Creating Charts on Windows

1. 

   Select data. Click the top cell in the column A, then press and hold the key ⇧ Shift and click on the last cell containing the data in the column A.

2. 

   Click the card Insert (Insert) is 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 tab Insert.

3. 

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

4. 

   Click the card All Charts (All chart). This tab is at the top of the pop-up window.

5. 

   Click the card Histogram located on the left side of the window.

6. 

   Select the Histogram template. Click the bar graph icon on the left side to select the Histogram template (not the Pareto chart), then click OK. A simple probability distribution chart will be created using the data of your choice.

7. 

   Open the horizontal axis menu. Right-click the horizontal axis (the horizontal axis that contains the number ranges), click Format Axis ... (Horizontal axis format) from the drop-down menu and select the column graph icon in the "Format Axis" menu that appears on the right side of the window.

8. 

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

9. 

   Enter the count distance in each drawer. Enter the value of the spacing between the counters in each pocket in the "Bin width" box, then press ↵ Enter. Excel relies on the data and automatically formats the histogram so that the histogram displays the appropriate numbers in the column.
   • For example, if you use 10-increment buckets, enter 10 come in.

10. 

    Label the chart. This is only necessary if you want to add a title for the axes or the entire chart:
    • Title for the axis Click the mark ＋ in green to the right of the chart, check the box "Axis Titles", click the text box Axis Title to the left or bottom of the graph and then enter the title you want.
    • Title for the chart Click on the text frame Chart Title (Chart Title) is at the top of the histogram graph, then enter the title you want to use.

11. 

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

Part 3 of 3: Creating Charts on Mac

1. 

   Select data and pits. Click the top value cell in the column A to select, then press and hold the key ⇧ Shift at the same time click on the cell C are on the same line as the cell A contains the final value. All data and counts in each respective bucket will be highlighted.

2. 

   Click the card Insert is in the green ribbon at the top of the Excel window.

3. 

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

4. 

   Click on the "Histogram" icon. This set of blue columns is below the "Histogram" heading. A probability distribution chart will be generated according to the data and counts in each bucket already available.
   • Be sure not to click the blue multi-column "Pareto" icon with an orange line.

5. 

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

6. 

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

Advice

• The pockets can be as wide or narrow as you want, as long as they fit the data and don't exceed the reasonable number of slots for this dataset.

Warning

• You need to make sure that the histogram is reasonable before drawing any conclusions.