How to Create a Histogram in Excel with Bins (4 Handy Methods)

This article illustrates how to create a histogram in excel with bins. A histogram depicts the frequency of data in different ranges within the data range. It is a graph made up of rectangular bars. The number and height of the bars are proportional to the number of different ranges called bins and to the frequency of data within those bins.

A histogram allows you quickly get an overview of a dataset. For example, if you have a histogram created from the test scores of the students in a class, you can assess the performance of the class at a glance. Because you will know how many students failed, how many of them got good grades etc the moment you look at the histogram. It can be very useful during your presentations.

Fortunately, there are several ways to create a histogram in excel. Follow the article as we are going to highlight all of the methods to do that.


Download Practice Workbook

You can download the practice workbook from the download button below.


What Is a Histogram with Bins?

A histogram is a graphical representation of data divided into different groups to show the frequency of data points in each group. It looks like the column chart in excel. All data points are divided into several groups or ranges called Bins. These bins are plotted along the horizontal axis. On the other hand, the frequencies of data corresponding to each bin are plotted along the vertical axis. Assume you have the test scores of 40 students from a class. The score may vary from 0 to 100. Now you can divide this range into several groups like 0-10, 10-20, 20-30, 30-40, and so on. This way you can quickly get an overview of the performance of the class based on the number of students appearing in those groups. A histogram just does that successfully.

create histogram using statistic chart in excel


4 Ways to Create a Histogram in Excel with Bins

Assume you have a dataset containing the test scores of 40 students in a class as shown below. Now you want to create a histogram with 10 bins using this dataset.

dataset to create a histogram

Dataset to create a histogram in excel. Some rows are hidden.

Follow any of the methods below to get your desired result.


1. Create a Histogram Using Statistic Chart in Excel 2016 and Later Versions

If you are using Excel 2016 or later versions, you can create a histogram in excel with bins by inserting a statistic chart. Follow the steps below to learn how to do that.

πŸ“Œ Steps:

  • First, select the scores (B5:B44) and then go to Insert >> Insert Statistic Chart >> Histogram.

insert histogram using statistic chart type

  • After that, the following histogram will be inserted.

a basic histogram

  • Now you need to modify the histogram to make it look as required. So first double-click on the chart title to rename it as required. Then click on the vertical axis and press delete if it is not required. You can also remove the chart gridlines using the chart element menu in the upper-right corner of the chart or from the chart design tab. Next right-click on the horizontal axis and select Format Axis.

format axis to modify histogram

  • After that, mark the radio button for the Number of bins and enter 10 in the text box. Then check the Overflow bin and Underflow bin checkboxes and enter 90 and 10 in the text boxes respectively.

format axis options

  • Finally, the histogram will look as follows.

create histogram using statistic chart in excel

Read More: How to Change Bin Range in Excel Histogram (with Quick Steps)


2. Create a Histogram Chart Using FREQUENCY Function

You can also use the frequency function along with a column chart to create a histogram in excel. Follow the steps below to be able to do that.

πŸ“Œ Steps:

  • First, enter the following formula in cell D5. This will return the frequency of data according to the bins.
=FREQUENCY(B5:B44,C5:C13)

use FREQUENCY function

  • Then click on an empty cell and select Insert >> Insert Column or Bar Chart >> 2-D Column Clustered Column. This will insert a blank chart.

insert a column chart to create histogram

  • Next, right-click on the blank chart and click on Select Data.

select data for histogram

  • After that, select the frequency range and click on Edit below the Horizontal Axis Labels.

change bin range of histogram

  • Then select the Bins range and click OK.

select bin range for histogram

  • Next, you will see the Bins values in the Horizontal Axis Labels. Now click OK to update the column chart.

bin values added

  • After that, the chart will look as follows.

basic histogram created from column chart

  • Finally, you can add a chart title, remove the vertical axis, add data labels, and remove the chart gridlines to get a similar result as earlier.

create histogram with bins using column chart

Read More: How to Make a Stacked Histogram in Excel (3 Easy Methods)


3. Create a Histogram Using PivotChart in Excel

Alternatively, you can insert a PivotChart to modify it to a histogram by following the steps below.

πŸ“Œ Steps:

  • First, select the scores (B4:B44) and go to Insert >> PivotChart as shown below.

insert a PivotChart

  • Then enter the location for the PivotChart and click OK. This will insert a PivotChart along with PivotTable.

select ranges for PivotChart

  • Next, drag the Score field to the Axis and Values areas.

drag PivotChart fields to areas

  • After that, click on Sum of Score in the Values area and go to the Value Field Settings.

go to Value Field Settings

  • Now change the type of calculation to Count and click OK.

change the calculation type

  • Then, right-click on any cell from the Row Labels in the Pivot Table and select Group.

group PivotTable data

  • Next, uncheck the β€˜Starting at’ and β€˜Ending at’ checkboxes, enter 0 and 100 in the respective text boxes beside them, then enter 10 in the text box below them to group By 10 and, then click OK.

criteria to group PivotTable data

  • Finally, the PivotTable and the PivotChart will look as follows.

create histogram using Excel PivotChart

Read More: How to Create a Bin Range in Excel (3 Easy Methods)


4. Create a Histogram with Data Analysis Tool in Excel 2013

Finally, we will discuss the smartest way i.e. using the Data Analysis tool to create a histogram in excel with bins. Follow the steps below to learn how to do that.

πŸ“Œ Steps:

  • First, select Data >> Data Analysis as shown below.

select Data Analysis to create histogram

Enable Data Analysis ToolPak:

If you don’t find the Data Analysis tool in the Data tab, then you can enable it by following the steps below.

  • First, press ALT + F + T or select File >> Options. Then go to the Add-ins tab and click on Go beside Manage: Excel Add-ins.

excel options

  • Next, check the Analysis ToolPak checkbox and click OK. After that, you will be able to access the Data Analysis tool.

enable Data Analysis ToolPak

  • Next, select Histogram and click OK.

select Hisogram as the analysis tool

  • Now select the range B4:B44 as the Input Range, the range C4:C14 as the Bin Range, check the Labels checkbox, enter the Output Range, the Chart Output checkbox and, click OK.

criteria to create histogram

  • After that, you will see the histogram along with a Bin and Frequency table as follows. You can modify the histogram to make it more presentable as looked at earlier.

create histogram with bins

Read More: How to Make Histogram Using Analysis ToolPak (with Easy Steps)


How to Make a Histogram in Excel with Two Sets of Data

Assume you have the test scores of students from two different classes. Then you need to apply the FREQUENCY function to calculate the frequency for both classes.

two sets of data for histogram

  • Now plot both of the frequency series against the bins to get the following result.

create histogram in excel with two sets of data

Read More: How to Make a Histogram in Excel with Two Sets of Data (4 Ways)


Things to Remember

  • Insert Statistic Chart option is available in Excel 2016 and later versions.
  • You must enable the Data Analysis ToolPak add-in to access the Data Analysis tool.

Conclusion

Now you know how to create a histogram in excel with bins in 4 different ways. Which method do you prefer the most? Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo