Souptik Roy
To make a visual representation of a dataset, Excel introduces many charts. Such as Bar charts, Pie charts, Scattered charts, etc. One of these charts is Histogram. This is a common type of chart to visualize datasets. The main component of this type of chart is Bin Range in Histogram. In this article, I am going to describe the Bin Range in Histogram with necessary pictorial examples.
Download Practice Workbook
Please download the practice workbook to practice yourself.
Introduction to Bin Range in Excel Histogram
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 to divide the dataset into a series of intervals. Then we count the number of values that fall into each interval. We can define the bins as a series of discrete intervals that don’t overlap. The bins (intervals) must be close together and frequently have the same size.
When the bins are the same size, a rectangle is constructed across each bin, with the height proportional to the frequency. This is the number of cases in each bin. Additionally, a histogram displays the percentage of cases that fit into each of various categories. Bins don’t always have to be identical in width. The number of instances per unit of the variable on the horizontal axis serves as the vertical axis.
Different Bin Ranges & Their Impacts in Excel Histogram
In this portion of this article, I will show different bin ranges as well as their impacts on histograms. To show this, Let’s consider a dataset that defines No. of Students in ABC School. We can see two particular columns consisting of No. of Students and Bin in Columns B & C respectively. Additionally, the dataset ranges from B4 to C12. Let’s see the steps of different bin ranges with illustrations.
Apply Bin Range in Histogram
Here, I will apply the bin range in the histogram. I will consider the dataset introduced earlier. I am going to describe the whole process step by step here.
Example 1: Use of Data Analysis
Steps:
- First, Go to the Data tab in your Toolbar.
- Then, Select the Data Analysis option from the Analysis
- After that, a new window will pop up. Select the Histogram
- Click on the OK
- Input $B$5:$B$12 in the Input Range by selecting the dataset.
- Moreover, Input $C$5:$C$12 in Bin Range by selecting the dataset.
- Select a cell $D$4 for the output range also.
- Click on the Chart Output to Check the option.
- You will find the output just like the picture given below.
- You will find the histogram just like the picture given below.
- To change the histogram column type, Right-Click on the Columns.
- From the opened menu, Select the Format Data Series
- After selecting this option, Click on the indicated icon.
- Then, Select gap width 0%.
- Hence, Select the first icon.
- Click on the Border
- Select Solid Line and then Select Black
- At last, you will find the histogram just like the one given below.
Example 2: Use of Recommended Chart Option
Steps:
- Select data ranging from B5 to B12
- After that, Select the Insert tab from your Toolbar.
- Then, Select the Histogram option.
- Moreover, Select the first option to insert a Histogram.
- After that, you will find the histogram chart just like the picture given below.
- Hence, Right-Click on the x-axis to edit.
- From the Format Axis option increase the number of bins to 6.
- Change the overflow and underflow bin to 60 & 0.0.
- You will find the histogram just like the one given below.
Read More: How to Make a Histogram in Excel with Two Sets of Data (4 Ways)
Impacts of Bin Range on Histogram
- In the first example, I have divided the result equally into 9 parts. As a result, we can see 9 columns having equal width. If we make the width inequal, this will affect the visual representation.
- The same description is also applicable to the second example. But at first, there were only two columns as Bin Range was higher. But at the final result, I have lowered the Bin range by dividing the whole data into six equal distributions.
Things to Remember
- If you don’t have the data analysis button in your data tab, you need to enable analysis Toolpak addins.
Conclusion
I have tried to explain the bin range throughout the whole article. I hope you have enjoyed it. If you have any kind of queries, let me know in the comment section.
