Excel Histogram: Change the Number of Bins or Bin Width

To change an Excel histogram’s bin count in Microsoft 365 for Windows, click the chart’s horizontal axis, right-click and choose Format Axis, then under Axis Options > Bins, select Number of bins and enter a value. To control the size of each interval instead, choose Bin width and enter a value in the same pane.

What a histogram bin means

A histogram groups numerical observations into ranges called bins and displays how many observations fall in each range. If you chart test scores, for example, bins might group scores from 0 to 10, 10 to 20, and so on. The height of each bar shows the count in its range. Changing the bin settings changes how the data is grouped; it does not change the source values.

Bin count and bin width are related but answer different questions. The count is how many ranges Excel should use. The width is how large each range should be in the units of your data. If scores span 0 to 100, a width of 10 produces intervals about ten points wide. If you instead ask for 10 bins, Excel divides the observed range into ten groups, so the width depends on the actual minimum and maximum. Data running from 12 to 98 would not necessarily produce ten-point bins.

Change the number of bins in an Excel chart

Select the histogram, then click the horizontal axis labels. Make sure the axis—not a bar or the chart area—is selected.
Right-click the selected axis and choose Format Axis. Excel opens the Format Axis pane, usually at the right of the window.
In Axis Options, find the Bins section. Select Number of bins.
Enter the desired count in the box that appears, then press Enter. Excel redraws the histogram using that many bins.

If you cannot see the Bins controls, check that you selected the horizontal value axis of a Histogram chart. These controls are not the same as the category-axis settings for an ordinary column chart. If the pane is narrow, widen it or scroll within it to find the options. You can also open the pane by selecting the axis and choosing Format Selection on the chart-formatting ribbon, when that command is available.

To return to automatic grouping, choose Automatic in the Bins section if it is shown. Excel then chooses a bin arrangement based on the data. The exact automatic result can vary when the source values or range change, so set a count or width explicitly when the same intervals must be maintained for comparisons.

Set a fixed bin width instead

Choose Bin width rather than Number of bins when the interval size matters more than the total number of bars. For measurements recorded in whole dollars, for instance, a width of 25 can make each bar represent a $25 range. For elapsed times recorded in minutes, a width of 5 gives ranges five minutes wide. Use the same unit as the source data: entering 5 does not mean five minutes unless the values themselves are stored in minutes.

Excel may create a different number of bins when you change the data, because it needs enough intervals to cover the observed values. A fixed width is useful when comparing different samples, but check that the data ranges are comparable and that the first and last groups make sense. If the range does not divide evenly into the selected width, the chart may include a partial interval at an end.

Use overflow and underflow bins

The Bins options can also include an Overflow bin and an Underflow bin. These settings gather extreme values into boundary groups. An overflow bin puts values above a specified cutoff into one final bin; an underflow bin puts values at or below a specified cutoff into one first bin. For example, on a chart of response times, you could group all unusually long times above a chosen threshold together rather than spreading them across several narrow bins.

These options can make the main distribution easier to read, but they hide detail among the values gathered at the boundary. Use them only when that grouping is meaningful, and make the threshold clear in the chart title, axis label, or a note. They are not a way to delete outliers: the observations remain represented in the histogram, just collected into a single bin.

Create the histogram before changing its bins

The bin controls described above apply to Excel’s built-in Histogram chart. To create one in Microsoft 365 for Windows, place the numeric observations in a column or row, select the data, and choose Insert > Insert Statistic Chart > Histogram. If the selected range includes a heading, Excel can use it as the series name. Then select the horizontal axis and change the bin settings in Format Axis.

For instance, suppose a column contains 100 delivery times in minutes. A histogram with 10 bins gives a quick overview using ten ranges across the observed data. If the operational question is instead how many deliveries took between 0 and 5 minutes, 5 and 10 minutes, and so forth, set the bin width to 5. The second choice makes the interval boundaries easier to interpret, even if the finished chart has more or fewer than ten bars.

On Excel for Mac, select the histogram’s horizontal axis, Control-click or right-click it, and choose Format Axis; look for the same bin controls in the formatting pane. Excel for the web may offer fewer chart-formatting options than the desktop app. If the bin count or width setting is missing, open the workbook in the desktop version of Excel rather than trying to change the chart through unrelated axis or series controls.

Choose bins that answer the question

There is no universally correct bin count. Too few bins can flatten meaningful differences into a few broad bars. Too many can make random variation look like a pattern and leave many bars with only a small number of observations. The useful setting depends on the sample size, spread, measurement precision, and the question the chart is meant to answer.

Start with the purpose. Choose a width that corresponds to a meaningful unit or decision threshold when one exists, such as five minutes or ten points.
Check the sample size. A small dataset usually cannot support a detailed-looking distribution. Avoid implying precision that the number of observations cannot provide.
Compare alternatives. Try a few reasonable counts or widths and see whether the overall shape is stable. A conclusion that appears only under one unusually narrow setting deserves caution.
Label the chart. State the measured quantity and units, and clarify important cutoffs. Readers should not have to infer whether a bin represents dollars, days, or another unit.

For a simple example, imagine 40 exam scores between 58 and 99. Four bins may show broad performance groups clearly, while 20 bins may produce many bars containing only one or two scores. Neither setting is automatically wrong: four bins may suit a quick summary, while a more detailed analysis may call for finer intervals. The important point is to inspect the counts and explain the grouping rather than treating Excel’s default as a statistical rule.

Common problems and how to fix them

The Bins section is missing: Verify that the chart type is Histogram and that the horizontal axis is selected. A regular column chart looks similar but does not have the same histogram-specific bin controls. If the chart came from another program or was pasted as an image, it may not be an editable Excel Histogram chart.

The chart has unexpected bars: Inspect the source range for text, blanks, errors, or values stored in an unexpected format. A histogram needs numerical observations. Text labels mixed into the values can prevent Excel from interpreting the input as intended, while dates and times are stored as numbers and may be grouped according to their underlying numeric values. Check that the selected range includes the observations you mean to chart and not unrelated cells.

The axis labels do not look like the intervals you expected: Check whether you selected Number of bins or Bin width, and confirm that the width uses the same units as the data. Also check for enabled overflow or underflow bins, which intentionally combine extreme values. The visible axis labels describe ranges, not necessarily individual data points.

A changed bin setting does not update another chart: Each chart has its own formatting. Changing one histogram does not automatically change the bins of a second chart, even if both use similar data. For a fair visual comparison, set matching widths or counts on each chart and check the axis ranges as well.

Built-in Histogram chart versus the Analysis ToolPak

Excel also has a Histogram command in the Analysis ToolPak. It is separate from the built-in chart command. The ToolPak workflow uses an input range and an optional range of bin boundaries, and can produce a frequency table and chart. It is useful when you need the counts in worksheet cells as well as a chart, or when you want to specify particular bin limits.

To use it, first enable the Analysis ToolPak if needed through File > Options > Add-ins, select Excel Add-ins in the Manage box, choose Go, and check Analysis ToolPak. Then use Data > Data Analysis > Histogram. Provide the input range and, if desired, a bin range, then choose an output location. The ToolPak’s bin-range workflow is not the same as changing the Number of bins field on a built-in Histogram chart; to revise a ToolPak analysis, change the chosen boundaries and run the analysis again. Menu availability can differ on Mac, and the ToolPak is not available in Excel for the web.

Frequently Asked Questions

Can I enter an exact number of bins in Excel?
Yes. Select the histogram’s horizontal axis, open Format Axis, and enter a value under Axis Options > Bins > Number of bins. This applies to Excel’s built-in Histogram chart.

Is bin width the same as number of bins?
No. Bin width sets the size of each interval, while number of bins sets how many intervals Excel uses. Choosing a width can result in a different bar count as the data range changes.

Why can’t I see the bin options?
You may have selected a bar or chart area instead of the horizontal axis, or the chart may be a regular column chart rather than a Histogram chart. Select the axis labels and reopen Format Axis.

Can I make a histogram with custom bin boundaries?
Yes. One option is the Analysis ToolPak’s Histogram command, where you supply a bin range. The built-in Histogram chart instead offers controls such as bin count, width, and boundary bins; it is not the same custom-boundary table workflow.

Will changing the number of bins change my data?
No. It changes how Excel groups and displays the observations. The original values remain in their cells, and the bar heights are recalculated from those values.

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.