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

In Excel histogram is widely used to analyze data. In the histogram, the ranges along X-axis are known as the Bin range. We can create a Bin range in Excel by different methods. This Bin range helps to visualize data in a more transparent manner.


Download Practice Workbook

Download the practice Workbook from here


3 Easy Methods to Create Bin Range in Excel

In this article, we will discuss 3 different methods to create a Bin range in Excel. All 3 methods are very simple and easy. All the methods are described below step by step.how to create a bin range in Excel


Method 1: Using Built-in Histogram Chart Option (For Excel 2016 and Further Versions)

In Excel 2016 and the following versions, we can create a Bin range in Excel very easily using the in-built Histogram Chart feature. The steps are given below.

Step 1: First we create the worksheet. In our case, we enlisted exam marks of 20 students ranging from 10-100.

dataset fro create a bin range

Step 2: Then we select the whole range of data.

dataset fro create bin range

Step 3: We need to go to the Insert tab and select Histogram from the options.

We can see the histogram like below on our sheet.

creating bin range in histogram

Step 4: On the bottom of the histogram there’s a horizontal axis. We right-click on that. Select Format  Axis.

Step 5: The histogram Bin range is set automatically. We can change the Bin width from the box as we require. In our case, we chose 5 as Bin width.

We have selected the Overflow bin and Underflow bin which denotes the range on which the histogram will plot.

Now we can see the Histogram as we chose.

Step 6: If we want to add labels to the histogram, we select the histogram right click on it, and select add data labels.

The frequency of each bin will be displayed on top of each histogram bar.

creating a bin range in excel

Read More: How to Create a Histogram in Excel with Bins (4 Methods)


Method 2: Using Data Analysis Toolpak

Before Excel 2016 versions the built-in Histogram Chart option is not present. In that case, we can create a bin range in Excel using a Data Analysis Toolpak. The steps to do that are described below.


Download Data Analysis Toolpak

We can download The Data Analysis Toolpak easily by following the steps.

Step 1: From File, we need to select the Options.

This window will pop up.

creating bin range using data analysis toolpak

Step 2: We have selected Add-ins>Excel Add-ins>Go. Then we selected OK.

A window like this will open.

Step 3: We need to select Analysis Toolpak and select OK.

We have checked the Data Analysis option in the Data section


Use Data Analysis Toolpak to Create a Bin Range

Step 1: We need to create the dataset with an extra column called Bins where we will enter the Bin range.

create bin using dat analysis toolpak

Step 2: Let’s go to Data>Data Analysis.

We see a Data Analysis window. Select Histogram from it and select OK.

A Histogram window will pop up.

Step 3: In the input range we select the cells containing our data. In the Bin range we select the Bins column we created additionally. We select the output range where we want to see the result.

Select OK and the histogram will show according to the bin range we created.

bin range create

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


Method 3: Create Bin Range Using FREQUENCY Function

We can create a bin range in Excel by using the FREQUENCY Function  also. The steps for this procedure are given below.

Step 1: We have created the worksheet with additional columns Bin Limit, Bin Label, and Bin Counts.

We fill up the Bin Limit cells with the upper limit of our Bin range. In Bin Range we put the Bin range which will be one cell more than Bin Limit.

create a bin range

Step 2: We selected cells below Bin Counts. The selected cell number should be 1 more than the Bin Limit cells. Then we enter the cell. 

Step 3: Let’s write (=) then write FREQUENCY( and select the data range 1st, put a (,) then select the Bin Limit data. Close the parenthesis and press ENTER.

=FREQUENCY(C5:C24, D5:D14)

create bin range by frequency function

We have found the counts.

Step 4: We selected the data from Bin Label and Bin Counts.

Step 5: We need to go to the Insert tab and select Column Chart >2D Column.

creating bin range by frequency function in excel

We have created a bin range in Excel using the FREQUENCY Function.

creat bin range frequency function

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


Conclusion

In this article, we have shown 3 easy methods to create a bin range in Excel. Bin range is very vital for data analysis. Hope this article will help you create a Bin range in Excel. If you face any problem following the steps or if you have any suggestions, please let us know by commenting below.


Related Articles

Mehedi Hasan Shimul

Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo