Change Bin Size in an Excel Histogram

To change a histogram’s bin size in Excel for Microsoft 365 on Windows, select the chart’s horizontal axis, open Format Axis, and under Axis Options choose Bin width or Number of bins. Enter the value you want; Excel redraws the histogram using the new grouping.

What bin size means in a histogram

A histogram groups numerical observations into ranges called bins, then shows how many observations fall in each range. If you chart test scores from 0 to 100 using bins 10 points wide, for example, the bars represent score ranges roughly 10 points apart. A bar’s height is the count of scores in its range, not the score itself.

Bin size is the width of each range. It is different from the number of bins: a smaller width usually produces more, narrower bars, while a larger width usually produces fewer, wider bars. The choice affects what patterns are visible. Narrow bins can reveal clusters or gaps but make random variation look important; wide bins smooth the display but can hide meaningful differences.

Excel’s built-in Histogram chart is intended for numeric data. It is not the same as an ordinary column chart with categories: a histogram places numeric values into continuous ranges, and neighboring bars generally touch to show that the ranges are connected.

Change the bin size in Excel for Windows

Click the histogram chart, then click the horizontal axis labels along its bottom edge. Make sure you have selected the axis rather than a bar or the chart background.
Right-click the selected axis and choose Format Axis. The Format Axis pane opens. If the right-click menu is awkward, select the axis and use the chart-formatting controls to open the same pane.
In the pane, open Axis Options, represented by a chart-like icon. Find the Bins settings.
Choose Bin width and enter a positive number to set the width of each interval, or choose Number of bins and enter how many intervals Excel should use.
Click elsewhere in the workbook to apply the change, then check the axis labels and bar heights. Adjust the setting if the chart is too detailed or too compressed.

For example, if a set of measurements is recorded in whole units and you enter a bin width of 5, Excel groups values into intervals five units wide. The exact first and last displayed ranges depend on the data and the chart’s boundary settings, so inspect the labels rather than assuming that the first interval starts at zero.

In current Excel for Microsoft 365 on Mac, select the horizontal axis and open Format Axis from its context menu; the bin controls are in the formatting pane, although the pane layout and context-menu wording can differ slightly. If Excel for the web does not show the controls you need, open the workbook in the desktop app to change the chart settings.

Choose bin width or number of bins

Use Bin width when the interval itself has a real interpretation. Examples include grouping delivery times into 5-minute intervals, temperatures into 2-degree intervals, or ages into 10-year intervals. Keeping the width fixed also makes it easier to compare two histograms, provided the data use the same scale and boundaries.

Choose Number of bins when you want to control how many bars appear and do not need a particular interval width. Excel calculates the resulting widths from the data range. This can be convenient for an initial view, but the width may change when the data range changes, making comparisons less consistent.

Use a smaller bin width to examine detail, especially when the data set is large enough to support it.
Use a larger bin width to summarize a small data set or reduce a jagged display.
Keep the settings consistent when comparing groups or reporting results over time.

There is no single best setting for every data set. A useful approach is to try a few reasonable widths, then ask whether the chart makes the distribution easier to understand without concealing important variation. For a formal analysis, record the chosen width and boundaries so another person can reproduce the grouping.

How to choose useful boundaries

Start with the question the chart is meant to answer. If a manager needs to see how many orders take less than an hour, intervals aligned to meaningful time thresholds may be more useful than automatically chosen ranges. If you are exploring a distribution without known thresholds, try a few widths and look for a stable overall pattern rather than choosing the version that makes the data look most dramatic.

Consider the number of observations and the spread of the values. With very few observations, many narrow bins can leave most bars empty. With a large data set, a handful of wide bins may conceal multiple peaks or a long tail. A common starting point in statistics is to choose a bin count related to the square root of the number of observations, but this is only a rough exploration aid, not a rule that guarantees a good chart. The shape and purpose of the data matter too.

Also check whether the values are rounded, bounded, or naturally grouped. For exam scores reported as whole numbers, a width of 10 may communicate the results more clearly than a width of 7.3. For continuous measurements, a fractional width might be sensible if the measurement scale and precision support it. Avoid implying precision that the source data do not have.

Understand bin edges and special bins

Excel assigns observations according to interval boundaries. In the usual histogram grouping, an interval’s upper boundary is included in that bin, while the next interval covers values above the previous boundary through its own upper boundary. That convention matters when observations equal an edge. Read the displayed labels and verify a boundary case if a count has operational or reporting significance.

The axis settings can also include Underflow bin and Overflow bin. An underflow setting groups values at or below a specified threshold into one end bin; an overflow setting groups values above its threshold into the other end bin. These options can be useful when the extremes are less important than the main distribution. For example, a service-time chart could group all unusually long waits together rather than spreading a few extreme values across several nearly empty bars.

These settings do not remove outliers from the source data. They change how the chart groups them. If an extreme value is a data-entry error, correct the source only after verifying it; do not hide it simply because it makes the chart less attractive. If the value is genuine, explain how it is represented.

Common problems and how to fix them

The Bin width control is missing. Confirm that the chart is a Histogram chart and that you selected its horizontal axis. An ordinary column chart has different axis options. If needed, select the chart and choose Chart Design > Change Chart Type, then choose Histogram under the statistical chart types.
The bars look too crowded or too sparse. Change the width or bin count, then check whether the result communicates the distribution. A useful chart does not need a fixed number of bars; it needs ranges that suit the data and the reader’s question.
The chart ignores some values or behaves unexpectedly. Check the source range for text mixed into the numeric data, numbers stored as text, blank cells, and errors. Convert intended observations to genuine numbers and review the chart’s selected data range.
The first or last bin seems surprising. Excel determines the chart’s ranges from the data and the chosen settings. Check the axis labels and any underflow or overflow thresholds. Do not assume the bins begin at zero unless the displayed boundaries show that they do.
A few large values flatten the main bars. Confirm whether those values are valid. You can use an overflow bin to group the upper tail, or make a second chart for a focused view, but state clearly when the display groups or omits detail.

If the values are dates, remember that the chart groups numeric date values, while the labels may be less intuitive than calendar categories. For a report that needs named calendar periods such as months, a summary table grouped by month and a column chart may be clearer than a generic histogram.

Histogram chart versus the Analysis ToolPak

The built-in Histogram chart is usually the simplest option when you want a chart linked to a range of raw observations and want to adjust bins interactively. Select the data, then choose Insert > Insert Statistic Chart > Histogram. After insertion, change the bin settings through the horizontal axis as described above.

Excel also provides a Histogram command in the Analysis ToolPak. On Windows, if Data Analysis is not on the Data tab, enable the add-in through File > Options > Add-ins; at the bottom, choose Excel Add-ins beside Manage, click Go, select Analysis ToolPak, and click OK. Then choose Data > Data Analysis > Histogram. You can supply an input range and a separate range of bin limits, and request a chart in the output.

The ToolPak is useful when you need explicit bin limits or a frequency table as well as a chart. Its supplied bin values act as upper limits for the groups. Unlike an interactive chart axis setting, changing those limits means editing the bin range and running the analysis again; the output is a generated result, not a live chart control. The built-in chart is generally more convenient for quick visual exploration, while the ToolPak offers a more table-oriented workflow.

When a frequency table is a better alternative

If you need exact, named categories or precise control over every boundary, build a frequency summary and chart that summary as a column chart. For instance, a report might need the fixed labels 0–9, 10–19, and 20–29, or bands that follow business rules. A prepared table makes those categories explicit and can be easier to audit than an automatically scaled axis.

This approach is also useful when comparing several groups. Create the same set of ranges for each group, count the observations in each range, and compare the resulting series or charts. Do not let each histogram choose different boundaries if the goal is to compare bar heights; different bins can make similar data appear different. A histogram is excellent for exploring one numeric distribution, but a fixed frequency table is often clearer for a repeatable report.

Frequently Asked Questions

How do I change the bin size in Excel?
Select the histogram’s horizontal axis, open Format Axis, and under Axis Options > Bins set either Bin width or Number of bins.

What is the difference between bin width and number of bins?
Bin width sets the size of each interval; number of bins sets how many intervals Excel uses. A fixed width is helpful for meaningful or consistent ranges, while a fixed count is convenient for controlling chart complexity.

Why can’t I see the bin settings?
You may have selected the chart area, a bar, or the wrong axis, or the chart may be a regular column chart rather than a Histogram chart. Select the horizontal axis of a Histogram chart and open its Format Axis pane.

Can I make the first histogram bin start at zero?
Changing the width does not necessarily make the first boundary zero. Check the displayed ranges and use underflow settings or an explicit frequency table with chosen boundaries if the report requires a zero-based start.

Can I use different bin sizes in one Excel histogram?
The built-in histogram axis uses a consistent bin width for its regular intervals, with optional underflow and overflow groups. For irregular ranges, create a frequency table using the exact boundaries you need and chart that table instead.

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.