Excel Histogram Bin Range: Meaning and How to Set It

Charts & pivot tables · Updated November 19, 2026

In Excel’s Data Analysis Histogram tool, the bin range is a worksheet range containing the upper limits of the groups you want to count. Enter ascending numeric cutoffs—for example, 10, 20, and 30—and Excel counts values up to 10, then values above 10 through 20, then values above 20 through 30, with values above 30 reported separately.

What a histogram bin range means

A histogram summarizes numerical data by counting how many observations fall into each interval, or bin. The bin range tells Excel where those intervals end. It is not the range of cells containing the original observations, nor is it a range that lists every value in your data.

For the Analysis ToolPak, the bin values are upper boundaries. If the bin range contains 10, 20, and 30, the output categories represent values less than or equal to 10; values greater than 10 and less than or equal to 20; and values greater than 20 and less than or equal to 30. The tool also reports a final category for values greater than the largest boundary. Values below the first boundary are included in the first category.

This convention matters when choosing cutoffs. A bin value of 20 does not mean the category is exactly 20, nor does it mean the interval is 10 to 20 inclusive at both ends. Each boundary closes the interval on its upper end, while the next interval starts above the preceding boundary.

Where to enter the bin range

In Excel for Microsoft 365 on Windows, the Analysis ToolPak provides a worksheet-based histogram procedure. If Data Analysis is not visible on the Data tab, enable the add-in first: choose File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins, choose Go, select Analysis ToolPak, and choose OK. Then open Data > Data Analysis and select Histogram.

In the Histogram dialog, set the Input Range to the cells containing the observations and the Bin Range to the cells containing the upper limits. Choose an output location and, if you want Excel to create a chart from the results, select Chart Output. Select Labels only when the first cell in the selected input or bin range is a heading rather than a number. The input and bin values should be numeric, and bin limits should be in ascending order.

Excel for Mac also provides the Analysis ToolPak in supported desktop versions, though the add-in setup is reached through Tools > Excel Add-ins rather than the Windows Options dialog. Excel for the web does not provide the same desktop Data Analysis ToolPak workflow, so use desktop Excel for these dialog steps. Menu availability can vary with the Excel edition and update channel.

Set up useful bins before running the tool

Place the data and the bin limits in separate worksheet columns so they are easy to inspect. For instance, put daily delivery times in A2:A101 and upper limits in C2:C6. If the limits are 15, 30, 45, 60, and 75, the resulting groups are up to 15 minutes, above 15 through 30, above 30 through 45, above 45 through 60, and above 60 through 75. Any time over 75 appears in the overflow category.

Choose boundaries that match the question you are trying to answer. If a report needs five-minute time bands, limits such as 5, 10, 15, and 20 may be appropriate. If the goal is to compare broad patterns, wider groups may make the chart easier to read. The cutoffs should be in the same units as the data: a bin of 10 means ten of whatever the observations measure, not ten categories.

  • Use numeric values, not text descriptions such as Low or High, as the bin limits.
  • Sort limits from smallest to largest and avoid accidental duplicates.
  • Include enough boundaries to cover the range you expect, while remembering that Excel adds an over-maximum category.
  • Decide how to communicate the groups in the final report; the ToolPak’s boundary labels may need clarification for readers.

Worked example: counting scores

Suppose cells A2:A11 contain the scores 4, 8, 12, 17, 19, 23, 28, 31, 36, and 42. Put 10, 20, 30, and 40 in C2:C5, then use A2:A11 as the Input Range and C2:C5 as the Bin Range. The histogram counts are 2 for values up to 10, 3 for values above 10 through 20, 2 for values above 20 through 30, 2 for values above 30 through 40, and 1 for values above 40.

The count can be checked against the original list: 4 and 8 fall in the first group; 12, 17, and 19 in the second; 23 and 28 in the third; 31 and 36 in the fourth; and 42 exceeds the last limit. The category labels in the output may show the boundary numbers rather than polished interval wording, so add explanatory labels in a separate presentation table if the audience needs to understand the exact ranges.

This example also shows why the last bin should not be mistaken for a cap on the data. A boundary of 40 does not discard 42; it is counted in the final More category. If you want every observation to fall into a regular interval, choose a final limit at least as large as the maximum value, or explain the overflow category clearly.

Bin range versus histogram chart bins

Excel has two related histogram workflows, and the term bin range can cause confusion. The Analysis ToolPak uses a worksheet range of explicit upper limits. A chart created with Insert > Insert Statistic Chart > Histogram instead groups the selected data using chart-axis settings. It does not ask you to select a worksheet bin range in the ToolPak sense.

To adjust a chart histogram in desktop Excel, select or right-click its horizontal axis and choose Format Axis. Under Axis Options, use the Bins controls to set a Bin width or Number of bins. You can also set an Underflow bin or Overflow bin when those controls are available. A bin width defines the size of intervals; a number of bins asks Excel to divide the data into a chosen count of groups. These settings are different ways to control chart grouping, not a list of upper-bound cells.

The chart may start with automatic grouping. That can be convenient for a quick view, but the resulting intervals may not match a reporting standard or a previous chart. Specify a width or number of bins when repeatable comparisons matter, and check the axis labels to confirm the intervals are sensible. If you need particular boundaries such as 10, 20, 35, and 50, use the ToolPak bin range or build a separate frequency table rather than assuming a fixed-width chart bin setting can reproduce irregular cutoffs.

How to choose the number and size of bins

There is no single bin range that is best for every data set. Too few bins can hide clusters or differences; too many can create a jagged chart with only one or two observations per group. Start with the purpose of the analysis: a quality-control report may require established tolerance bands, while an exploratory view may call for a few alternative widths to see whether a pattern persists.

Consider the spread and precision of the measurements. For whole-number test scores, decimal cutoffs may add complexity without adding useful information. For measurements recorded to tenths, boundaries aligned to tenths may be meaningful. Also consider the sample size: a small set of observations cannot support a detailed-looking distribution reliably. Try a defensible set of bins, then compare a nearby coarser or finer grouping rather than treating one chart as definitive.

For comparisons across periods or groups, use the same boundaries for each histogram. If Excel chooses a different automatic scale for each chart, visual differences can reflect the binning rather than changes in the underlying data. Fixed boundaries make the counts comparable, provided the groups and measurement units are also consistent.

Common problems and checks

  • Counts seem to be missing: Look for values greater than the final boundary in the More category. Expand the bin range if you want them represented by regular intervals.
  • Bins appear in the wrong order: Sort the limits ascending. A bin range is a sequence of increasing upper limits, not a set Excel should reorder for you.
  • A heading is counted or causes an issue: Include the Labels option when the selected range contains a header. Otherwise, select only numeric cells.
  • Numbers are treated unexpectedly: Check for numbers stored as text, stray spaces, or nonnumeric entries in the input. Clean the source data and confirm that its units are consistent.
  • The output is hard to interpret: Write interval descriptions alongside the ToolPak output. A displayed limit such as 20 represents the group above the prior limit through 20, not a single score.

Blank cells are generally not observations to count, but a worksheet with mixed text and numeric entries deserves review before analysis. Do not silently remove unusual values just because they make the chart look untidy; first establish whether they are data-entry mistakes or valid extreme observations. If they are valid, retain them and make the scale or overflow group clear.

Alternatives when fixed bins are not enough

For a quick visual distribution, use the built-in Histogram chart and adjust its axis bins. For a reproducible table with exact boundaries, use the Analysis ToolPak. If you need custom labels, special treatment of endpoints, or a calculation that updates within a larger report, you can construct a frequency table with COUNTIFS or another appropriate method and chart those counts. The formulas must reflect the same endpoint rules as the intended intervals; otherwise, adjacent groups may double-count a boundary or omit it.

A pivot table is another option for data already organized into categories or dates, but it is not a direct substitute for choosing arbitrary numeric cutoffs in every case. The best method depends on whether the priority is fast charting, precise fixed intervals, custom presentation, or an analysis that refreshes with changing data. Keep a record of the boundaries used so another reader can reproduce the result.

Frequently Asked Questions

What should I put in the Excel histogram bin range?
Enter numeric upper limits in ascending order, one per cell. For example, 10, 20, and 30 create groups ending at those values, followed by a separate group for values above 30.

Does Excel include the bin number in the count?
Yes. In the ToolPak histogram, each boundary is included as the upper end of its group. With limits of 10 and 20, a value of exactly 20 belongs in the group above 10 through 20.

What happens to values above the last bin?
The Analysis ToolPak reports them in a final More category. Increase the final limit if you want the data covered by regular bins, or retain the overflow count and label it clearly.

Can I use a bin range with an Excel Histogram chart?
Not in the same way as the ToolPak. A Histogram chart uses axis settings such as Bin width or Number of bins. Use the ToolPak when you need a worksheet range of explicit, potentially uneven upper limits.

Why does my histogram show an extra category?
The extra category may be the ToolPak’s More group, which counts observations above the largest limit. It may also be an axis grouping in a chart. Inspect the output type and the final boundary to identify which applies.

Should I use the same bins for two histograms?
Usually, yes, when the purpose is a direct comparison. Matching boundaries make counts and distribution shapes easier to compare; automatic bins that differ between charts can make similar data look different.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.