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

The dataset contains profit data for the first ten months of the year. We will use this dataset to create a histogram and organize the graph by applying a proper bin range.

how to change bin range in excel histogram

Step 1 – Calculating the Bin Range for the Dataset

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)

  • Select a cell to store the bin range and copy the following formula in that cell:


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
  • Hit Enter and you will get the bin range.

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

Step 2 – Creating a Histogram with an Excel Dataset

  • Select the range B4:C14.
  • Go to Insert and select Histogram (from the Charts group).

Step 2. Creating Histogram with Excel Dataset

  • You will see the histogram appear on your Excel sheet.

The histogram is divided into only two bars which means it only shows the frequency for two intervals. To get frequencies for more intervals, we need to apply the bin range.

Step 3 – Changing the Bin Range in the Histogram

  • Click on the Plus icon of the histogram chart.
  • Select Axes and choose More Axis Options.

Step 3. Changing Bin Range in Histogram

  • Change the Bin width to 4583.43. This will divide the histogram into 4 intervals.
  • You can change the number format. We chose a Custom Category which will convert the number to thousands format (1k = one thousand).

  • The histogram changes to adopt the new bin range.

Practice Section

Here’s the dataset of this article so that you can practice making a histogram.

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF