Make a histogram with Excel

They look just like normal bar charts, but there’s an important difference: Histograms represent the frequency distribution of data. It can be achieved rather laboriously with mathematical formulas—or, you can use the straightforward tool. Histograms can be made quickly and easily with Excel. In this article, we’ll explain how.

Histograms represent the distribution of frequencies, which is why this kind of chart is mainly used in statistics. With the appropriate graphics, it’s possible to read how often certain values appear in one bin (a group of values). Here, both the width and the height of the bars play a role. The size of a bin can be read from the width of the bar – and this is one of the advantages of a histogram. When you create this kind of chart, you can independently set the size of the bin.

Here’s an example of this. Let’s assume you want to process the results of a throwing competition from a children’s sports day visually using a histogram. The people in charge naturally measure different throws here. You’ll want to process these values visually. To do this, you divide the measured values into different bins. These needn’t be designed equally. In a histogram, the width of the bar makes it clear how big the respective bin is.

It’s a good idea to ensure uniformity, though—at least in the middle part of the chart—as this makes the visual representation easier to understand. For example, one bin could include throws between 30 and 34 meters. Now, the individual data is divided into bins and determines the bin frequency.

To determine the height of the bars, we should also calculate the width. For this, you divide the number of values within one bin by the bin width. In our example with a bin that contains the throws from 30 to 34 meters, the width is 4 (because of the 4-meter range). With 35 to 40 meters, on the other hand, there would be a bin width of 5.

Let's assume that 8 children achieved a result in the area between 30 and 34 meters. The bin size would accordingly be 2 (8 divided by the bin width of 4). In this way, you can construct a rectangle in the histogram with a width of 4 and a height of 2. Somebody looking at the graph would now be able to read the height and width for the number of items, as for this both edge lengths simply need to be multiplied.

Microsoft’s table calculation program doesn’t take all the work off your hands in creating a histogram, but it can save you a lot of the donkey work. For this, Excel uses an add-in, which is an extension of the standard functions, among other things. What you need is the “Analysis ToolPak”. To activate this add-in (or to check if it’s already active), open Options in Excel and the “Add-ins” menu. There are also other possibilities to represent frequency distribution with a chart in Excel, though.

Excel also understands histograms as a type of chart. With this function you have other possibilities to decide how to divide the bins. To be able to use these options, you have to use the list of original measurement data. Highlight these and then click on the histogram button in the “Insert” tab (in the “Charts” area). Based on the data, Excel will then determine how to divide the bins. In this method, too, the bars are distributed evenly. If you now right-click on the x-axis and then select the “Format axis...” option, you will have the choice of extended axis options.

However, in order to map the width of a bin correctly in the chart, you have to take a kind of detour, as there’s no standard function for this. Instead, you have to use a little trick using a support table. You can determine the bins and therefore decide on their width beforehand. From these bin widths you can then identify the biggest common factors. Now, you need to establish how frequently the factors respectively come up in the bins. You also use the data analysis again in order to calculate the frequencies.