How to Calculate Bin Range in Excel (4 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Excel offers various kinds of charts, especially histograms. In the histogram chart, there is a fundamental component named Bin Range to define the charts in Excel. Basically, Bin Range is defined as intervals that are obtained by dividing the entire dataset value by several spaces. More importantly, it is essential to calculate the Bin Range in Excel for making a histogram. If you don’t know how to estimate it, don’t get worried. In this article, we are going to show you the steps to calculate the Bin Range in Excel. So, let’s get started.

What Is Bin Range?

The distribution of numerical data is roughly depicted by a histogram. The first stage in creating a histogram is to bin the range of values. This Bin means dividing the dataset into a series of intervals. Then we count the number of values that fall into each gap. We can define the bins as discrete intervals that don’t overlap. The bins (intervals) must be close together and frequently be the same size.

For instance, you have this dataset: 1, 2, 2, 3, 3,6, 2, 10, 3. Here, the maximum and minimum numbers of the dataset are 10 and 1, respectively. So, if you want to divide this dataset range into two intervals, each interval will be 5 (rounded value of (10-1)/2 or 4.5) units long. You see that from 1 to 5, there are 7 data (1, 2, 2, 2, 3, 3, 3) and from 6 to 10, there are two data (6 and 10).

How to Calculate Bin Range in Excel: 4 Easy Methods

There are several ways to calculate the Bin Range. We tried to cover all the methods for doing it. We have taken a dataset of No. of Students under different Departments to calculate the Bin Range.


Not to mention, we have used the Microsoft 365 version. You can use any other version at your convenience.

1. Using MAX and MIN Functions

To calculate the Bin Range, you have to calculate the Bins first. It means finding the upper and lower values of your dataset and then making the intervals according to your preferences. There are quite handy methods for finding out the highest and lowest values of a certain dataset. For example, using the MAX and MIN functions is one of them. Here, we have demonstrated to you the steps to be followed to use it.

Step 1: Find Lower Value

In the beginning, go to cell E5 and insert the formula


The MIN(C5:C15) will find out the minimum value from the C5:C15 range.

Find Lower Value to calculate Bin range in Excel

Step 2: Determine Upper Value

After that, move to cell E11 and input the formula


It will find out the maximum value of the C5:C15 range.

Determine Upper Value to calculate bin range in Excel

Step 3: Create Bin Intervals

Now, go to cell E6 and enter the formula.


Here, we have taken 10 as our bin interval.

Create Bin Intervals to calculate bin intervals in excel

Eventually, press ENTER and drag it down to cell E10. You will then get the Bins just like in the image below.

Step 4: Making Bin Range

In this step, we will calculate the range of our bins. To do this operation, select cell F5 and insert the formula.

=E5 & ” – ” & E6-1

Making Bin Range

Sequentially, it will create a range of 60-69 after pressing ENTER.

Step 5: Drag Down Fill Handle

Afterward, drag down the Fill Handle tool for the other cells.

Consequently, you will get your desired Bin Range just like the image below. But in the last cell, the formula will be-

=E10 & ” – ” & E11

That means you don’t need to subtract 1 from the last bin limit.

2. Utilizing LARGE and SMALL Functions

You can also find the upper and lower values by utilizing the LARGE and SMALL functions, respectively. These functions take a data array and a value to find out the kth largest or smallest value of a dataset. Follow the steps to clear the confusion.

📌 Steps:

  • Firstly, move to cell E5 and enter the formula.

In the above formula, the SMALL function searches for the 1st smallest value in the range of C5:C15 because we entered the logical argument as 1.

Utilizing LARGE and SMALL Functions to calculate bin range in Excel

  • Then, go to cell E11 and write up the formula.

It will search for the first largest value of C5:C15.

  • Thus, you will get the following result.

  • Afterward, follow Step 3 as we mentioned earlier to get the bin interval.

  • Lastly, pursue Step 4 and Step 5 to make the data range like the snapshot shown below.

calculating Bin range in Excel with SMALL and LARGE functions

Read More: How to Calculate Range for Grouped Data in Excel

3. Using the FREQUENCY Function

In a bin table of a set of data or datasets, the Excel FREQUENCY function calculates how frequently numerical values occur within the ranges you provide. It will compute a frequency distribution and provide it back. This function returns the distribution in the form of a vertical array of values representing a count per bin. Follow the steps below to use this function.

📌 Steps:

  • In the very beginning, make a Bins and Bin Range following the above method of using MAX and MIN functions.
  • Hence after, move to cell G5 and enter the formula.
=FREQUENCY(C5:C15, E5:E11)

This function finds the numerical values under the range of E5:E11, makes a count, and displays them.

Using the FREQUENCY Function to calculate bin range in Excel

Lastly, your result will look like the image below.

4. Employing PivotTable

You can get and calculate the Bin Range by creating a PivotTable. It will automatically create the range of your dataset. Go through the steps to do it.

📌 Steps:

  • Initially, select your entire data range >> go to Insert tab >> choose Pivot Table.

Employing PivotTable

  • PivotTable from table or range will appear and make the Table/Range as Sheet1!$C$5:$C$15 >> check the Existing Worksheet box >> create the Location in Sheet1!$E$4. Then press OK.

  • A PivotTable Fields sidebar will show. Drag down the No. of Students to Rows area.

  • Then, click on the Sum of No. of Students drop-down of the ∑Values and select Value Field Settings.

PivotTable Fields

  • A Value Field Settings dialog box will pop out. From there, select Count and hit OK.

  • Eventually, a pivot table will appear in the sheet, and right-click on any cell of the table and pick Group.

  • A Grouping window will appear. Uncheck the Starting at and Ending at boxes and select 5 in the By box. It will take the intervals as 5.
  • Finally, press OK.

Lastly, you will get the Bin Range and Bin Counts just like in the below image.

Employing PivotTable to calculate bin Range in Excel

How to Calculate Conditional Bin Range

Sometimes, you may need to calculate the upper and lower ranges of specific criteria. For example, in our dataset, we want to find the minimum value starting from 80 and the maximum value the same as before. For a conditional Bin Range like this, we have discussed two methods for making your conception at ease.

1. Employing MAX and MINIFS Functions

We can use the MAX and MINIFS functions in Excel 2019 and Microsoft 365. The MINIFS function is not available in the other versions of Excel. The MINIFS function is a combination of MIN and IF functions. See the steps for better visualization.

📌 Steps:

  • Firstly, go to cell E5 to enter the formula.

The above formula checks for the minimum value >=80 in the range of C5:C15.

Employing MAX and MINIFS Functions to calculate bin range in excel

  • Then, move to cell E13 and write the formula.

It will find out the maximum value from C5:C15.

  • Consequently, follow Step 3 and Step 4 stated above to create the Bin Range and the final image will look like the image below.

2. Applying MAX, MIN, and IF Functions

You can apply the MAX, MIN, and IF functions combinedly to calculate the conditional Bin Range. The IF function finds out the values under special criteria, and then the MAX and MIN functions will find out the largest and smallest values, respectively.

📌 Steps:

  • First of all, go to cell E5 and input the formula.
=MIN(IF(C5:C15>=80, C5:C15))

Formula Breakdown:

IF(C5:C15>=80, C5:C15)→ This function will look for values greater than or equal to 80 and return the values.

MIN(IF(C5:C15>=80, C5:C15))→ It will give you the minimum value by taking the result of the IF function.

Applying MAX, MIN, and IF Functions for Conditional Bin Range in Excel

  • Then, again follow Step 3 and Step 4 from the above and get your desired result.

How to Create Histogram Bin Range in Excel

As we said earlier, in the histogram chart the Bin Range holds a very significant role in specifying the intervals. You can plot a histogram chart by the Bin Range. Here we show you the step-by-step way to plot a histogram chart.

📌 Steps:

  • Firstly, select the entire data range and go to the Insert tab >> pick Insert Statistic Chart >> choose Histogram.

How to Create Histogram Bin Range in Excel

  • A Histogram Chart will appear.
  • Now, you need to format the chart.
  • Double-click on the X-axis value.

  • A Format Axis sidebar will open and check the Overflow bin and Underflow bin and make the Bin Width 10.

Eventually, after creating the Axis and Title, your chart will look like the image below.

Histogram chart with bin range

Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section

Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


That’s all about today’s session. And these are some easy methods to calculate bin range in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Thanks for your patience in reading this article.

Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Fahim Shahriyar Dipto
Fahim Shahriyar Dipto

Hello! Welcome to my Excel blog! I am a big fan of MS Excel. I am learning new and exciting things in Excel and writing the process here. I think this will be helpful for you to get used to Excel. Keep visiting our website for new and updated Excel methods.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF