The article will show you how to change the bin range in an Excel histogram. A histogram is a graph that illustrates the frequency of some statistical data using rectangles. The height of a rectangle defines the number of data within the corresponding range. In Excel, we can do analysis by a histogram, but the format of the histogram in Excel is not user-friendly by default. So we need to do some modifications to make the histogram look better and more understandable. One of the vital features to modify an Excel histogram is to use a proper bin range. You will get some valuable information about this bin range in the later sections of this article.
Download Practice Workbook
What Is Bin Range in Histogram?
Bin range in histogram defines the series of intervals that divide the entire range of values of the dataset and then count how many values fall in those intervals.
For instance, you have this dataset: 1, 2, 2, 3, 3,6, 2, 10, 3. Here, the maximum and minimum numbers of the dataset are 10 and 1 respectively. So, if you want to divide this range of dataset into two intervals, each interval will be 5 (rounded value of (10-1)/2 or 4.5) unit long. You see that from 1 to 5, there are 7 data (1, 2, 2, 2, 3, 3, 3) and from 6 to 10 there are 2 data (6 and 10).
Steps to Change Bin Range in Excel Histogram
In the dataset, you can see some profit data for the first 10 months of the year. We will make a histogram with this dataset and organize the graph in a suitable way by applying a proper bin range.
Step 1: Calculating Bin Range for the Dataset
To get a nice looking histogram for any given dataset, it is necessary to calculate the bin range value. You cannot just use a random value for the bin range. Because you may get redundant intervals in that case. The formula to determine the bin range is given below:
(Maximum Value – Minimum Value)/(Rounded Value of the Square Root of the Number of Data)
- First, select a cell to store the bin range and type the following formula in that cell.
The formula uses the MAX, MIN, ROUNDUP, SQRT, and COUNT functions to calculate the bin range value.
- COUNT(C5:C14) —-> returns the number of data of the dataset.
- Output: 10.
- SQRT(COUNT(C5:C14)) —-> turns into
- SQRT(10) —-> returns the square root of 10.
- Output: 16227766
- ROUNDUP(SQRT(COUNT(C5:C14)),0) —-> then becomes
- ROUNDUP(3.16227766) —-> returns
- Output: 4
- MIN(C5:C14) —-> returns the minimum value of the dataset.
- MAX(C5:C14) —-> gives the maximum value of the dataset.
- Output: 42
- (MAX(C5:C14)-MIN(C5:C14))/ROUNDUP(SQRT(COUNT(C5:C14)),0) —-> turns into
- (38733.42-20399.71)/4 —-> which results into
- Output: $4583.43
- After that, hit the ENTER button and you will get the bin range
Read More: How to Create a Histogram in Excel with Bins (4 Methods)
Step 2: Creating Histogram with Excel Dataset
Let’s make a histogram using our dataset of this article.
- First, select the range B4:C14 and then go to Insert >> Histogram (from the Charts group).
- After that, you will see the histogram appear on your Excel sheet.
You can see that the histogram is divided into just only 2 bars which means it only shows the frequency for two intervals. So, to get frequencies for more intervals, we need to apply the bin range which you will see in the next step.
Read More: How to Make a Histogram in Excel with Two Sets of Data (4 Ways)
- How to Create Probability Histogram in Excel (with Easy Steps)
- Plot Histogram with Unequal Class Intervals in Excel
- How to Make a Stacked Histogram in Excel (3 Easy Methods)
- How to Plot Histogram in Excel (5 Easy Ways)
Step 3: Changing Bin Range in Histogram
This is the final step of this process. Let’s have a look at the description below.
- First, click on the Plus icon of the histogram chart and then select Axes >> More Axis Options.
- After that, change the Bin width to 43. This will divide the histogram into 4 intervals.
- In addition, you can change the number format according to your choice. Here I chose a Custom Category which will convert the number to thousands format (1k = one thousand).
Now you are all set. You can see the histogram in a suitable way as the bin range has been changed properly.
Read More: [Fixed!] Excel Histogram Bin Range Not Working
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Suffice to say, you will learn the easy steps of how to change bin range in an Excel histogram after reading this article. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.