Excel Histogram Custom Bins: How to Set Them

Charts & pivot tables · Updated November 19, 2026

In Excel for Microsoft 365 on Windows, select your numeric data, choose Insert > Insert Statistic Chart > Histogram, then right-click the horizontal axis and choose Format Axis to set a bin width, number of bins, underflow bin, or overflow bin. If you need uneven custom boundaries, Excel’s built-in histogram chart cannot specify every boundary independently; create a frequency table with formulas and chart that table instead.

What custom bins mean in an Excel histogram

A bin is an interval that groups numeric observations. A histogram displays how many values fall in each interval, so changing the bins can make a distribution easier—or harder—to interpret. For example, a chart of delivery times might group results in five-minute intervals, while a chart of exam scores might use ten-point intervals.

Excel offers two different meanings of “custom bins,” and the distinction matters. In a built-in histogram chart, you can control the number or width of bins and set optional limits for values at the low or high end. Those controls produce regular, equally spaced intervals. They do not let you enter a different width for each bin. If you need intervals such as 0–5, 5–20, and 20–100, make a frequency table with your own boundaries and chart its counts.

Do not confuse a histogram with an ordinary column chart. A histogram is designed for a numeric variable whose values are grouped into adjacent intervals. A column chart generally compares separate categories, such as product names or departments. The bars in a histogram usually touch because the intervals are continuous; category columns usually have gaps.

Set the bin width or number of bins

To create and adjust a standard histogram in Excel for Microsoft 365 on Windows:

  1. Put the observations in one column, with a descriptive heading above them if useful. Select the numeric data, excluding unrelated notes and totals.
  2. Choose Insert > Insert Statistic Chart > Histogram. Excel inserts a chart and chooses an initial grouping automatically.
  3. Right-click a horizontal-axis label and choose Format Axis. In the Format Axis pane, open Axis Options and find the Bins controls.
  4. Choose Bin width and enter a positive number, or choose Number of bins and enter how many intervals you want. Excel recalculates the other interval details for the chart.

Use Bin width when the interval has a meaningful real-world size: for example, 5 minutes, 10 dollars, or 2 degrees. Use Number of bins when you have a target level of detail but no natural interval size. A fixed width makes it easier to compare charts built from different datasets; a fixed count can be useful when you want a single chart to show a manageable number of groups.

These choices are alternatives, not separate controls for defining irregular intervals. If you change the data range or the number of observations, a chosen width remains the width, while a chosen bin count remains the requested count and Excel adjusts the grouping accordingly. Inspect the axis labels after changing either setting; a plausible-looking chart can still use intervals that do not suit the question.

Choose boundaries that answer the question

There is no universally best number of bins. Too few can hide clusters, gaps, or skew; too many can make random variation look like a meaningful pattern. Start with the purpose of the chart and the scale of the measurement. If a manager needs to know how many orders took less than an hour, a boundary at 60 minutes may be more useful than a collection of arbitrary intervals.

  • Use a meaningful unit. For a measurement recorded in whole minutes, a width of 0.01 minutes adds visual detail without necessarily adding useful information.
  • Check the sample size. A small dataset divided into many bins will produce several empty or nearly empty bars. That is not automatically an error, but it can obscure the overall distribution.
  • Keep comparisons consistent. When comparing two groups, use the same boundaries and axis scale. Letting each chart choose its own bins can make similar distributions appear different.
  • Consider the audience. Clearly labelled intervals such as 0–10 and 10–20 are often easier to explain than numerous narrow decimal ranges.

Try a few defensible settings and compare the resulting shape. The histogram is a summary, not proof that the data follow a particular pattern. Retain the original observations and explain the bin width when the chart will inform a decision.

Use underflow and overflow bins

The Format Axis pane can also group values at the ends of the distribution. An underflow bin collects values at or below a threshold you specify; an overflow bin collects values above a specified threshold. These options are useful when a few unusually small or large observations would otherwise stretch the axis and compress the main body of the data.

For example, a service-time chart might use an overflow threshold of 60 minutes to collect all longer waits together. This keeps the ordinary range readable while showing that long waits exist. It also hides the individual differences among values in that tail, so use it only when that loss of detail is acceptable. Make the threshold clear in the chart or its explanation.

End bins are not a substitute for checking unusual data. Before grouping an extreme value, confirm that it is a real observation rather than a typing error or a value stored in an unexpected unit. Also remember that an underflow or overflow group changes the way the tail is displayed; it does not remove those observations from the underlying data.

Create uneven custom bins with a frequency table

For irregular intervals, make a table of lower limits, upper limits, and counts, then chart the counts. This approach gives you explicit control over the boundaries and makes the results auditable. For a small example, suppose A2:A11 contains 4, 7, 9, 12, 14, 18, 21, 22, 27, and 31. Put lower limits 0, 10, 20, and 30 in B2:B5, and upper limits 10, 20, 30, and 40 in C2:C5. The intervals are (0,10], (10,20], (20,30], and (30,40].

With those data in A2:A11, the lower limits in B2:B5, and the upper limits in C2:C5, enter this formula in D2 and fill it down through D5:

=COUNTIFS($A$2:$A$11,">"&B2,$A$2:$A$11,"<="&C2)

The formula returns counts of 3, 3, 3, and 1: three values fall in (0,10], three in (10,20], three in (20,30], and one in (30,40]. The intervals deliberately use an exclusive lower limit and inclusive upper limit, so a value exactly equal to 10 is counted in the first interval, not the second. Choose and document a boundary convention that fits your use; make sure the ranges cover all expected observations and do not overlap or leave gaps.

To chart the result, create a label for each interval and place the labels beside the counts. Select the labels and counts, then choose Insert > Insert Column or Bar Chart > 2-D Column. To make the columns look more like adjoining histogram bars, right-click a column, choose Format Data Series, and reduce Gap Width. This remains a column chart based on a frequency table, rather than Excel’s built-in histogram chart, but it is the practical method for nonuniform intervals.

Use the Analysis ToolPak when you need a frequency output

Excel also includes a Histogram command in the Analysis ToolPak. It is useful when you want a frequency table as well as an optional histogram chart. On Windows, if Data > Data Analysis is not available, enable the add-in through File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins, choose Go, select Analysis ToolPak, and choose OK.

Then choose Data > Data Analysis > Histogram. Set the Input Range to the observations and the Bin Range to a separate, ascending list of bin upper limits. Choose an output location and, if desired, select the chart output option. The ToolPak treats the listed values as upper limits: the first frequency counts observations at or below the first limit, later frequencies count values above the preceding limit and at or below the current one, and an additional “More” group can count values above the final limit.

The ToolPak output is a calculated result, not the same kind of live chart linked directly to every source observation. If you change the data or boundaries, rerun the analysis to refresh the frequency output. For a chart that should update as source data changes, the built-in histogram chart or a formula-driven summary is usually more convenient.

Common problems and checks

  • The Histogram option is missing. Select a suitable numeric data range and look under Insert > Insert Statistic Chart. If you want the separate Data Analysis command, enable the Analysis ToolPak; it is not required for the built-in histogram chart.
  • The chart has an unexpected number of values. Check for numbers stored as text, blank cells, error values, headers included as observations, or totals mixed into the source column. Clean the source range before interpreting the chart.
  • The bars do not match the intervals you expected. Reopen Format Axis and check whether the chart is set to Bin width or Number of bins. Also check underflow and overflow settings and whether your chosen boundary convention includes exact threshold values where you expect.
  • You need different widths for different intervals. This is a limitation of the built-in chart’s bin controls, not a hidden setting. Use a custom frequency table and a column chart, or use the ToolPak with explicit upper limits when its interval convention fits your needs.
  • The chart changes after filtering or editing. Confirm which cells the chart references and whether the source range includes the new records. A chart cannot count observations that are outside its source data.

Excel on Mac and Excel for the web

In current desktop versions of Excel for Mac, histogram chart creation and axis formatting are available, although the ribbon arrangement and pane presentation can differ from Windows. Look under Insert for a statistical chart, then select the chart axis and inspect its formatting options for bin controls. The Analysis ToolPak is also available for Mac versions that support Excel add-ins; enable it through Tools > Excel Add-ins, then select Analysis ToolPak. Exact labels can vary by release.

Excel for the web may expose different chart creation and formatting controls from the Windows desktop application, and availability can change as the web app is updated. If you cannot find the bin settings or need the ToolPak workflow, open the workbook in desktop Excel. The formulas-and-frequency-table method is a useful alternative when you need more explicit control and can use ordinary worksheet formulas.

Frequently Asked Questions

Can I enter custom bin edges directly in an Excel histogram chart?
No. The built-in chart lets you set a bin width or a number of bins, plus optional underflow and overflow thresholds. To set a different boundary for every interval, calculate the frequencies in a table and chart them.

What is the difference between bin width and number of bins?
Bin width sets the size of each regular interval; number of bins sets how many intervals Excel should use. Choose width when the interval has a meaningful unit, and choose count when you want to control the chart’s level of detail.

Does the Analysis ToolPak use bin values as lower or upper limits?
It uses them as upper limits. The first bin counts observations at or below its limit; each following bin counts values above the previous limit and at or below its own limit.

Why are my histogram bars different from my custom table counts?
The built-in chart may be using different boundaries or a different grouping setting. Check its axis options and compare the exact interval edges and treatment of boundary values with the rules used in your frequency formulas.

Can histogram bins have different widths?
Not as independently specified intervals in the built-in histogram chart. Use a frequency table with your own limits and a column chart when unequal widths are necessary, and label the intervals clearly.

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.