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.
What are histograms?
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.
You can decide yourself how many bins to have and how wide to make them but try to choose values that allow the chart to transmit meaningful information.
Creating a histogram in Excel: Step-by-step instructions
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.
Create an Excel histogram using the add-in
If the add-in is activated, make a table with all your measurement data in one column and your chosen bins in a second one. For the latter, enter an “up to” value. If you’d like to integrate all values from 30 to 34 into one bin, then create a 29 bin and a 34 bin. Everything smaller than 30 will fall into the first bin, and everything that’s larger will fall into a third bin.
To select the bin frequency, now use the add-in. Go to the tab “Data” and click on the button “Data analysis”. From the list that opens, select the option “Histogram”. At this point, Excel will provide you with an input mask. For the “Input Range” select the column containing the measurement data. The “Bin Range” is then the area where you’ve defined the bins. If you’ve labeled the columns in the first line, activate the option “Labels”.
After you’ve decided where the analysis of the data is to be represented (in a new or an existing spreadsheet), Excel will create a frequency analysis for you. In the new table you can now read how much measurement data appears in the respective bins. In order to create the actual histogram, you have to activate the option “Chart Output” in the mask. Now confirm the entry and Excel will immediately create the graph.
Using this method you can only make histograms in Excel with identical bin intervals – that is, bars with the same width. It’s not possible to properly represent an uneven distribution of widths using this method.
Histograms as a type of chart
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.
If you follow the steps described, you will create the histogram in the same way as making other charts in Excel.
Alongside the automatic division carried out by the program, with Excel you can also take advantage of two further interesting possibilities. Either you can define a bin width (container width), and Excel will then determine how many bins are produced by this. Or, you can tell the program how many classes you’d like to have, and Excel will then determine the width of the bars independently. Otherwise, you can also determine overflow and underflow containers. These are the bins that qualify the edges of the histogram, so enter values that you consider to be your desired minimum and maximal values—that is, “everything under this value” and “everything over this value”. Depending on the values collected, you can adopt a division of the bins that makes most sense for you.
Creating a histogram with differing bar widths on Excel
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.
To identify the biggest common factors from different values, you can use the formula “=GGT” on Excel.
Now create the support table: If you have a bin, for example, that the biggest common factor fits into twice over, then list this position twice too. If the factor fits into the bin three times, create three entries accordingly. The bin frequency is only reflected here; the values remain the same.
From this table you can now create a bar chart. Several bars of the same height will now appear alongside each other in the chart. You just need to format the appearance of the chart. To do this, start by right-clicking on one of the bars and selecting the option “Format data rows...” to adjust the gap width. If you set this to 0, the bars will touch each other, just like in a histogram anyway. If you now adjust the coloring of the bars so that matching bars stand out from the others, you’ll have created a genuine histogram.