How to Create a Bin Range in Excel (3 Easy Methods)

Here’s an overview of a histogram created by applying bin ranges.

how to create a bin range in Excel

Method 1 – Using the Built-in Histogram Chart Option (For Excel 2016 and Later Versions)


  • Create the worksheet. In our case, we put the exam marks of 20 students ranging from 10 to 100.

dataset fro create a bin range

  • Select the whole data range.

dataset fro create bin range

  • Go to the Insert tab and select Histogram from the options.

  • Here’s the stock histogram.

creating bin range in histogram

  • Right-click on the horizontal axis and select Format Axis.

  • Change the Bin width as needed. We chose 5 as Bin width.

  • Check the Overflow bin and Underflow bin and insert the values which the histogram will plot.

  • To add labels to the histogram, right-click on it and select Add Data Labels.

  • The frequency of each bin will be displayed on top of the bar.

creating a bin range in excel

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

Method 2 – Using the Data Analysis Toolpak

Part 1 – Download the Data Analysis Toolpak

  • From File, select Options.

  • The Options window will pop up.

creating bin range using data analysis toolpak

  • Select Add-ins.
  • Choose Excel Add-ins in the Manage field and click on Go.

  • Select Analysis ToolPak and click OK.
  • You will get the Data Analysis option in the Data section.

Part 2 – Use the Data Analysis ToolPak to Create a Bin Range

  • Create the dataset with an extra column called Bins and enter the upper bin thresholds.

create bin using dat analysis toolpak

  • Go to Data and select Data Analysis.

  • Select Histogram from the window and select OK.

  • A Histogram window will pop up.
  • In the Input range, select the cells containing the data.
  • In the Bin range, select the Bins column. S
  • Select the Output range for the result.

  • Select OK and the histogram will show according to the bin ranges.

bin range create

Read More: How to Make a Histogram in Excel Using Data Analysis

Method 3 – Create a Bin Range Using the FREQUENCY Function


  • Add columns for Bin Limit, Bin Label, and Bin Counts.
  • Fill up the Bin Limit cells with the upper limit of each bin range.
  • In Bin Label, put the Bin ranges.

create a bin range

  • Enter the following formula in the first cell for Bin counts (F5):
=FREQUENCY(C5:C24, D5:D14)

create bin range by frequency function

  • We have found the counts.

  • Select the data for Bin Label and Bin Counts.

  • Go to the Insert tab and select Column Chart and 2D Column.

creating bin range by frequency function in excel

  • Here’s the “histogram” chart.

creat bin range frequency function

Download the Practice Workbook

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF