How to Make a Histogram

A histogram is a distribution graph that allows you to see how several measurements fall within set ranges, or bins, of the dependent variable. A histogram is usually depicted as a bar chart, with one bar representing the count of how many measurements fall with a single bin.

Histogram Example — Distribution of Days to Readmission for Cardiovascular Patients within 30 Days

How to Make a Histogram 01

Step 1: Enter Raw Data: Enter your data into a single column. In this example we will be recording the days to readmission by patient. We have 167 observations for this example. (Please see example in Excel Worksheet).

Step 2: Determine Bins: Next you will want to decide how fine you want the increment of your bins. The finer the increment, the more bins, and thus the more bars on your chart. For this example we will choose a bin increment of 5 starting with 5. Enter the title “Bins” in cell C4 and type the bin increments in cells C5 through C10.

How to Make a Histogram 02

Step 3: Calculate Frequency: In the column next to Bins, add a column for the Frequency. Enter the title “Frequency” in cell D4.

Step 4: Click on cell D5, then click the “Formulas Tab” and select “Insert Function”. Select the “Statistical Function” category, scroll down in the box below “Select a function” and select “Frequency”. Click “OK”.

How to Make a Histogram 03

Step 5: In the dialogue box click in the ‘Data_array’ box and go to the spreadsheet page and highlight the data values (A5:A171). Click in the ‘Bins_array’ box and go to the spreadsheet and highlight the bin limits cells (C5:C10). Click “OK”.

How to Make a Histogram 04

Step 6: Copy the array function down to the remaining frequency cells. Highlight the frequency cells D5:D10. Click in the ‘Frequency Cell’ (D5). The formula will appear in the function space in the toolbar.

Click before the = sign in the formula bar. Click Ctrl, Shift, and Enter at the same time. The frequency values will fill in cells D6:D10.

How to Make a Histogram 05

How to Make a Histogram 06

Step 7: Total the frequency column: In this example, we totaled the frequency column by entering the formula: =SUM(D5:D10) in cell D11.

Step 8: In the next column enter a title of Percentage in cell E4. Enter =+D5/$D$11 in cell D5 and copy the formula down to cells D6 through D10.

How to Make a Histogram 07

How to Make a Histogram 08

Step 9: Edit Bins Names: Before you may edit the Bin names to reflect the ranges, you must copy and paste special your data. Highlight your Bins, Frequency, and Percentage Data Cells, C4:E10. Click Ctrl C , then under the “Home” tab click the drop down arrow on Paste on the toolbar, select Paste Special. Click on “Values and Numbers”.

Edit your bin names by clicking cell C5 and changing the value to ‘0 – 5, then click C6 and change to ‘6- 10, and so on until you have edited all the Bins names. (Note: You must enter an apostrophe (‘) prior to the ranges to preserve your calculations.) Highlight cells G5:G10 and under the “Home” tab click on the number format and change cell format to text. Now your bins show the days to readmission by range.

How to Make a Histogram 09

How to Make a Histogram 10

Step 10: Create Chart: Highlight the Bin column C4:C10. Hold the Ctrl key down and highlight the Percentage column E4:E10. Click the “Insert Tab”. Under Charts select “Column”, “Clustered Column”.

How to Make a Histogram 11

How to Make a Histogram 12

Step 11: Add Chart Title: Click the chart and click the “Layout Tab” under “Chart Tools”. Click “Chart Title” and “Above Chart”.

How to Make a Histogram 13

Step 12: Edit Chart title: Click “Chart Title”, highlight the text, and enter the text you want. Change the font size by highlighting the new text, click Font Size under the “Home” tab under “Font” and change to appropriate size for the chart. In this example we chose 10.

How to Make a Histogram 14

Step 13: Clean up chart junk: Click the legend and press the delete key. Click the horizontal axis lines and press the delete key. Click the bars, right click and select “Format Data Series”. Click “Series Options” and change “Gap Width” to 0%.

How to Make a Histogram 15

How to Make a Histogram 16

Share
This entry was posted in Editing Charts & Graphs, Excel Tips & Tricks. Bookmark the permalink.

2 Responses to How to Make a Histogram

  1. Karen says:

    This is great – You always provide helpful tips and valuable information. Thank you, Karen

  2. Pingback: A Revealing Look at Re-admission Rates | Katherine S. Rowell & Associates

Leave a Reply

Your email address will not be published. Required fields are marked *


× 3 = fifteen