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

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Create a Bin Range in Excel


How to Create a Bin Range in Excel: 3 Easy Methods

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. Follow the steps below to plot a Histogram Chart and create a bin range.

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


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 a Histogram in Excel Using Data Analysis


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


Download Practice Workbook

Download the practice Workbook from here.


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo