Excel Histogram: How to Set the Number of Bins Exactly

In Excel for Microsoft 365 on Windows, select the histogram’s horizontal axis, open Format Axis, and under Axis Options set Bins to Number of bins; enter the count you want. If you have not made the chart yet, select your numeric data and choose Insert > Insert Statistic Chart > Histogram.

Set the number of bins in an Excel histogram

A bin is one interval used to group numeric observations. To change how many intervals Excel uses in a histogram chart:

Click a cell in the column of numeric observations. Include a header if the column has one, but avoid selecting unrelated columns or summary rows.
Choose Insert > Insert Statistic Chart > Histogram. Excel creates a chart and groups the values into intervals automatically.
Click the horizontal axis, which displays the intervals, and right-click it. Choose Format Axis.
In the Format Axis pane, expand Axis Options. Under Bins, select Number of bins and enter the desired count.

The chart updates to use that count of regular bins. The entry is a setting for the chart, not a change to the source data. To revise it later, return to the same axis setting. If you selected the vertical value axis by mistake, the bin controls will not appear; click the horizontal axis labels instead.

These steps describe Excel for Microsoft 365 on Windows. In Excel for Mac, select the horizontal axis and open its Format Axis pane; the pane or ribbon layout can look different. Excel for the web may not expose every desktop chart-formatting control. If the Number of bins setting is missing, open the workbook in the desktop app.

What bins mean and how Excel groups values

A histogram is a frequency chart: it shows how many observations fall within each numeric interval. For example, a histogram of delivery times might have bins for short, medium, and long durations. The height of a bar represents the number of observations in its interval, rather than an individual measurement.

When you choose a number of bins, you are asking Excel to divide the span of the data into that many regular intervals. The chart determines the bin boundaries from the data’s range and the chosen count. Consequently, changing the count changes the interval widths as well as the number of bars. A larger count generally produces narrower intervals; a smaller count produces wider ones.

That is different from choosing a particular bin width. A request for 10 bins means ten intervals, while a width of 10 means each interval spans ten units of the measurement, subject to the chart’s handling of the data range. If observations include decimal values, the axis labels may show boundary values that are not themselves observations. Read them as interval boundaries, not as a list of the original data points.

How to choose a useful bin count

There is no single best number of bins for every data set. The aim is to make the shape of the distribution visible without hiding important detail or making random variation look meaningful. Start with a moderate count, then compare the chart with a few nearby settings.

Consider the sample size. A few dozen measurements usually cannot support a very detailed histogram. Hundreds or thousands of observations may reveal useful patterns with more intervals.
Look at the data range. If most values occupy a narrow span but a few are extreme, the full range can squeeze the main cluster into a small part of the chart. Investigate unusual values before choosing a count.
Match the measurement. A histogram of whole-number ratings from 1 to 5 has limited meaningful detail. Measurements such as wait times or weights may justify finer divisions.
Check the story at neighboring counts. If a supposed peak or gap appears only at one setting and vanishes when the count changes slightly, avoid treating that feature as a firm conclusion.

As a starting point, a data set with about 100 observations might be viewed with roughly 8 to 12 bins, then adjusted to suit the measurement and the distribution. This is a practical trial range, not a rule Excel applies or a guarantee of statistical quality. For a report, explain a chosen interval size or rationale when readers need to compare charts.

A concrete example of adjusting bins

Suppose a teacher has 60 exam scores, and wants to see whether results cluster in the middle or at the extremes. With a low bin count, such as 4, the chart gives a broad overview: it is easy to see the general distribution, but smaller clusters may be combined. Setting 10 bins makes the view more detailed, but some bars may contain only a few scores, so small fluctuations become more noticeable.

To decide which view is useful, compare the chart at 4, 8, and 10 bins. If the broad pattern remains similar, that is reassuring. If the apparent shape changes sharply, describe the chart cautiously and inspect the raw scores or use a complementary summary. For instance, a frequency table can show the exact number of scores in chosen score bands. The right setting depends on the question: a teacher checking overall spread may prefer a simpler view, while one examining the shape near a pass threshold may need narrower intervals.

Number of bins, bin width, and end bins

Excel’s histogram axis offers controls that solve different problems. Choose Number of bins when you know how many bars you want. Choose Bin width when the interval size matters more than the final bar count. For example, when monitoring measurements recorded in seconds, a width of 5 seconds can make comparisons easier to explain than an arbitrary number of intervals.

The number of regular bins and the width are alternatives for setting the histogram’s divisions; do not assume that both choices can be fixed independently. Excel derives the other aspect from the data range and your chosen setting. If the axis labels are difficult to interpret, check whether a width that matches the units would be clearer.

The Underflow bin and Overflow bin options are useful when you want to group values at the low or high end into a catch-all category. An underflow cutoff gathers values at or below its specified limit; an overflow cutoff gathers values above its limit. These settings are not a substitute for choosing the regular-bin count. Use them when extreme values should be displayed together, and make the cutoff clear so the bar is not mistaken for an ordinary interval.

Common histogram problems and fixes

No histogram option appears. Check that the selected data contains numeric observations. Text labels in a separate column, blank rows, or mixed data can lead to an unexpected selection or chart. Select the measurement column rather than a title or summary table.
The chart has too many or too few bars. Excel starts with an automatic choice. Set Number of bins explicitly in Format Axis if you need a particular count, or choose Bin width if a fixed interval is more useful.
The bin controls are missing. Make sure you selected the horizontal axis on a histogram chart, not a vertical value axis or a different chart type. If the chart is a regular column chart, create a Histogram chart from the numeric observations.
One bar contains most of the values. Check for a wide range, a skewed distribution, or an extreme observation. Try a different bin count, and inspect the data rather than deleting an unusual value simply to improve the appearance.
Axis labels are hard to read. Increase the chart width, reduce the bin count, or choose a width that produces simpler boundaries. Avoid changing labels in a way that obscures which values each interval covers.

Also check that the chart is based on the intended records. A filtered table, a selected range that omits recent rows, or numbers stored as text can make a histogram represent something other than the data you meant to summarize.

Histogram chart versus the Analysis ToolPak

Excel offers another route through the Analysis ToolPak. Its Data > Data Analysis > Histogram tool creates a frequency table and can also create a chart. This is useful when you need a table of counts for further calculations, a cumulative percentage, or clearly specified interval cutoffs.

The ToolPak works differently from the built-in Histogram chart. Instead of entering a number of bins in the chart’s axis settings, you can supply a range of bin limits. The limits define the groups for the output, so make sure they are in ascending order and match the units of your observations. The output is a calculated result, whereas the chart created from source data can update as the source data changes.

On Windows, if the ToolPak is not available, enable it through File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins, choose Go, and select Analysis ToolPak. Then use Data > Data Analysis. For a quick visual distribution, the built-in Histogram chart is usually simpler; for a reproducible frequency table with chosen cutoffs, the ToolPak may be more suitable.

Reading and sharing the finished chart

A histogram is most helpful when its units, bin choices, and data scope are clear. Add a descriptive chart title and axis titles when the context is not obvious. State whether a bin count or a fixed width was selected if readers may compare the chart with another group or reporting period. Two histograms with different bin settings can look different even when they summarize the same observations.

Remember that a histogram shows distribution, not sequence. It can reveal concentration, spread, skew, or possible clusters, but it does not show the order in which measurements occurred. If timing or trends matter, use a time-based chart as well. For precise counts, pair the visual with a frequency table or a brief note explaining the interval boundaries.

Frequently Asked Questions

How do I change the bin size in an Excel histogram?
Click the horizontal axis, choose Format Axis, and under Axis Options > Bins select Bin width. Enter the interval width in the same units as your data. Use Number of bins instead when the desired number of intervals matters more than their width.

Can I set an exact number of bins in Excel?
Yes. In the Format Axis pane for a Histogram chart, select Number of bins and enter the count. Excel updates the regular intervals to use that count. Underflow and overflow settings can also group end values separately.

Why does Excel choose a different number of bins than I expected?
New histogram charts use an automatic setting, and Excel chooses intervals based on the data. The automatic result is not a promise of a particular count or a universal statistical optimum. Set Number of bins or Bin width yourself when you need consistent intervals.

Can I make a histogram with custom bin ranges?
Yes. Use the Analysis ToolPak’s Histogram tool and provide a range of bin limits, or create a frequency table using cutoffs suited to your analysis. The built-in chart’s Number of bins control specifies a count, not a list of individually chosen boundaries.

Does changing the bin count change my original data?
No. It changes how the chart groups observations, not the values in the worksheet. The chart’s appearance and frequency distribution can change, so keep the bin settings consistent when comparing charts.

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.