Make a Histogram in Microsoft Excel



By Jim Gordon, co-author of
Office 2011 for Mac All-in-One For Dummies


The instructions on this page are for Microsoft Excel 2011.


Instructions for Excel 2008 can be found on pages 448 through 455 of Office 2008 All-In-One for Dummies.



A histogram displays the values of a frequency in a proportional graph.

You're going to need some data to work with. Here's the data used in the example below, which you can type into a blank worksheet:

Screen shot of
        histogram data

Follow these steps to make a really great looking histogram.

1. Select the range of cells that includes the data and include the data labels. If you use the sample data above, select the range A1:H2.
2. Click the Charts tab on the Ribbon.
3. In the Insert Chart group on the Riboon, click the Column button.
4. Choose any Clustered chart type. (If you're doing this for a course, choose Clustered Column.)
5. Click once on any of the columns representing values. In this example, values are 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 columns selected:
Selected value
        columns
6. Press the Delete key. The values no longer display and the Frequency columns remain visible. Now your chart should look like this:
Frequency columns
7. Now we need to put the proper values in the x-axis. From the Chart menu choose Source Data. The Source Data dialog opens. If you don't see the Chart option in the menu at the top of the page, you clicked away from the chart. Click anywhere on your chart to activate the Chart menu.
8. Note that the Category (X) 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.
10. Click the OK button to close the Source Data dialog box.
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 this:
Finished histogram
 
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, in step 4 above choose Clustered Column (it's the only chart type your teacher will like), and then complete the steps as shown through step 11 above. Your chart will look like this after step 11:
Clustered Column
To satisfy your teacher, you have to get rid of the gaps between the columns. Here are the additional steps to take:
12. Click once on any of the columns so that they are all selected.
13. Right-click on a column and choose Format Data Series from the pop-up menu. The Format Data Series dialog will open.
14. In the Format Data Series dialog, in the left side click Options.
15. In the Format Data Series dialog, on the right side change the gap width to 0%
16. Click the OK button.
17. Drag the border of the chart to resize it if desired. Your chart should now look like this:
Finished histogram
If you really want to impress your teacher, click the Chart Format tab of the Ribbon and play with the formatting options and chart styles. Here's the same chart with one of the built-in chart styles applied:
Styled histogram


Jim Gordon's MVP Home