Histogram in Excel
In this tutorial, you will learn how to make a histogram in Excel.
1. First, type the bin numbers (upper limits) in the cells from C4 to C8.
2. On the Data tab, find the Analysis group and select 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.
4. Select the range A2:A19.
5. In the Bin Range field, click once and choose the cells from C4 to C8.
6. Click the Output Range option button, then click inside the Output Range box and choose cell F3.
7. Check Chart Output.
8. Click OK.
9. Click the legend on the right side of the chart, and then press the Delete key.
10. Properly label your bins.
11. If you want no space between the bars, right-click a bar, go to Format Data Series, and set the Gap Width option to 0%.
12. To apply a border, right-click any bar, select Format Data Series, click the Fill & Line icon, go to Border, and choose a border color.
Result:
If you are using Excel 2016 or a newer version, you can easily create a histogram using the built-in Histogram chart type.
13. Select the range A1:A19.
14. From the Insert tab, under the Charts category, select the Histogram chart.
15. Click Histogram.
Result: a histogram with 3 bins.
Note: Excel automatically calculates the number and width of bins using Scott’s normal reference rule.
16. To format the horizontal axis, right-click it and click Format Axis.
The Format Axis pane appears.
17. Define the histogram bins. We will use the same bin numbers as shown in the first picture on this page. Bin width: 5. Number of bins: 6. Overflow bin: 40. Underflow bin: 20.
Result:
Recall that we created the following histogram using the Analysis ToolPak (steps 1–12).
Conclusion: The bin labels may look different, but the histograms represent the same data. For example, ≤20 means the same as 0–20, and (20, 25] means the same as 21–25.
Tip: You can create a frequency distribution in Excel more easily by using a Pivot Table.
2/10 Completed! Learn more about the analysis toolpak ➝
Next Chapter: Create a Macro