A histogram is a useful tool in analyzing frequency data, offering users the ability to sort data into groupings (called bin numbers) in visual charts, similar to bar charts. Here’s how to make it in Microsoft Excel.
If you want to create a histogram in Excel, you must use Excel 2016 or later. Older versions of Office (Excel 2013 and earlier) do not have this feature.
How to make a histogram in Excel
Put simply, analyzing frequency data involves taking a data set and trying to determine how often the data occurs. You might, for example, look to take a set of student test results and determine how often those results occur, or how often results fall to certain class boundaries.
The histogram makes it easy to take this kind of data and visualize it in an Excel chart.
You can do this by opening Microsoft Excel and selecting your data. You can select data manually, or by selecting cells in your range and pressing Ctrl + A on your keyboard.
With your data selected, select the “Insert” tab in the ribbon bar. The various chart options available to you will be listed in the “Chart” section in the middle.
Click the “Insert Statistics Graph” button to see a list of available charts.
In the “Histogram” section of the drop-down menu, tap the first chart option on the left.
This will insert a histogram chart into your Excel spreadsheet. Excel will try to determine how to format your chart automatically, but you might need to make changes manually after the chart is inserted.
Formatting a Histogram Chart
After entering the histogram into a Microsoft Excel worksheet, you can change it by right-clicking the chart axis label and pressing the “Format Axis” option.
Excel will try to determine the tray (grouping) that is used for your chart, but you may need to change it yourself. For example, to list student test results out of 100, you might prefer to group the results into class boundaries that appear in group 10.
You can leave the Excel bin grouping option by leaving the “By Category” option under the “Format Axis” menu that appears on the right. However, if you want to change this setting, switch to another option.
For example, “By Category” will use the first category in your data range to group data. For a list of student test results, this will separate each result from students, which will not be useful for this kind of analysis.
Using the “Bin Width” option, you can combine your data into several groups.
Referring to the example of our student test results, you can group these into groups of 10 by setting the value of “Bin Width” to 10.
The lower axis range starts with the lowest number. The first bin grouping, for example, is displayed as “[27, 37]” while the largest range ends with “[97, 107],” although the maximum test result remains 100.
The “Trash Amount” option can work in the same way by setting the amount of waste to be displayed on your chart. Setting 10 bins here, for example, will also group results into groups of 10.
For our example, the lowest result is 27, so the first tray starts with 27. The highest number in that range is 34, so the axis label for that tray is displayed as “27, 34.” This ensures the same distribution of bin groupings as possible.
For example student outcomes, this might not be the best choice. However, if you want to ensure that a number of bin groups are always displayed, this is the option you need to use.
You can also divide data into two with excess and less trays. For example, if you want to carefully analyze data below or above certain numbers, you can check to activate the “Overflow Bin” option and set the numbers accordingly.
For example, if you want to analyze a student’s passing level below 50, you can activate and set the “Abundant Tray” number to 50. A tray range below 50 will still be displayed, but data over 50 will be grouped in the appropriate overflow tray instead.
This works in combination with other bin grouping formats, such as by bin width.
The same method applies for underflow bins.
For example, if the failure rate is 50, you can decide to set the “Trash Can” option to 50. Other trash bins will be displayed as usual, but data below 50 will be grouped in the appropriate bins section.
You can also make cosmetic changes to your histogram chart, including changing title and axis labels, by double-clicking the area. Further changes to the color and text and bar options can be made by right-clicking the chart itself and selecting the “Format Chart Area” option.
Standard options for formatting your chart, including changing borders and filling in the options bar, will appear in the “Format Chart Area” menu on the right.