Histogram in Excel

 

This example teaches you how to make a histogram in Excel.

1. First, enter the bin numbers (upper levels) in the range C4:C8.

Bin Numbers

2. On the Data tab, in the Analysis group, click Data Analysis.

Click Data Analysis

Note: can't find the Data Analysis button? Click here to load the Analysis ToolPak add-in.

3. Select Histogram and click OK.

Select Histogram

4. Select the range A2:A19.

5. Click in the Bin Range box and select the range C4:C8.

6. Click the Output Range option button, click in the Output Range box and select cell F3.

7. Check Chart Output.

Histogram Parameters

8. Click OK.

Histogram First Result

9. Click the legend on the right side and press Delete.

10. Properly label your bins.

11. To remove the space between the bars, right click a bar, click Format Data Series and change the Gap Width to 0%.

12. To add borders, right click a bar, click Format Data Series, click the Fill & Line icon, click Border and select a color.

Result:

Histogram in Excel

If you have Excel 2016 or later, simply use the Histogram chart type.

13. Select the range A1:A19.

Select Range

14. On the Insert tab, in the Charts group, click the Histogram symbol.

Insert Histogram

15. Click Histogram.

Click Histogram

Result: a histogram with 3 bins.

Histogram with 3 Bins

Note: Excel uses Scott's normal reference rule for calculating the number of bins and the bin width.

16. Right click the horizontal axis, and then click Format Axis.

Format Axis

The Format Axis pane appears.

17. Define the histogram bins. We'll use the same bin numbers as before (see first picture on this page). Bin width: 5. Number of bins: 6. Overflow bin: 40. Underflow bin: 20.

Define Histogram Bins

Result:

Histogram Chart

Recall, we made the following histogram using the Analysis ToolPak (steps 1-12).

Histogram in Excel

Conclusion: the bin labels look different, but the histograms are the same. ≤20 is the same as 0-20, (20, 25] is the same as 21-25, etc.

Tip: you can also use pivot tables to easily create a frequency distribution in Excel.