Edit Histogram Bins in Excel: Width, Count, and Limits

Charts & pivot tables · Updated November 18, 2026

To edit histogram bins in Excel for Microsoft 365 on Windows, select the chart’s horizontal axis, open Format Axis, and change the bin width or number of bins under Axis Options. You can also set an underflow or overflow bin to group values below or above a limit.

What a histogram bin controls

A histogram groups numerical observations into intervals, or bins, and displays how many observations fall in each interval. The bar heights show frequencies; the bin boundaries determine which values are counted together. Changing the bins can make a distribution easier to understand, but it can also change the impression the chart gives. A very small bin width may produce many short bars, while a large width may conceal important differences.

Excel’s built-in Histogram chart lets you adjust bins after creating the chart. This is different from a regular column chart: in a histogram, the horizontal axis represents ranges of numeric values, and the bars touch to show that the ranges are continuous. Bin settings belong to the chart’s horizontal axis, not to the vertical axis or the data series.

Create a histogram before editing it

Arrange the observations in one column, preferably with a descriptive heading and one numeric measurement per row. For example, a column called Delivery time might contain values such as 18, 22, 25, and 31. Select the heading and values, then use Insert > Insert Statistic Chart > Histogram. Excel creates a chart and chooses an initial grouping automatically.

If the chart does not look right, first confirm that Excel has treated the horizontal axis as the bin axis. Click the axis labels along the bottom of the chart, rather than a bar or the vertical frequency axis. Right-click and choose Format Axis. In the pane, expand Axis Options and find the bin controls. You can also select the horizontal axis and press Ctrl+1 on Windows to open its formatting pane.

On Excel for Mac, select the horizontal axis and open its formatting options, commonly by Control-clicking or right-clicking the axis and choosing Format Axis. Ribbon wording and pane layout can vary slightly by Excel release. Excel for the web may display or permit some chart edits, but the desktop app is the more dependable choice when you need the full set of histogram axis controls.

Change the bin width

Choose Bin width when you want bins of a particular size, such as groups of 5 units, 10 minutes, or 100 dollars. In the Format Axis pane, select the Bin width option and enter a positive number. Excel redraws the chart using intervals of that width. The exact first and last intervals depend on the data range, so check the labels after changing the value rather than assuming every boundary will start at zero.

For example, a chart of test scores might use a width of 10 to show the distribution in roughly ten-point groups. A chart of call durations measured in seconds might use a width of 30 to compare half-minute intervals. The appropriate width depends on the measurement and the question: a width of 10 is sensible for scores out of 100, but would be too broad for measurements that vary only by a few units.

Excel expects a numeric width appropriate to the values being charted. If the observations are dates, remember that Excel stores dates as serial numbers. A width of 7 therefore corresponds to seven days in date-based data, though the displayed axis labels may need attention. If the axis labels are hard to interpret, consider whether a date-oriented chart or a grouped frequency table would communicate the time periods more clearly.

Choose a number of bins

Select Number of bins when you know how many bars you want across the data range but do not need each interval to have a particular width. Enter the desired count in the Format Axis pane. Excel divides the range into that many bins; because the overall span depends on the minimum and maximum values, the resulting widths are determined by the data.

There is no universally correct number of bins. Too few can flatten out clusters or gaps, while too many can make random variation look meaningful. Try a couple of reasonable settings and compare whether the broad pattern remains visible. For a small dataset, a modest number of bins is often easier to read; for a larger dataset, more detail may be useful. Explain the chosen grouping when the chart is used in a report so readers can interpret it consistently.

Automatic binning is convenient for a first look, but it is not a guarantee that Excel’s choice suits your analysis. A chart that updates automatically as data changes can also end up with a different-looking scale. If comparisons across several charts matter, set a consistent width or use shared, explicit boundaries rather than relying on each chart’s automatic settings.

Set underflow and overflow bins

An Underflow bin groups values at or below a limit into one end category, and an Overflow bin groups values above a limit into the other end category. These options are useful when a few unusually small or large observations would otherwise stretch the chart and make the central pattern difficult to see. Enter the threshold in the relevant control in the Format Axis pane.

For instance, if most delivery times fall between 10 and 60 minutes but a few take much longer, an overflow bin can collect the long delays above a chosen threshold. This keeps the main part of the distribution readable while still showing that extreme values exist. Do not use an end bin to hide inconvenient observations: retain the underlying data and make the grouping clear in the chart or its caption.

Underflow and overflow categories are not substitutes for checking unusual data. A large value may be a genuine exception, a unit-entry mistake, or a data-import problem. Inspect the records before deciding how to present them. The chart’s grouped bar does not reveal how many different values are inside the end category unless you read its frequency or inspect the source observations.

Use explicit boundaries with the Analysis ToolPak

If you need particular cutoffs rather than automatically spaced bins, Excel’s Analysis ToolPak offers a Histogram analysis. On Windows, go to Data > Data Analysis > Histogram. Provide the input data range and a separate bin range containing the upper limits you want, then choose an output location. If the ToolPak command is missing, enable Analysis ToolPak through File > Options > Add-ins, select Excel Add-ins, click Go, and check the add-in.

For example, suppose the observations are 4, 9, 10, 11, 18, 20, 21, 27, 30, and 34, and the bin range contains 10, 20, and 30. The resulting groups are values up to 10, values greater than 10 and up to 20, values greater than 20 and up to 30, and values above 30. Their frequencies are 3, 3, 3, and 1, respectively. This illustrates an important detail: ToolPak bin values act as upper limits, and the last group catches values above the final listed limit.

The ToolPak output is a frequency table, and it can also create a chart. Unlike a built-in Histogram chart linked to the original observations, the analysis output does not recalculate as a live histogram simply because the source values change. Rerun the analysis to update it. If you change the cutoff values, check that the bin range is in ascending order and that the output communicates the interval boundaries clearly.

Troubleshoot bin settings and chart behavior

If the bin controls are missing, the most common cause is selecting the wrong chart element. Click the labels along the horizontal axis and reopen Format Axis. If you inserted a standard column chart instead of a Histogram chart, the axis may not offer histogram bin controls; use Insert > Insert Statistic Chart > Histogram to create the appropriate chart.

  • Bars do not match the expected counts: Check for text values, blanks, duplicated records, or numbers stored as text. Confirm that the selected range contains the observations you intend to analyze.
  • The chart has too many or too few bars: Switch between Bin width and Number of bins, then inspect the labels and the overall shape.
  • One bar dominates the chart: Check for a wide range or extreme observations. Consider an overflow bin only if the threshold is meaningful and disclosed.
  • Axis labels look confusing: Make the chart wider, simplify the number format, or use a frequency table with clearly written intervals.
  • Changes do not appear in a ToolPak result: Rerun the Histogram analysis; its output is not a live recalculation of the source data.

Also distinguish a count from a bin width. Entering 10 under Number of bins asks Excel for ten groups. Entering 10 under Bin width asks for groups ten units wide. Those instructions can produce very different charts, especially when the data span changes.

Built-in Histogram chart or ToolPak?

Use the built-in chart when you want to explore a distribution quickly and adjust its binning interactively. It remains connected to the selected source data, so chart changes and many source edits are easier to manage together. Use the ToolPak when you need a frequency table with specified upper limits or need the counts as cells for another calculation or report.

A regular column chart can be a suitable alternative when the categories are already defined, such as named age bands or product groups. In that case, calculate or summarize the category counts first and chart those categories. A histogram is intended for numerical observations grouped into intervals; it is not automatically the best choice for labels that have no numeric order or meaningful spacing.

Frequently Asked Questions

Can I edit histogram bins after creating the chart?
Yes. Select the horizontal axis, open Format Axis, and change Bin width, Number of bins, or the underflow and overflow limits. If those controls are absent, check that you selected a Histogram chart and clicked its horizontal axis.

How do I make every bin the same size?
In the Format Axis pane, select Bin width and enter the interval size you want. Check the axis labels afterward, because the chart’s starting boundary depends on the data range and may not be the round number you expected.

Can I choose exact bin cutoffs in an Excel histogram?
The built-in chart focuses on automatic grouping, width, count, and end limits. For a frequency table with specific upper boundaries, use Data > Data Analysis > Histogram in the Analysis ToolPak and provide an ascending bin range.

Why does Excel put a value in a different bin than I expected?
Check the boundary rule and the displayed interval labels. In a ToolPak histogram, each listed bin value is an upper limit; for example, the 20 bin includes values above 10 and up to 20. Also verify that the cell is numeric and that its units match the rest of the data.

Will the histogram update when I add more data?
A built-in Histogram chart is linked to its source range, but it will not include newly added rows if they fall outside that range. Expand the chart’s source data or use an Excel Table as the source range so added rows can be included. ToolPak frequency output must be generated again to reflect changed data.

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.