Excel Histogram Bin Size: How to Set the Right Width

In Excel for Microsoft 365 on Windows, select the histogram’s horizontal axis, open Format Axis, and under Axis Options choose either Bin width or Number of bins. Bin width sets the range covered by each interval; the number of bins tells Excel how many intervals to use. The right choice depends on your data and the detail you want readers to see.

What histogram bin size means

A histogram groups numerical values into adjoining intervals, called bins, and shows how many observations fall into each interval. If you record exam scores, for example, bins might cover 0–9, 10–19, 20–29, and so on. The height of each bar represents the number of scores in its interval. Bin size usually means the width of one interval, not the number of bars.

These two settings are related but are not interchangeable. A smaller width generally creates more, narrower bars and reveals finer variation. A larger width creates fewer, wider bars and gives a broader overview. If your data span roughly 0 to 100, a width of 10 suggests about ten intervals, while a width of 20 suggests about five. The exact count can depend on where Excel places the boundaries and on the values in the data.

A histogram is not just a column chart with spaces removed. The bars represent ranges on a continuous numeric scale, and adjacent bars normally touch. The chosen bin boundaries can change the apparent shape of the distribution, so a width should make the pattern understandable without hiding meaningful differences or exaggerating random fluctuations.

Set bin width in an Excel histogram chart

For a built-in histogram, select the numeric data and choose Insert > Insert Statistic Chart > Histogram. Excel creates the chart and groups the values automatically. To replace its automatic grouping with a specific interval width:

Click the histogram’s horizontal axis, where the bin labels appear.
Right-click and choose Format Axis. You can also select the axis and use the chart formatting options to open the Format Axis pane.
In the pane, select Axis Options if it is not already selected.
Under Bins, choose Bin width and enter a positive number appropriate to the units in your data.
Review the chart and adjust the width if the labels or distribution are difficult to interpret.

For example, if a measurement is recorded in centimeters and a useful interval is 5 cm, enter 5 as the bin width. Excel will group the measurements into intervals of that width. The displayed endpoints and number of bars depend on the data range; check the axis labels rather than assuming a particular first or last boundary. To return to Excel’s automatic grouping, select Automatic under the Bins options.

On Excel for Mac, the histogram chart and axis-formatting controls are also available, although the pane layout can differ slightly. Excel for the web may not offer the same full chart-formatting controls as the Windows desktop app; if you cannot find the bin settings, open the workbook in desktop Excel.

Choose a number of bins instead

If you know how many bars you want but do not have a meaningful interval width in mind, use Number of bins in the same Bins section of the Format Axis pane. Excel divides the observed range into that many intervals. This is convenient for making charts with a similar level of detail, such as when comparing several groups with different ranges.

Choosing a count does not guarantee that the bins will have a width you would select yourself. If the data range is 0 to 100, ten bins will be roughly ten units wide; if the range is 0 to 1, the same count produces much narrower numeric intervals. For a meaningful unit-based grouping—such as every 5 years, 10 dollars, or 2 degrees—set the width directly instead.

The automatic option is a reasonable starting point, not a universal definition of the best histogram. Excel determines the grouping for the data and may produce a different number of bars when the values or data range change. If consistent bin boundaries matter across charts, specify a width or create a controlled frequency table rather than relying on automatic settings.

How to choose a useful width

There is no single bin size that is correct for every dataset. A useful width depends on the number of observations, the spread and precision of the measurements, the question you are asking, and the audience. Try a few plausible settings and look for a view that shows the important shape without making the chart noisy.

Start with the units. Choose intervals people can interpret, such as 5 years, 10 minutes, or $100, rather than an awkward width with excessive decimal places.
Consider the data range. A width that works for values from 0 to 50 may produce too many bars for values from 0 to 5,000.
Account for the sample size. With a small sample, numerous narrow bins can leave most bars empty or nearly empty. With a very large dataset, wider bins can conceal useful structure.
Match the purpose. A summary for a presentation may need broader intervals; an exploratory analysis may benefit from more detail.
Check whether comparisons are fair. When comparing distributions, use the same bin width and boundaries where practical. Different groupings can make similar datasets look unlike one another.

Imagine you have 240 delivery times, measured in minutes, ranging from 12 to 86. A width of 10 gives a readable broad summary, while a width of 2 may show more detail but could make each bar represent only a few deliveries. If the question is whether most orders arrive within a particular ten-minute band, width 10 may fit. If you are studying small operational changes, a narrower width may be worth examining alongside the broader view. The example is a decision aid, not a rule that a certain sample size requires a particular width.

Use overflow and underflow bins for extremes

Excel’s histogram axis options can also group extreme values into an overflow or underflow bin. An overflow bin collects values above a limit you specify; an underflow bin collects values below a limit. These options can keep a few unusually high or low values from stretching the scale and making the main cluster harder to inspect.

Use these bins only when grouping the extremes suits the analysis. They do not remove those observations; they combine them into an endpoint category. Make the limit clear in the chart or its notes, because a label such as “greater than 100” communicates more than an unexplained last bar. If the extreme values are errors, investigate and correct the source data rather than concealing them through a bin setting.

Histogram chart versus the Analysis ToolPak

The built-in Histogram chart is generally the easiest option when you want a chart that can be formatted directly. The separate Analysis ToolPak Histogram tool is useful when you need a frequency table or want to specify bin limits explicitly. It is not the same interface as the chart’s Bin width setting.

On Windows, if the ToolPak is not available, enable it through File > Options > Add-ins. At the bottom, select Excel Add-ins beside Manage, choose Go, check Analysis ToolPak, and confirm. Then use Data > Data Analysis > Histogram. The Mac add-in controls are reached through Excel’s Tools > Excel Add-ins menu, though availability and layout can vary by installation.

In the ToolPak, you provide the input range and, optionally, a bin range. The bin range contains upper limits, not interval widths. For instance, limits of 10, 20, and 30 define successive groups up to those limits; observations above the final limit are reported in a final “More” category. This differs from entering a width of 10 in a chart, where Excel creates intervals using that width. If you need a precise set of boundaries, the ToolPak approach can be easier to control, but make sure the bin labels and the interpretation of each upper limit are clear.

Common bin-size problems

There are too many bars. Increase the bin width or reduce the number of bins. Then check whether the broader view still answers your question. A chart with a bar for nearly every distinct value is often difficult to read as a distribution.

The chart hides a cluster or creates a jagged shape. Try a smaller width to reveal detail, but compare it with a slightly larger width. A peak that appears only under one narrowly chosen grouping may not be a robust feature of the data.

The labels do not match the categories you expected. Confirm that you changed the horizontal axis settings and inspect the actual boundaries. A bin width is an interval size, not a command to start at a particular value. If the first boundary must be a specific number, use explicit limits with the ToolPak or build a frequency table with deliberately chosen categories.

The histogram does not include a value you expected. Check that the source range contains the value and that it is numeric rather than text. Also inspect underflow and overflow settings and any filters applied to the data. Blank cells and text entries are not numerical observations to be grouped like valid measurements.

Two histograms look different even though the data are similar. Check their widths and boundaries before drawing conclusions. Different bin origins can split observations differently even when widths match. For a fair comparison, use the same category limits, and avoid comparing charts whose automatic settings have changed independently.

Frequently Asked Questions

What is the best bin size for an Excel histogram? There is no universally best width. Choose one that fits the measurement units and question, then compare a few reasonable alternatives. The bars should reveal useful structure without being so narrow that random variation dominates or so broad that important differences disappear.

How do I change the number of bins in Excel? Select the histogram’s horizontal axis, open Format Axis, and under Axis Options > Bins choose Number of bins. Enter the count you want. To set the interval size directly instead, choose Bin width.

Why does Excel change the bin size automatically? With Automatic selected, Excel chooses grouping for the current data. If the values or range change, the chart may use different intervals. Enter a bin width or a bin count when you need more control or consistent formatting.

Is bin width the same as the number of bins? No. Width is the size of each interval in the data’s units; number of bins is the count of intervals. The width and the data range together affect how many bars appear, while the requested count leads Excel to divide the range into intervals.

Can I set exact histogram boundaries in Excel? Yes. The Analysis ToolPak Histogram tool accepts a bin range of upper limits, which lets you specify the category cutoffs. Remember that these are limits, not widths, and that values above the last listed limit appear in a final “More” category.

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.