Excel Histogram Bin Number: How to Choose It

In an Excel histogram, the bin number is the number of intervals used to group your numeric data. To change it in Microsoft Excel for Microsoft 365 on Windows, select the histogram’s horizontal axis, open Format Axis, and under Axis Options > Bins choose Number of bins and enter a value.

What a histogram bin number means

A histogram summarizes a set of numbers by counting how many observations fall into each interval, or bin. If a chart has 8 bins, it divides the data’s value range into 8 groups and plots a count for each group. The bin number is therefore about the chart’s grouping, not how many observations are in your data and not the value of a particular data point.

For example, if you have 80 delivery times measured in minutes, a histogram with 8 bins might show how many deliveries took 0–5 minutes, 5–10 minutes, and so on, depending on the data range and Excel’s calculated interval width. Each bar’s height represents the number of observations in that interval. A bin with no observations may appear as an empty interval.

Changing the bin number changes the view of the same data, not the underlying values. Fewer, wider bins make broad patterns easier to see but can conceal detail. More, narrower bins reveal local variation, but may make random fluctuations look meaningful. There is no universally correct bin number for every dataset.

How to set the number of bins in an Excel chart

In the Windows desktop version of Excel for Microsoft 365, create a histogram from a single numeric data series, then set its bin count:

Select the numeric data, including its heading if it has one.
Choose Insert > Insert Statistic Chart > Histogram.
Click the horizontal axis labels along the bottom of the chart.
Open the Format Axis pane. You can right-click the axis and choose Format Axis.
In Axis Options, find Bins, select Number of bins, and type the number you want.

Excel adjusts the interval widths to fit the data range when you specify a bin count. The result may not have round-number boundaries. If clear boundaries matter more than a particular number of groups, choose Bin width in the same Bins section instead and enter a useful interval size. For example, a width of 10 groups measurements into intervals ten units wide.

Excel for Mac also supports histogram charts and axis formatting, though the ribbon and Format Axis pane can look different by release. Select the chart axis, open its formatting controls, and look for the Bins options. Excel for the web may offer fewer chart-formatting controls than the desktop app; if you cannot find the bin settings, open the workbook in desktop Excel.

Number of bins versus bin width

These two settings answer different questions. Number of bins tells Excel how many intervals to use; Excel determines the width needed to cover the range. Bin width tells Excel how large each interval should be; the resulting number of bins depends on the data’s spread. If the data ranges from 2 to 102 and you set a width of 10, you will get roughly ten intervals, with the exact displayed grouping depending on the chart’s boundaries.

Use a fixed bin number when you need a consistent number of groups, such as when preparing several charts for a presentation. Use a fixed width when the interval has a real-world meaning or when two datasets need directly comparable boundaries. For example, comparing customer ages in two regions is easier if both charts use five-year intervals than if each chart independently chooses a different number of groups.

Do not assume a setting of 10 means ten bars will always be visible. Underflow and overflow bins, empty intervals, and the distribution’s range can affect what appears. Inspect the axis labels and the chart after changing the setting, especially when comparing charts or communicating results.

How to choose a useful bin number

Begin with the question the chart should answer. A chart intended to show the overall shape of a large dataset can use broader groups; one intended to reveal a cluster or a long tail may need narrower intervals. Try a few reasonable settings and check whether the main pattern remains visible. A dramatic change in the story when you move from, say, 8 to 9 bins is a reason to describe the chart cautiously.

Consider these practical factors:

Sample size: A small dataset may not support many narrow bins; most intervals would contain few or no values.
Measurement precision: If values are recorded to the nearest whole unit, extremely fine decimal intervals may suggest more precision than the data has.
Range and outliers: A few extreme values can stretch the scale and compress the central data. Check whether an outlier is valid before deciding how to display it.
Comparisons: Keep bin boundaries consistent across datasets when readers need to compare bar heights.
Purpose: Use boundaries that are easy to explain, such as every 5 minutes or every $100, when that fits the subject.

Statistical rules of thumb, including Sturges’ rule and the Freedman–Diaconis rule, can suggest a starting point, but they are not commands Excel’s automatic setting is guaranteed to follow. Excel’s Automatic option chooses a grouping for the chart; it does not establish that the result is statistically optimal. For a formal analysis, document your method and consider whether the bin choice affects the conclusion.

What the bin boundaries and bars represent

A bin is an interval of values, and each bar shows the count assigned to that interval. Excel’s histogram chart uses adjacent intervals along a numeric axis. In the usual upper-bound interpretation, values are grouped up to a bin’s displayed upper limit; the next interval covers values above that limit through its own upper limit. Read the axis labels carefully, because formatting and the selected bin options determine how the intervals are presented.

Excel also provides Underflow bin and Overflow bin settings in the chart’s Bins options. These let you place values at or below a chosen threshold into an underflow group, or values above a chosen threshold into an overflow group. They can be useful when a few unusually small or large observations would otherwise dominate the axis. They do not delete or change those observations; they group them for display.

For instance, if most customer wait times fall between 0 and 30 minutes but a few are several hours, an overflow threshold can keep the ordinary range readable while still showing that longer waits exist. State the threshold clearly when presenting the chart so readers understand what the final category includes.

Histogram chart versus the Analysis ToolPak

Excel has two common ways to make a histogram, and their bin controls are not the same. The Insert Statistic Chart > Histogram option creates a chart whose axis can be set to Automatic, Bin width, Number of bins, Underflow bin, or Overflow bin. This is generally the more direct choice when you want to format and revise a chart.

The Analysis ToolPak creates a frequency table from your data and optional bin range. To enable it on Windows, choose File > Options > Add-ins. At the bottom, select Excel Add-ins beside Manage, choose Go, check Analysis ToolPak, and select OK. Then use Data > Data Analysis > Histogram. The dialog asks for an Input Range and, if you provide one, a Bin Range.

In the ToolPak, the bin range contains upper limits, usually listed in ascending order. If the limits are 10, 20, and 30, the output reports counts through those limits and includes a More category for values above the last limit. The ToolPak does not ask for a number of bins in the same way as the chart axis; the number and meaning of groups follow the supplied limits. This is useful when you need a frequency table or precise, predefined cutoffs. It is also a common source of confusion: entering bin limits is not the same as typing a bin count.

Common bin-setting problems

If the chart shows unexpected groups, first confirm that Excel is using the right data. A heading, blank cells, text stored among the numbers, or an unintended second series can change the result or prevent a clean histogram. Check the selected source range and make sure the observations are numeric. If the chart was created from a table, confirm that its data range includes the rows you intended.

If you cannot find Number of bins, make sure you selected the horizontal axis rather than a bar, the chart title, or the vertical value axis. The Bins controls belong to the histogram’s category axis. Also confirm that the chart type is Histogram; a column chart made from a frequency table does not have the same automatic bin controls.

If the chart looks misleading, test both a smaller and a larger bin count, inspect outliers, and consider using a fixed width. Avoid choosing bins solely to make a preferred pattern appear. When presenting results, include the units and interval width or boundaries, and do not treat the histogram as a replacement for examining the original data when exact values matter.

Frequently Asked Questions

How many bins should I use in an Excel histogram?
There is no single best number. Start with Excel’s Automatic setting, then try several counts and choose one that makes the distribution understandable without hiding important features. Consider sample size, measurement precision, outliers, and whether you need to compare this chart with another.

What does Number of bins mean in Excel?
It tells the histogram chart how many intervals to divide the numeric range into. It does not set the number of data points in each interval. Excel calculates counts from the values that fall into each bin.

Why does Excel show different bin widths when I change the bin number?
When you choose Number of bins, Excel adjusts the width to cover the data range with the requested groups. Choose Bin width instead if you want intervals of a specified size.

How do I make Excel histogram bins start at zero?
Use the Underflow bin option to group values at or below a threshold, or adjust the bin width and inspect the chart’s boundaries. If exact cutoffs are essential, create a frequency table with the Analysis ToolPak and supply explicit bin upper limits.

Are Excel histogram bins the same as ToolPak bins?
No. A histogram chart’s Number of bins setting specifies how many intervals Excel should use. In the Analysis ToolPak, a Bin Range lists the upper limits for the frequency groups, and values above the last limit are reported in a More category.

Choosing a setting that readers can interpret

The best Excel histogram bin number is the one that communicates the data’s distribution clearly and fairly. Use Number of bins when you want control over the count of intervals, Bin width when the interval size matters, and explicit ToolPak limits when exact cutoffs are required. Before sharing the chart, verify its labels, units, boundaries, and treatment of unusually high or low values; those details make the counts meaningful to someone reading the chart.

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.