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.

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

**=MIN(C5:C15)**

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

After that, move to cell **E11 **and input the formula

**=MAX(C5:C15)**

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

Now, go to cell **E6 **and enter the formula.

**=E5+10**

Here, we have taken 10 as our bin interval.

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

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**

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

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.

**=SMALL(C5:C15,1)**

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

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

**=LARGE(C5:C15,1)**

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

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

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

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

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

## 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

**. The**

*Microsoft 365***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.

**=MINIFS(C5:C15,C5:C15,”>=80″)**

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

- Then, move to cell
**E13**and write the formula.

**=MAX(C5:C15)**

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.

## 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 I**nsert Statistic Chart**>> choose**Histogram**.

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

- Then, move to the
**Series Options**of that**Format Data Series**. Pick up a suitable color.

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

## Practice Section

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

**Download Practice Workbook**

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

## Conclusion

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.