How to create a histogram in Excel

Although they look just like normal bar graphs, his­to­grams are used to show the frequency dis­tri­bu­tion of data. While you can do this with a math­em­at­ic­al formula, Excel offers a straight­for­ward tool for creating his­to­grams. In this article, we’ll explain how to make a histogram in Excel.

How to make a histogram in Excel quick guide

1. Select the data in your Excel document that to create a histogram from.
2. Click on Insert, then on the bar chart symbol and select Histogram.
3. A histogram will now be generated in your Excel worksheet.

What are his­to­grams?

His­to­grams provide a visual summary of frequency dis­tri­bu­tions, which is why this kind of chart is mainly used in stat­ist­ics. With the ap­pro­pri­ate 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 ad­vant­ages of a histogram. When you create this kind of chart, you can in­de­pend­ently set the size of the bin.

How to make a histogram in Excel step by step

Excel doesn’t take all the work off your hands when creating a histogram, but it can save you a lot of time. The in­struc­tions shown here apply to Excel versions from Office 2016 onwards and Microsoft 365.

His­to­grams as a chart type

Excel also re­cog­nises his­to­grams as a chart type. With this function, you have different options for clas­si­fy­ing classes. To be able to use the options when creating a histogram in Excel, you need to use the list con­tain­ing the original meas­ure­ment data. The first step is to select the data you want to use.

Close

Now click on the button for the histogram in the Insert tab (in the Charts section).

Close

Based on the data you provide, Excel auto­mat­ic­ally creates cat­egor­ies. Excel evenly dis­trib­utes the data into different bars.

Close

If you right-click on the X-axis and then select the Format axis… option, you’ll be given advanced axis options.

Apart from the automatic clas­si­fic­a­tion that the program performs, Excel also offers you two other in­ter­est­ing pos­sib­il­it­ies. You can define the class width (bin width), and Excel will then determine how many classes need to be created or you can specify how many classes you want, and Excel will auto­mat­ic­ally determine the width of the bars. Ad­di­tion­ally, you can also set overflow and underflow bins. These are classes that more precisely define the edges of the histogram. Enter values that you consider to be the desired minimum and maximum values – that is, ‘everything below this value’ and ‘everything above this value’. Depending on the collected values, you can make a more mean­ing­ful clas­si­fic­a­tion.

How to create an Excel histogram using add-ins

You can use an add-in, i.e., an extension of the standard functions, to create Excel his­to­grams. To do this, you need the Analysis ToolPak. You can activate this add-in (or check whether it is already active) by going to the Add-ins section in the Excel Options menu.

If the add-in is activated, make a table with all your meas­ure­ment 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.

Close

Now use the add-in to select the bin frequency. 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 field. For Input Range, select the column con­tain­ing the meas­ure­ment data. The Bin Range is the area where you’ve defined the bins. If you’ve labelled the columns in the first line, activate the option Labels.

Close

After you’ve decided where the analysis of the data is to be displayed (in a new or an existing spread­sheet), Excel will create a frequency analysis for you. In the new table you can now read how much data appears in the re­spect­ive bins. In order to create the actual histogram, you have to activate the option Chart Output. After you do this, click OK and Excel will create the chart.

Close