How to Calculate Bin Range in Excel (4 Methods)

Excel offers various types of charts, including histograms. In a histogram chart, a fundamental component called Bin Range defines the intervals for the chart. Essentially, the Bin Range represents divisions obtained by splitting the entire dataset into several spaces. Calculating the Bin Range is crucial for creating histograms in Excel. If you’re unsure how to estimate it, don’t worry—I’ll guide you through the steps.

What Is Bin Range?

A histogram roughly depicts the distribution of numerical data. The first step in creating a histogram is to define the range of values using bins. Binning involves dividing the dataset into discrete intervals, and then counting the number of values that fall into each interval. These bins should not overlap, and it’s ideal for them to be of equal size and close together.

For example, consider this dataset: 1, 2, 2, 3, 3, 6, 2, 10, 3. The maximum and minimum values in this dataset are 10 and 1, respectively. If you want to divide this range into two intervals, each interval would be 5 units long (rounded from (10-1)/2 or 4.5). From 1 to 5, there are 7 data points (1, 2, 2, 2, 3, 3, 3), and from 6 to 10, there are two data points (6 and 10).

Let’s explore four methods for calculating the Bin Range. We’ll use a dataset representing the number of students under different departments.

Method 1 – Using MAX and MIN Functions

Step 1: Find the Lower Value

Go to cell E5 and enter the formula:

=MIN(C5:C15)

- This will find the minimum value from the C5:C15 range.

Step 2: Determine the Upper Value

Move to cell E11 and input the formula:

=MAX(C5:C15)

- This will find the maximum value in the C5:C15 range.

Step 3: Create Bin Intervals

In cell E6, enter the formula:

=E5+10

(We’ve chosen 10 as our bin interval.)

Press ENTER and drag it down to cell E10 to get the bin intervals.

Step 4: Calculating the Bin Range

Select cell F5 and insert the formula:

=E5 & ” – ” & E6-1

- This will create a range like 60-69 after pressing ENTER.

Step 5: Drag Down the Fill Handle

Drag down the Fill Handle tool for the other cells.

Your desired Bin Range will be similar to the image below.
Note: In the last cell, the formula will be:

=E10 & ” – ” & E11

(No need to subtract 1 from the last bin limit.)

Method 2 – Utilizing LARGE and SMALL Functions

You can also find the upper and lower values by using the LARGE and SMALL functions, respectively. These functions allow you to determine the kth largest or smallest value in a dataset. Follow these steps to clear any confusion:

Find the Lower Value:

Go to cell E5 and enter the formula:

=SMALL(C5:C15,1)

The SMALL function searches for the 1st smallest value in the range C5:C15 because we specified the logical argument as 1.

Determine the Upper Value:

Move to cell E11 and use the formula:

=LARGE(C5:C15,1)

This will find the first largest value in the range C5:C15.

You will get the following result.

Create Bin Intervals:

Follow Step 3 as mentioned earlier to get the bin intervals.

Calculate the Bin Range:

In cell F5, enter the formula:

=E5 & ” – ” & E6-1

This will create a range like 60-69 after pressing ENTER.

Drag Down the Fill Handle:

Drag down the Fill Handle tool for the other cells.
The desired Bin Range will be similar to the image below.

Method 3 – Using the FREQUENCY Function

The Excel FREQUENCY function calculates how frequently numerical values occur within specified ranges. It provides a frequency distribution as a vertical array of values representing counts per bin. Follow these steps:

Set Up Bins and Bin Range: