Excel: create histogram

In Excel you can clearly create histograms with classes of the same size. Excel also automatically calculates the accumulated frequencies. You can find detailed instructions in this article.

Create histogram in Excel - how it works

1. First activate the analysis functions under "Windows Button"> "Excel Options"> "Add-ins"> "Go"> "Analysis Functions".
2. Then enter your (sorted) original list and the upper class limits.
3. Then click on "Data Analysis" in the "Data" tab (this tool is only available after activating the analysis functions, see point 1).
4. Now select "Histogram" from the list and mark the sorted original list for the input area; for the class area the cells with the upper class limits.
5. Click on the first cell of a new column for the output area and check the "Cumulative frequencies" and "Diagram display" checkboxes. Then create the histogram.
6. At first it looks like a column chart, but right-click on a column and open the formatting> "Row Options"> "Distance Width"> "No Distance".
7. Then you have to add the frame lines under "Frame color" and "Frame type" to be able to differentiate between the individual classes.

Then: format the histogram

1. First of all, the chart looks like a bar chart. Therefore, right-click on a column and open the formatting.
2. Select the "Row Options" tab and change the "Clearance Width" to "No Clearance".
3. Now you only have to select the "Single color line" and a color under "Frame color" and change the frame thickness in 2pt in "Frame type" to be able to distinguish between the individual classes.

You will find the individual steps again in our picture gallery.

Further useful tips and instructions can also be found on the Excel topic page.