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.
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.
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.
Step 2: Then we select the whole range of data.
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.
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.
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.
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.
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.
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.
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)
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.
We have created a bin range in Excel using the 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.
