Quick Reference for Creating Histograms in Excel

Suppose you want to produce a histogram showing the distribution of student grades on a recent exam. The following guide outlines the procedure you would need to follow.

Step 1. Scan your data to get a sense for the overall range of values. For our example, the grades fall between 50 and 100, so this is the range that our bins must span. The next decision is how fine you want the increment of your bins to be – the finer the increment, the more bins, and thus the more bars in our histogram. For our sample data set, a bin increment of 10 seems appropriate. Create a column next to the raw exam score data that shows the bin ranges, and a column to the right of that which shows the maximum values of your bins.

Step 2. Now use the Excel function FREQUENCY to determine how many values fall within each of the bins that you have defined. The FREQUENCY function is an array function, returning values to a range of cells. Follow the following steps to enter the FREQUENCY function:

• Highlight the range of cells which will hold the frequency counts (E2:E7). These will be all of the Frequency Count cells next to the max bin values.

• Choose Insert>Function..., pick the Statistical Function category and scroll down in the box on the right and choose FREQUENCY as the Function name.

• Use the dialogue box to enter the function. With the Data_array box selected, go to the spreadsheet page and highlight the data values (A2:A17). The dialogue box with "roll up" while you highlight these values and then "roll down" when you are done.

• Repeat this process by selecting the Bins_array box and then go out the spreadsheet and highlight the bin limits cells (D2:D7).

• Click OK. The completed formula is seen in the formula bar and the correct count value is seen in the Bin Limit 50 count cell (E2)

Step 3. Now copy the array function down to the other Frequency Count cells. This is a bit different than typical cell copying:

• With the Frequency Count cells still highlighted (E2:E7), click on the FREQUENCY function into the formula bar (i.e., =FREQUENCY(A2:A17,D2:D7))

• Propagate the function by typing Control-Shift-Enter on a PC (type Command-Return on a Mac).

The frequency values should now fill the cells next to the bin increments. Note that your first bin increment, 50, holds all the grades at 50 and below. The next bin, 59, holds measurements from 50-59, and so on.

Step 4. Create a bar chart plotting the frequency count (Column E) as a function of the student grade increments (Column C).