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

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 of modifying 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.

What Is Bin Range in Histogram?

Bin range in the 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) units 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).

How to Change Bin Range in Excel Histogram: Step-by-Step Procedures

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 to create 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.

=(MAX(C5:C14)-MIN(C5:C14))/ROUNDUP(SQRT(COUNT(C5:C14)),0)

The formula uses the MAX, MIN, ROUNDUP, SQRT, and COUNT functions to calculate the bin range value.

Formula Breakdown

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.
- Output:71
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

Step 2: Creating Histogram with Excel Dataset

Let’s make or plot 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 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.

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.

Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

Conclusion

Suffice it to say, that 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 questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.

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

Get FREE Advanced Excel Exercises with Solutions!