Make a Histogram in Microsoft Excel
2016 for Mac
A histogram displays the
frequency values in a proportional graph.
You're going to need some data to work with. Here's the data used
in the example below.. Type this into a blank worksheet:
Follow these steps to make
a really great looking histogram.
1. Select any cell within the range of cells that includes
2. Click the Insert tab on the Ribbon.
3. In the Charts group on the Ribbon, click the Recommended
4. Choose Clustered Column chart type.
5. Click once on any of the columns measuring Value. In this
example, the Value row is represented by the taller columns.
Little round "handles" will appear on all of the columns to
indicate they are selected. Your chart should look something like
this with the Value measure selected:
6. Press the Delete key. Values no longer display and Frequency
columns remain visible. Now your chart should look like this:
7. Next, we need to put the proper values in the x-axis. From the
Chart Design tab of the Ribbon click the Select Data
button. The Select Source Data dialog opens. If you don't see the
Chart Design tab in the Ribbon, you clicked away from the chart.
Click anywhere on your chart to activate the Chart Design tab.
8. Note that the Horizontal (Category) axis labels field in the
dialog is blank. We need to fill this in. Click the little
button to the right of the empty field.
9. Drag over the cell range that has your values, but do
not include the data label. Then press the Return key.
Using our example, you would select the range B2:H2. Excel fills
in the dialog box for you, but you might have to click into the
empty field to get the display to refresh.
10. Click the OK button to close the Source Data dialog
11. You can click on the Frequency label and press delete if you
want to tidy things up. Your finished chart will look about like
If you're making a histogram for
a course, your instructor may be anal-retentive. If
you're unfortunate enough to have one of these ultra-picky types,
you're not done yet. To make your teacher happy you'll have to get
rid of the gaps between the bars.
Here are the additional steps to take:
12. Click once on any of the columns so that they are all
13. Right-click on a column and choose Format Data Series from the
pop-up menu. The Format Data Series pane will open.
14. In the Format Data Series pane, adjust the Gap width to 0%
15. Click the OK button.
Your chart should now look like this:
If you really want to impress your teacher, apply different
formatting options. Here's the same chart after formatting was
changed. Well, maybe this isn't better. I am sure you can do a
Alternative Methods for making
Mike Middleton has a free add-in
that makes Histograms:
Data Bucket Chart. Here's how to try it:
1. On the Insert tab of the Ribbon click the Add-ins
2. Click the Store button
3. Click into the Search field and type Histogram then
4. Click the Buy button
5. Follow the instructions to add this add-in to your add-ins