Excel Histogram Groups: Set and Change Bin Sizes

Excel histogram groups numerical values into bins—ranges such as 0–10, 10–20, and 20–30—and counts how many observations fall in each range. In Microsoft 365 for Windows, select your numeric data, choose Insert > Insert Statistic Chart > Histogram, then right-click the horizontal axis and choose Format Axis to change the bin width, number of bins, or end bins.

What histogram groups show

A histogram is a chart of the distribution of numerical data. Its horizontal axis divides a scale into adjoining intervals, called bins or groups; its vertical axis shows the number of observations in each interval. A tall bar means many values fall in that range, while a short or missing bar means few or none do. The bars touch because the intervals represent a continuous numeric scale, unlike the separated categories in a typical column chart.

For example, suppose a class records exam scores from 0 to 100. Groups of 10 points could show the counts for scores up to 10, above 10 through 20, and so on. A histogram can reveal a cluster around the middle, a long tail of unusually high or low results, or more than one concentration. It is useful for measurements such as delivery times, ages, temperatures, test scores, and product dimensions. It is not usually the right chart for names, departments, or other unrelated labels.

Excel’s built-in Histogram chart makes the groups for you. You can accept its automatic grouping or set a width or count, then optionally define an underflow or overflow group for values at either end.

Create a histogram in Microsoft 365

Prepare one column of numeric observations. A descriptive heading is fine, but avoid mixing text notes into the values. Select the heading and data, or just the numeric cells, then follow these steps in Excel for Windows:

Choose Insert > Insert Statistic Chart > Histogram.
Select the horizontal axis—the row of bin labels—and right-click it. Choose Format Axis.
In the Format Axis pane, open Axis Options. Under Bins, choose the grouping option you need.
Use the chart title and axis titles to explain the measure and units, then check that the labels are readable.

If you do not see the Histogram option, confirm that Excel has a numeric data range selected and that you are using a version with the statistical chart type. The Insert Statistic Chart menu may be displayed slightly differently in other Excel editions. In Excel for Mac, look on the Insert tab for the statistical chart options; after creating the chart, select its horizontal axis and open Format Axis. The ribbon and pane arrangement can differ, but the bin controls serve the same purpose.

Choose bin width or number of bins

In Format Axis, Excel lets you control the groups in two main ways. Bin width sets the numerical span of each regular bin. Number of bins asks Excel to divide the data into a specified count of bins. These are alternatives: setting one determines the grouping rather than independently applying both. With Automatic, Excel selects a grouping based on the data and chart settings.

Use bin width when the scale has a meaningful unit. For response times measured in seconds, a width of 5 makes groups five seconds wide. For scores, a width of 10 gives familiar ten-point intervals.
Use number of bins when you want a chosen level of detail. A smaller count gives a broad overview; a larger count shows more local variation.
Start with a sensible range and inspect the result. Too few bins can hide patterns. Too many can make ordinary fluctuations look like separate features.

For example, if delivery times run from about 8 to 58 minutes, a width of 10 produces a compact view with roughly five or six ordinary intervals. A width of 2 could show finer differences, but may create many bars with small counts. The best setting depends on the question: a manager comparing broad service bands may want ten-minute groups, while an analyst investigating short delays may need narrower intervals.

Bin width is expressed in the same units as the values. If a column stores time as Excel time serials rather than plain numbers of minutes, a width of 10 means ten serial units, not ten minutes. Check how the values are stored and displayed before choosing a width.

Set underflow and overflow groups

The Underflow bin and Overflow bin controls collect values at the ends of the scale. An underflow threshold groups values less than or equal to the threshold into a single end bin; an overflow threshold groups values greater than the threshold. These controls are useful when the tails are less important than the main distribution or when you want to emphasize a limit.

Suppose a service target is 30 minutes. An overflow group at 30 can make the chart show how many deliveries took longer than that threshold in one bar. That is a presentation choice, not a change to the underlying records: the individual values remain in the worksheet. Be clear in the chart title or labels about the threshold, because a single end bar hides how far its observations extend beyond the limit.

Regular bins are intervals, and their displayed labels describe boundaries rather than separate categories. Excel’s histogram groups values by their numeric positions; values on a boundary are assigned according to the interval rules used by the chart. If the exact treatment of a boundary matters to a report, verify the result with a small known dataset or use a frequency table with explicitly defined limits.

How to choose useful groups

There is no universally best bin width. A useful grouping balances detail against readability and should reflect both the data and the decision the chart supports. Before settling on settings, consider:

Measurement precision: Grouping values more finely than they are meaningfully measured can imply precision the data do not have.
Sample size: A small dataset may produce many empty or one-observation bins if the groups are too narrow.
Expected comparisons: If readers will compare charts, use the same bin boundaries and axis scale rather than letting each chart choose unrelated groups.
Important thresholds: Align a boundary with a meaningful cutoff when possible, and state the cutoff plainly.
Shape and outliers: A few extreme observations can stretch the horizontal axis and compress the main cluster. An end bin can help, but investigate unusual values rather than hiding them.

A practical approach is to begin with Automatic, then compare a few reasonable widths or bin counts. Ask whether the chart makes the central pattern visible without disguising important variation. Do not select a setting just because it produces a smooth-looking shape: the grouping can change the apparent story even though the observations have not changed.

Prepare and update the source data

A standard histogram chart needs numerical observations, not a prebuilt frequency table. Put each observation in its own cell in one column. Remove accidental text entries, check for numbers stored as text, and decide whether zeros are valid observations. Blank cells do not represent zero; they are missing entries. If a blank actually means a measured zero, enter zero deliberately rather than relying on Excel to infer it.

Keep units consistent. A column that mixes seconds and minutes, or pounds and kilograms, will produce groups that do not have a useful interpretation. Similarly, check dates and times: Excel stores dates and times as numeric values, so a chart may group them as serial numbers unless the data and axis are configured for the intended interpretation.

If you add records later, make sure the chart’s source range includes them. Formatting the source as an Excel Table before building the chart can make a growing dataset easier to manage; table-based charts generally extend as table rows are added. Still, inspect the chart after appending data, especially if the new values fall outside the original range. In Excel for the web, chart editing and formatting controls can be more limited than in the Windows desktop app, so use desktop Excel for detailed bin adjustments if a control is unavailable.

Built-in chart versus Analysis ToolPak

The Insert Statistic Chart histogram is usually the simplest choice when you want a visual chart whose bins you can adjust from the axis settings. Excel also has a separate Histogram command in the Analysis ToolPak. That tool produces a frequency table from input data and optional bin limits, and it can create a chart as part of its output. Its result is an analysis output rather than the same interactive chart workflow.

To use the ToolPak on Windows, first enable it if needed: choose File > Options > Add-ins, set Manage to Excel Add-ins, select Go, and check Analysis ToolPak. Then choose Data > Data Analysis > Histogram. Specify the input range and, if you have one, a bin range. Bin-range entries are upper limits for the groups; for example, limits of 10, 20, and 30 define successive cutoffs. Choose an output location and, if desired, the chart output option.

The ToolPak is useful when you need a frequency table for further calculations or want to define explicit cutoffs. The built-in chart is often more convenient for visual exploration and formatting. ToolPak output does not automatically recalculate as a fresh analysis when source values change; run the analysis again to refresh the results. On Mac, the add-in is managed through Tools > Excel Add-ins when available, and menu availability can depend on the Excel edition.

Common histogram grouping problems

The chart has one bar or an unexpected number of bars: Open Format Axis and inspect whether Automatic, bin width, or number of bins is selected. Also check for a narrow data range or many repeated values.
Some values seem missing: Check whether they are text rather than numbers, outside an input range, blank, or excluded by an underflow or overflow setting. Confirm that the chart source includes the whole column of observations.
The bars look misleadingly uneven: Confirm that the data use consistent units and that the bin width is appropriate. A large outlier can stretch the scale and make the central bars appear compressed.
Labels look like category names: A histogram’s horizontal labels represent numeric intervals. If the data are categories such as product names, use a column chart of category counts instead.
The chart does not show the groups expected from a frequency table: Check the boundary values and how the chart labels its intervals. For exact, auditable cutoffs, the ToolPak or a deliberately constructed frequency table may be a better fit.

A related misconception is that changing a bin setting changes or filters the source data. It does not. Binning changes how the chart summarizes the observations; it does not remove, round, or edit the values in the worksheet.

Frequently Asked Questions

How do I change the histogram group size in Excel?
Select the horizontal axis, choose Format Axis, and under Axis Options > Bins set Bin width. Enter a width in the units of your data. If you want a fixed count of groups instead, use Number of bins.

Can I set exact group boundaries?
The built-in chart offers bin width, a number of bins, and end-bin thresholds, rather than a separate custom list of every boundary. For explicit cutoffs, use the Analysis ToolPak Histogram command with a bin range, or build a frequency table from defined limits.

Does Excel include the upper limit in a histogram bin?
Excel’s histogram uses interval boundaries to assign values, and the chart labels can make a boundary look ambiguous. If inclusion of a specific cutoff matters, test boundary values directly or use a frequency table with rules you define and document.

Should I use a histogram or a bar chart?
Use a histogram for numerical measurements grouped into intervals, such as weights or times. Use a bar or column chart for distinct categories, such as departments or product types. Histogram bars touch because their intervals form a continuous scale.

Why does my histogram look different after changing the number of bins?
Changing the bin count changes the interval widths and therefore which observations contribute to each bar. The underlying data stay the same, but the distribution can look smoother or more jagged depending on the grouping.

Can I make a histogram from a frequency table?
The built-in Histogram chart expects raw numerical observations rather than a list of categories paired with counts. For an existing frequency table, use a column chart if appropriate, or recreate the analysis from the underlying observations with the Histogram chart or ToolPak.

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.