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.

**Table of Contents**hide

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

`=(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 (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)**

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