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

how to change bin range in excel histogram


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.

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

Step 1: Calculating Bin Range for the Dataset

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

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).

Step 2. Creating Histogram with Excel Dataset

  • 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)


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.

Step 3. Changing Bin Range in Histogram

  • 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: How to Make a Stacked Histogram in Excel (3 Easy Methods)


Practice Section

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


Conclusion

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.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo