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

- Output:
**SQRT(COUNT(C5:C14)) â€”->**turns into**SQRT(10) â€”->**returns the square root of**10**.- Output:
**16227766**

- Output:
**ROUNDUP(SQRT(COUNT(C5:C14)),0) â€”->**then becomes**ROUNDUP(3.16227766) â€”->**returns- Output:
**4**

- Output:
**MIN(C5:C14) â€”->**returns the minimum value of the dataset.- Output:
**71**

- Output:
**MAX(C5:C14) â€”->**gives the maximum value of the dataset.- Output:
**42**

- Output:
**(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**

- Output:

- After that, hit the
**ENTER**button and you will get the**bin range**

**Read More:** How to Create a Histogram in Excel with Bins

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