Excel Histogram Bin Range: Set or Change Bins

Charts & pivot tables · Updated November 19, 2026

To set an Excel histogram’s bins in Microsoft 365 for Windows, select the chart’s horizontal axis, choose Format Axis, then under Axis Options set Bin width or Number of bins. If you need to enter a specific list of bin limits, use Data > Data Analysis > Histogram and put those limits in the Bin Range box; the chart’s Format Axis pane does not accept a custom list.

What a histogram bin range means

A histogram groups numerical observations into intervals, called bins, and displays how many values fall into each interval. For example, a test-score histogram might group scores from 0 through 9, 10 through 19, and 20 through 29. The bin range can mean either the interval size used by a chart or a list of upper limits supplied to Excel’s Analysis ToolPak. Those are related ideas, but they are set in different places.

Excel’s built-in Histogram chart is the quickest option for a visual summary. It lets you choose a bin width or tell Excel how many bins to create, and it can collect unusually low or high values into underflow or overflow bins. It does not provide a box where you can type an arbitrary series of cutoffs. The ToolPak’s Histogram command does provide that box and creates a frequency table from the limits you supply.

Before changing anything, check that the source data are numeric measurements, such as ages, elapsed times, prices, or scores. A histogram is not the same as a bar chart of named categories. If the values are labels such as regions or product names, use a regular column or bar chart instead.

Set bins on a Histogram chart

  1. Select the numeric data, including a heading only if you intend to identify the first row as a label.
  2. On the ribbon, choose Insert > Insert Statistic Chart > Histogram.
  3. Click the horizontal axis labels in the chart. Right-click and select Format Axis to open the formatting pane.
  4. In Axis Options, under Bins, select Bin width or Number of bins and enter a value.
  5. If needed, select Underflow bin or Overflow bin and enter the threshold for grouping tail values.

The bin controls appear when the selected chart is a Histogram chart and the horizontal axis is selected. If the pane shows ordinary axis settings instead, confirm that you inserted a Histogram rather than a column chart, then select the axis labels rather than a data bar. To revise the setting later, return to the same pane and replace the value; Excel updates the chart.

On a Mac, the chart and axis settings are available in desktop Excel, although ribbon organization and pane wording can vary by release. Control-click the horizontal axis and choose Format Axis if a standard right-click is not available. Excel for the web has a more limited feature set than desktop Excel, so use the Windows or Mac desktop app if the needed bin controls or ToolPak command are missing.

Choose bin width or number of bins

Bin width is the span covered by each regular interval. If a set of times ranges from 0 to 60 seconds and you choose a width of 10, the chart groups the observations into intervals roughly 10 seconds wide. A narrower width reveals more detail but may leave many bins with few observations. A wider width makes the overall shape easier to see but can conceal clusters or gaps.

Number of bins is useful when you want to control how many groups the chart displays without calculating a width yourself. Excel derives the bin intervals from the data and the requested count. The resulting widths are not necessarily the cutoffs you would have chosen manually, so inspect the axis labels after entering a number. Changing the data later can also change how useful the selected count is.

  • Choose a wider bin when the chart looks noisy and your goal is to show a broad pattern.
  • Choose a narrower bin when meaningful detail is being hidden, while checking that the chart does not become mostly empty bars.
  • Use a fixed width when comparing several groups or time periods, so each chart uses comparable intervals.
  • Use a bin count when you want a quick overview and do not need particular cutoff values.

There is no universally best width. The right choice depends on the number of observations, the measurement scale, and the question being asked. Try a few plausible settings and compare whether the main features—such as a central cluster, a long tail, or two peaks—remain visible. If you are comparing multiple histograms, keep the limits consistent; letting each chart choose its own bins can make similar distributions look different.

Use exact cutoffs with the Analysis ToolPak

For explicit bin limits, use Excel’s Analysis ToolPak. On Windows, if Data Analysis is not visible on the Data tab, enable it through File > Options > Add-ins. At the bottom, select Excel Add-ins in the Manage box, choose Go, check Analysis ToolPak, and select OK. Then open Data > Data Analysis > Histogram.

In the Histogram dialog, enter the cells containing the observations in Input Range and the cells containing your upper limits in Bin Range. Choose an output location or a new worksheet, and select Chart Output if you want the ToolPak to create a chart as well as a frequency table. If the first cell of either range contains a heading, select Labels; otherwise leave it unchecked. The exact dialog options may vary slightly across desktop releases.

Arrange the bin limits in ascending order in one row or one column. For example, a Bin Range containing 10, 20, and 30 defines successive upper limits. The ToolPak counts observations less than or equal to 10 in the first bin, those greater than 10 and less than or equal to 20 in the second, and those greater than 20 and less than or equal to 30 in the third. Values above the final limit are reported in a final More row. This boundary convention matters: a value of exactly 20 belongs to the bin ending at 20, not the next one.

On a Mac, the Analysis ToolPak may need to be enabled through Tools > Excel Add-ins; availability and menu placement depend on the Excel release. After enabling it, look for Data Analysis on the Data tab. The ToolPak is a desktop add-in, so do not assume its Histogram dialog is available in Excel for the web.

Worked example: scores from 0 to 100

Suppose a class has exam scores from 0 to 100 and you want to report counts in bands ending at 59, 69, 79, 89, and 100. Put those five upper limits in ascending order in a worksheet column, then use that column as the ToolPak Bin Range. The resulting intervals are up to 59, above 59 through 69, above 69 through 79, above 79 through 89, and above 89 through 100, followed by More for any values above 100.

This setup illustrates why the exact boundaries should be chosen deliberately. If scores are whole numbers, the first band contains 59 and below; it does not mean 0 through 59 unless the data cannot include negative scores. If scores can contain decimals, values such as 59.5 fall in the next band. To label groups as 0–59, 60–69, and so on, confirm that the underlying data and intended boundary rules agree. The ToolPak uses upper limits; it does not interpret text labels as lower-and-upper ranges.

For the built-in Histogram chart, set a width of 10 to get regular intervals, then inspect the labels and endpoints. That may be suitable for a quick distribution, but it is not a substitute for the custom limits above if your intended bands have unequal widths, such as 0–59 followed by ten-point bands. In that case, use a frequency table based on the exact limits and chart that table with an appropriate chart type.

How to choose limits for your data

Start with the purpose of the chart, not a preferred-looking number of bars. For a service-time report, intervals of five minutes might be meaningful because staff use those increments. For heights recorded to a tenth of a unit, a width of 10 units would hide too much. Keep the units visible in axis titles or labels so that a reader can tell whether a bin represents one day, one dollar, or ten points.

Check the data range and precision before choosing a width. A dataset with values from 48 to 52 may not need bins beginning at zero unless the zero baseline has meaning. Conversely, if comparing two groups, use the same starting point and bin widths even when one group has a narrower range. Otherwise, the bar heights are harder to compare fairly.

Also consider outliers. One very large observation can make most values appear crowded into a small part of the chart if the bins span the full range. An overflow bin can group high values together, but it hides differences among those values. Keep the raw data available and document the threshold if that detail matters to readers.

Common problems and boundary mistakes

  • The bin options are missing: You may have selected a column chart, the vertical axis, or a data series instead of the horizontal axis of a Histogram chart. Re-select the chart’s horizontal axis and open Format Axis.
  • The ToolPak command is missing: Enable Analysis ToolPak in Excel Add-ins, then check the Data tab again. In Excel for the web, open the workbook in desktop Excel for the ToolPak workflow.
  • Unexpected counts appear: Check that the bin limits are numeric and sorted in ascending order, that the input range contains the intended observations, and that Labels is checked only when the selected range actually includes a heading.
  • A value seems assigned to the wrong interval: ToolPak limits are upper bounds. A value equal to a limit is counted in the bin ending at that limit. Review the interval convention before interpreting the output.
  • There are too many empty bins: Increase the width or reduce the number of bins. If the chart is still hard to read, confirm that the data have not been imported as text or mixed with irrelevant values.

Do not confuse a histogram’s bin settings with filtering the source data. Changing a bin width changes how Excel groups values for display; it does not remove observations from the worksheet. Likewise, choosing an overflow bin groups tail observations in the chart rather than changing their underlying values.

Histogram chart versus ToolPak output

Use the built-in Histogram chart when you want a chart that is quick to create and easy to revise by changing the axis settings. Its bin width, bin count, and tail-bin controls are convenient for exploring a distribution. Use the ToolPak when you need a reproducible frequency table with explicit upper limits, want the counts in worksheet cells, or need to share the exact cutoffs used.

The ToolPak’s frequency table is also useful when the counts must feed another calculation or report. Its generated chart is a starting point, but check the categories and axis labels rather than assuming the appearance communicates every boundary clearly. If bins have unequal widths, a standard histogram-style display can imply a misleading comparison: equal-looking bar widths do not represent equal-sized intervals. Consider presenting the interval limits and counts in a table, or use a chart designed to represent unequal interval widths accurately.

Neither method chooses the right statistical interpretation for you. A histogram describes the distribution of numeric values; it does not establish why the pattern exists, whether a difference is significant, or whether the sample represents a wider population. Use the chart as a descriptive view and retain the underlying values and bin definitions for context.

Frequently Asked Questions

Can I type a custom bin range into an Excel Histogram chart?
No. The built-in chart lets you set a bin width, number of bins, and underflow or overflow thresholds. To supply a list of exact upper limits, use Data > Data Analysis > Histogram and enter the cells in Bin Range.

Does Excel’s Bin Range mean the cell range with my data?
Not in the Analysis ToolPak dialog. Input Range is where you specify the observations. Bin Range is a separate row or column of numeric upper limits that tells Excel where one frequency group ends and the next begins.

Should I choose Bin width or Number of bins?
Choose Bin width when the interval size has a useful meaning or needs to match another chart. Choose Number of bins for a quick overview when the exact interval boundaries are not important, then inspect the axis labels.

What happens to values above the last ToolPak bin?
Excel reports them in a final More row in the frequency table. Add a higher upper limit if those values should be divided into additional groups, and check whether an extreme value is valid before changing the limits.

Why does a value equal to a bin limit go into that bin?
The ToolPak treats each listed number as the upper end of its bin. A value equal to 20 is counted in the bin ending at 20; the next bin includes values greater than 20 up to its own upper limit.

Can Excel use bins with different widths?
The built-in Histogram chart is intended for regular-width bins, with optional underflow and overflow groups. The ToolPak can count against a custom list of upper limits, but for unequal intervals, clearly label the ranges and take care that the chart does not imply equal widths.

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.