How to Make a Histogram in Excel

In this tutorial, I will show you how to make a histogram in Excel using two different methods. A histogram is a chart which groups the data points into user specified ranges. It looks very similar to a bar chart and it condenses data series into an easy to understand visual by taking many data points and grouping them into logical ranges or bins. The x axis buckets a range of outcomes into columns. The y axis is the count or percentage of occurrences in the data for each column and used to visualise data distributions.

People who work in statistics normally use histograms and it shows how many times a certain variable occurs within a specified range. For example, suppose you send out a questionnaire asking for the ages of the people who have filled in the questionnaire. You may, for instance, want to create a histogram which shows the number of people within the age ranges of 18-27, 28-37, 38-47, 48-57, 58-67 who have filled in the questionnaire.

I will now show you two different methods of how to make a histogram in Excel. The first method will show you how to make a histogram using Excel 365 or Excel 2016 onwards. Secondly, for those of you who don’t have Excel 2013 or older, the second half of this tutorial will show you how to make a histogram using the FREQUENCY function.

How to Make a Histogram in Excel 365

Before Excel 2016, you didn’t have the option to choose a histogram in the charts section of the ribbon. As a result, this made making a histogram a little more difficult. Luckily, Excel 365 and Excel 2016 introduced the histogram chart as one of its default chart options and therefore you can create one very quickly.

A dataset to be used to make a histogram in Excel which shows a list of employees with their ages
A dataset with a list of employees in a company with their ages

To make a histogram in Excel, I will use the above dataset which shows a list of employees working in a company along with their ages.

Here are the steps to create a histogram in Excel.

  1. Select the entire dataset. In this example, I have highlighted the range A1:B16
A dataset in a worksheet with a list of employee names and their ages highlighted
Highlight the dataset
  1. Click on the Insert tab in the ribbon. Under the Charts group select Insert Static Chart and then select the Histogram chart icon
The Insert tab in the Excel ribbon, the Insert Static Chart and the histogram chart highlighted
Select the histogram chart in the Excel ribbon
  1. This will insert the histogram in the worksheet
A worksheet showing a histogram chart embedded
The histogram will be embedded into your worksheet
  1. Now you can format the histogram. Right click the vertical axis and select Format Axis
A chart selected and the Format Axis menu highlighted
You can customise your histogram chart by selecting Format Axis in the shortcut menu
  1. The Format Axis options pane will open on the right where you can select different options to customise your histogram
The Format Axis options box with the Automatic option selected
Use the Format Axis box to customise your histogram

Here are what the different categories mean:

By Category: Use this option when there are repetitions in categories and you want to know the sum or count of the categories. This option is not relevant in this example as the categories are all different because the employees all have different names.

Automatic: Excel will automatically decide what bins to create. In this example, Excel decided to have 3 bins in the histogram chart. These are 18 to 39, 39 to 60 and 60 to 81.

Bin Width: With this option, you can decide how big the bins should be. For example, in the histogram chart above, the bins are 21.

A chart embedded in a worksheet with a bin width of 10
The bin width has changed from 21 to 10

In the above example, I have changed the bin width to 10. As a result, the number of bins has increased from 3 to 5.

Number of Bins: Here you can specify the number of bins you want and Excel will automatically create the histogram chart with that many bins.

A chart embedded in a worksheet with 4 bins
The number of bins has changed from 5 to 4

In the above example, I have changed the number of bins to 4 and as a result, the chart now shows 4 bins.

Overflow Bin: If you want to group values above a certain number together then you can use this option. For example, if I want to know how many employees were over 40, then I enter 40 in the Overflow bin field.

A chart embedded in a worksheet with a Overflow bin of 40
All the ages over 40 have been grouped together

In this example, you can see that I have grouped all the values over 40 together. The last bin is now showing >40.

Underflow Bin: This is very similar to the Overflow bin but this option groups values below a certain number.

A chart embedded in a worksheet with a Underflow bin of 40
All the ages under 40 have been grouped together

In this example, I entered 40 in the Underflow bin. As a result, this has grouped all ages below the age of 40 together.

Once you have customised the histogram chart as per the above settings, you can further format it by changing the chart title, adding axis titles, changing the chart design etc. To learn how to do this, you can see my post on How to Make a Chart in Excel: How to Create a Chart in Excel in 3 Easy Steps.

Using the FREQUENCY Function

If you don’t have Excel 365, Excel 2016 or newer, then you can make a histogram in Excel with a formula using the FREQUENCY function. The FREQUENCY function calculates the frequency of each value within a range. The FREQUENCY function is an array formula so you have to press Control + Shift + Enter on your keyboard.

The syntax of the FREQUENCY function is:

=FREQUENCY (data_array, bins_array)

The data_array argument is the values for which you want to get frequencies.

The bins_array argument is the intervals (bins) for grouping values.

A big advantage of creating a histogram with a formula is that it is dynamic which means it instantly updates when the data changes. Now I will show you how to make a histogram in Excel using the FREQUENCY function.

A dataset with employee names and their ages and a list of bins for the FREQUENCY function
Bins have been created in the range D2:D6 which will be used in the Excel FREQUENCY function

I will use the same dataset I used in the previous example of how to make a histogram in Excel 365. In the range D2:D6, I have entered the bins. In the range E2:E6, I want to enter a formula to see the frequency of the bins. For example, in cell E2, I want to see how many people are aged 28 and under. In cell E3, I want to see how many people are aged between 29 and 38 and so on.

Here are the steps to make a histogram in Excel using the FREQUENCY function.

  1. Highlight the range E2:E6
A dataset with the Frequency range selected
Highlight the data where the FREQUENCY formula will be entered
  1. Enter the formula =FREQUENCY(B2:B16,D2:D6) in the Formula Bar
Excel Frequency formula entered in the Formula bar which shows how to make a histogram chart in Excel
Enter the FREQUENCY formula in the Formula Bar
  1. Press Control + Shift + Enter on your keyboard. The frequency is now populated in the range E2:E6. Notice the curly brackets before and after the formula { }. This means the formula is an array formula
The Excel Frequency formula entered in the Formula bar which shows the frequency of the bins
Once the FREQUENCY formula has been entered you must remember to press Control + Shift + Enter on your keyboard
  1. You can now create a histogram chart by simply creating a column chart

Learn how to make Excel Charts

To learn more about Excel charts, then please see my tutorial on how to make a chart in Excel. In this tutorial, you will learn the following:

  • The different charts available in Excel
  • How to make an Excel chart in 3 easy steps
  • How to format your charts in quick easy steps

To check out this tutorial then please just click here.

I hope you enjoyed this post on how to create a histogram in Excel and found this tutorial helpful. Please leave a comment below if you have any feedback or if you know of any other ways to make histograms in Excel.

Spread the love
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •   
  •  

Leave a Comment

Your email address will not be published. Required fields are marked *