How to Create a Histogram in Excel with Bins (5 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.

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 sales data of some sales reps of a certain organization. The sales quantity may vary from 0 to 500. Now you can divide this range into several groups like 0-100, 101-200, 201-300, 301-400, and so on. This way you can quickly get an overview of the performance of the business on the number of sales reps appearing in those groups. A histogram just does that successfully.

histogram with 100 bin size

How to Create a Histogram in Excel with Bins: 5 Simple Ways

Assume we have Sales Data for a particular organization. This dataset includes the “Sales Rep ID” and the corresponding “Quantity Sold” under columns B and C. We also have the number of “Bins” available on the right side of our dataset.

Sales data of multiple sales rep

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.
Now, we’ll utilize this dataset to create a histogram chart in Excel with bins using multiple methods. So, let’s explore them one by one.
Not to mention, here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.

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

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

📌 Steps:

  • First, select the sales quantity in the C5:C24 range and then go to Insert >> Insert Statistic Chart >> Histogram.

inserting histogram from Insert ribbon

  • After that, the following histogram will be generated. Excel recognizes the statistical data range and gives us 3 sets by default.

histogram inserted with 3 columns

  • 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.

selecting Format Axis option

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

working on the Format Axis task pane

  • Finally, the histogram will look as follows.

histogram with 100 bins size in excel

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:

  • Firstly, enter the following formula in cell F5. This will return the frequency of data according to the bins.

calculating frequency of each bins

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

inserting 2D clustered column

  • Next, right-click on the blank chart and click on the Select Data option from the context menu.

opening context menu on blank chart

  • After that, select the frequency range (F5:F9) and click on Edit below the Horizontal (Category) Axis Labels.

working on Select data source dialog box

  • Then select the Bins range (E5:E9) and click OK.

adding axis label range on the histogram in excel

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

select data source dialog box in excel

  • After that, the chart will look as follows.

create histogram with bins in excel

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

histogram with bin in excel with proper formatting

Read More: How to Make a Stacked Histogram in Excel

3. Create a Histogram Using COUNTIFS Function

The COUNTIFS function is an additional feature that assists with computing frequency distributions for creating histograms in Excel. To utilize this feature, three distinct formulas will be necessary. Please proceed with the instructions provided below.

📌 Steps:

  • The formula for the top bin in cell F5 is the following.

$C$5:$C$24: This is the range of cells being evaluated.
“<=”&E5: This is the criteria being used to count the number of cells. The less than or equal to operator (“<=“) is concatenated with the value in cell E5 using the “&” operator.

COUNTIFS function to determine frequency of first bin

  • The formula for bins except the last one is the following. Below is what we used in the F6 cell.

For the remaining cells, use the Fill Handle to get results.

COUNTIFS function to determine frequency of all bins except the first and last ones

  • The following is the formula for the final bin in cell F9.

COUNTIFS function to determine frequency of last bin

  • We completed calculating the frequency for all bins. Now, plot them in a column chart just like the previous method. The result is before our eyes.

histogram plot with dataset

4. Create a Histogram Chart Using PivotChart in Excel

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

📌 Steps:

  • At first, select the sales quantity (C5:C24) and go to Insert >> PivotChart as shown below.

inserting pivotchart in Excel

  • You can see that the whole range will be selected automatically with the header row. Then, enter the location for the PivotChart to be placed and click OK. This will insert a PivotChart along with PivotTable.

working on create pivotchart dialog box

  • Next, drag the Quantity Sold field to the Rows and Values areas.

ordering pivottable fields

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

opening value field settings

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

changing type of calculation to count

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

clicking on the Group option

  • Next, uncheck the ‘Starting at’ and ‘Ending at’ checkboxes, enter 1 and 500 in the respective text boxes beside them, then enter 100 in the text box below them to group By 100 and, then click OK.

giving arguments for grouping in pivottable in excel

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

histogram with bins beside pivottable in Excel

5. Create a Histogram with Data Analysis Tool in Excel (Available from 2013 Version)

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 make a Histogram in Excel using Data Analysis.

📌 Steps:

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

moving to data analysis tool on the Data tab ribbon

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 C4:C24 as the Input Range, and the range E4:E9 as the Bin Range, check the Labels checkbox, enter the Output Range, select the Chart Output checkbox, and click OK.

working on histogram dialog box to create histogram with bins in excel

  • 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.

histogram with bins visible beside bins and frequency table

How to Customize a Histogram in Excel

You can change the chart type, color scheme, axis labels, and other formatting options to suit your needs. You can also add additional data series, adjust the bin size, and modify the chart layout. Excel provides many customization options to create a professional-looking histogram that effectively communicates your data.

How to Customize Axis Labels on Histogram Chart

To modify the axis labels on an Excel histogram chart, you can navigate to the chart’s “Format Axis” menu and edit the labels as desired. This allows you to customize the labeling scheme for the horizontal and vertical axes, making the chart more informative and easier to understand for your audience. For detailed information, follow the steps below.

📌 Steps:

  • At the very beginning, open the Select Data Source dialog box as we did in Method 2. Then, click on the Edit button under the Horizontal (Category) Axis Labels.

editing horizontal axis labels of histogram plot in excel

  • To specify which labels should be displayed on the Axis, input them in the Axis label range box and separate them using commas. If you need to enter intervals, enclose them in double quotes and click OK as shown in the screenshot.

customizing axis label range

  • You can see the ultimate outcome right in front of you.

customized axis label range in histogram with bins in excel

How to Customize Bin Size

In Method 1, the width of the bin was set to 100 and you saw how it looks in the image. Here, we’ll demonstrate that we can customize the bin size also. See the steps carefully.

📌 Steps:

  • First and foremost, open the Format Axis task pane as we have shown before. Then, change the Bin width to the number you want. In this case, we converted it to 50.

changing bin width

  • The histogram looks like the following now.

histogram with bin width of 50

How to Adjust the Number of Bins

You can also change the visualization of your chart by changing the number of bins. Let’s see how we do it easily.

📌 Steps:

  • Again, open the Format Axis task pane and write your desired number in the Number of bins box. We wrote 10 in it.

changing number of bins of histogram in excel

  • Now, the chart is like the one below.

histogram with 10 bins in excel

It’s now consisted of the same number of columns as our input.

How to Modify Gaps Between Bars

Also, it’s possible to change the gap between the two columns in this chart. This makes the columns thick and thin also. So, let’s see it in action.

📌 Steps:

  • Firstly, right-click on any column in the chart and select Format Data Series from the context menu. Then, change the Gap Width on the dialog box.

changing gap width of column

  • And see the magic.

histogram with decent gaps between columns

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

Assume we have Sales Data for two different months of the same sales reps of a certain organization. Here, you have “Quantity Sold in Jan” and “Quantity Sold in Feb” under columns C and D.

dataset with two sets of data

We’ll create a histogram plot from this dataset with two sets of data. Just follow the steps to make a Histogram in Excel with two sets of data.

📌 Steps:

  • First, you need to apply the FREQUENCY function to calculate the frequency for both Jan and Feb.

calculated frequency for two months

  • Now, plot a chart as we did in Method 2 and it’ll look like the one below.

histogram with two sets of data with bins width of 100 in excel

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.
  • Adjust the scale of the vertical axis if necessary to better display the data.
  • The Number of bins you choose can affect the shape and readability of the histogram, so consider experimenting with different bin sizes to find the best fit for your data.

Frequently Asked Questions

1. What is a histogram in Excel?

A histogram is a chart type that displays the distribution of a set of data. It groups data into bins or intervals and shows the frequency or count of data points that fall within each bin.

2. What is bin size in Excel?

The bin size in Excel refers to the range of values that are grouped together in each bar of the histogram. It determines the width of each bar and can affect the shape and readability of the histogram.

3. What are some methods for calculating bin size in Excel?

Excel offers several methods for calculating bin size, including Scott’s rule, Freedman-Diaconis’ rule, and Sturges’ rule. These methods use different formulas to determine the optimal bin size based on the range and number of data points in the dataset.

4. How can I customize the appearance of my histogram in Excel?

Excel allows you to customize many aspects of the histogram’s appearance, including color, font, chart title, and axis labels. You can also choose to include a cumulative frequency curve or other additional elements to enhance the chart’s visual impact.

Download Practice Workbook

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


To sum up, creating a histogram in Excel with bins is a useful way to visualize and analyze the distribution of data. By grouping data into bins, you can see how frequently values occur within a certain range, which can help you identify trends, outliers, and other patterns in your data.

In this reference, we added 5 different but easy ways to create this chart. Also, we shared some tips and tricks to customize and beautify your histogram plot in Excel.

Now you know how to create a histogram in Excel with bins in 5 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. Stay with us and keep learning.

Related Articles

<< Go Back to Excel Histogram | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF