See also: our Excel Scatterplot page and our Basic Statistics page

1. This example uses the following simple data set (quiz scores for twenty students): 5, 7, 8, 3, 7, 7, 1, 9, 6, 8, 5, 6, 7, 8, 7, 9, 6, 8, 6, 6.   To construct a histogram with five bars:
2. Type numbers in column A, starting at A1.  Press ENTER after each number.  
3. In column B, starting at B1 type 2, 4, 6, 8. Note that it is not necessary to write 0 or 10.
4. Because the histogram will have 5 separate bins, select 5 contiguous cells, for example  c3:c7.
   1. Click in the formula bar (the long white bar with the equals sign) and type  =frequency(a1:a20, b1:b4). The first array contains the data; the second array contains the bin separators.
   2. Press Control-Shift-Enter simultaneously.  You will see an array of 5 numbers.
5. To draw the graph, you can use the chart wizard: Highlight the number in column C.  Click on the chart icon at the top of the page.  Choose columns.  Click on "next".  A bar graph will appear in the window. To make the graph comprehensible to others, add some titles by clicking on "next".  For titles, type "histogram of quiz scores".  For category, type "quiz score".  For value on the y axis, type "quiz score frequency".  Click "finish".
6. The graph that you see now is technically a bar graph, NOT a histogram, because there are spaces between all the bars.  Histograms should not have spaces between the bars unless there are categories that have no observations.  To rectify this, double click on one of the bars.  Another window will appear.  Click on "options" and change the gap width to 0.  Then click "OK".  Now it’s a legitimate histogram.

How to construct a histogram in Excel (with Data Analysis Toolpack)

1. In my example, my list consists of 20 numbers that span from 0 to 10. I want to construct a histogram with 5 bars, each corresponding to an interval of length two. My list resides at A1 to A20. My intervals are: (0 to 2), (2 to 4), (4 to 6), (6 to 8), (8 to 10).
2. I make a separate list of the endpoints of each of the intervals: starting at B5 and going down: B5 = 2, B6 = 4, B7 = 6, B8 = 8, B9 = 10. In B4 I put a title: Interval.
3. In the Tools Menu, I choose Data Analysis…. A window comes up, and I choose Histogram.
4. The first open box is listed Input Range. Select from A1 to A30.
5. The second open box is listed as Bin Range. Click in the open box. Select from B5 to B9. Don’t include the title.
6. If you want the histogram chart on this same page, click Output Range and enter a cell number, such as D4. The chart will take as many rows as you have bins and 2 columns. If you don’t want the chart on the same page, choose New Worksheet Ply.
7. Click the box labeled Chart Output. If you don't click this, you won't get a chart - you'll have to make your own. (This is not hard, simply select the frequencies and press the Chart Wizard icon (the graph). 
8. It's not officially a histogram unless the bars are scrunched together, with no spaces in between them. To get the bars right next to each other, click on the finished graph. You will see little squares in each bar on the graph. Double click on one of them and a window will appear. Click on options. Change gap width to 0. Press Okay.

Go to: