To change an Excel histogram’s bins in Microsoft 365 for Windows, click the chart’s horizontal axis labels, right-click and choose Format Axis, then use Axis Options to set Bin width or Number of bins. You can also set underflow and overflow bins there to group unusually low or high values.
What histogram bins control
A histogram groups numeric observations into intervals, called bins, and displays how many values fall into each interval. Changing the bins changes the chart’s grouping and appearance; it does not change the values in your worksheet. A dataset with a wide range may look smooth with many narrow bins but lumpy with a few broad ones, even though the underlying observations are identical.
Excel’s built-in Histogram chart is available from Insert > Insert Statistic Chart > Histogram. Its bin settings are chart-axis settings, so you can adjust them after creating the chart without rebuilding your source data. The labels on the horizontal axis represent bin boundaries or ranges, and the columns show the associated frequencies.
Change bin width or number of bins
- Select the histogram, then click a label on its horizontal axis. Make sure you select the axis labels, not a column or the vertical frequency axis.
- Right-click the axis and choose Format Axis. The Format Axis pane opens. If needed, select Axis Options in the pane.
- Under Axis Options, find the Bins controls. Select Bin width and enter a positive number to define the interval size, or select Number of bins and enter how many groups Excel should display.
- Click elsewhere in the worksheet to apply the setting, then check the chart labels and column heights. Reopen the pane to refine the value if the result is too detailed or too broad.
The exact layout of the Format Axis pane can vary slightly with Excel updates, but the bin controls are attached to the horizontal axis of a Histogram chart. If you cannot see them, confirm that the chart type is Histogram and that you selected the horizontal axis rather than a data series.
Choose between bin width and bin count
Use Bin width when the interval size has a useful meaning in the units of your data. For example, if a column contains delivery times in minutes, a width of 5 creates groups five minutes apart. This makes the grouping easier to explain and helps compare charts that use the same scale.
Use Number of bins when you have a target number of columns or want Excel to divide the data into a chosen number of groups. This can be convenient for an initial view, but the resulting interval width depends on the data range. If new values extend that range, the chart may no longer have the same practical groupings you intended.
- Choose a smaller width or more bins to reveal finer variation. Too many bins can leave many columns empty or make random fluctuations look important.
- Choose a larger width or fewer bins to emphasize the overall shape. Too few bins can conceal clusters, gaps, or skew.
- Use comparable settings when comparing separate groups or periods. Changing the width between charts can make differences look larger or smaller than they are.
There is no universally correct bin count. The choice depends on the number of observations, the measurement scale, and the question being asked. It is useful to try a couple of reasonable settings and check whether the main pattern persists, rather than selecting a setting solely because it produces a smooth-looking chart.
Example: grouping measurements by tens
Suppose a worksheet contains test scores from 42 to 91, and you want to see the distribution in roughly ten-point intervals. Select the horizontal axis, open Format Axis, and set Bin width to 10. The chart will group the scores into intervals of that width. The frequency column for each interval tells you how many scores it contains; it does not show the average score.
For a simple check on the result, count the source values in a few intervals and compare those counts with the column heights. If you are explaining the chart to someone else, state the interval size and the units, such as ten score points or ten minutes. Do not assume that two charts with ten bins necessarily use the same interval boundaries: different data ranges can produce different widths when you choose a bin count instead of a width.
Also distinguish the interval width from the number of observations. A bin width of 10 means each interval spans ten units, not that each column contains ten records. Some intervals may contain many values and others only a few.
Use underflow and overflow bins
The Underflow bin and Overflow bin settings are useful when you want to group values beyond a threshold rather than display many extreme intervals. Enter a threshold for an underflow bin to collect values below it, or set an overflow threshold to collect values above it. Excel labels these groups accordingly, allowing the main part of the distribution to remain readable.
For instance, if most response times fall between 0 and 60 seconds but a few are much longer, an overflow bin can group the high values beyond a chosen cutoff. This makes the central distribution easier to inspect while preserving the fact that long responses occurred. It does not remove those records from the chart; it places them together in an end group.
Use thresholds that make sense for the measurement, and explain them in a chart title, axis title, or note. Grouping extremes can hide how far apart those values are from one another, so keep the original data available and avoid using an end bin when the distance between extreme observations is itself important.
Histogram chart versus Analysis ToolPak output
Excel has more than one way to produce a histogram, and the editing process depends on which one you used. An inserted Histogram chart has editable bin settings in the Format Axis pane. In contrast, the Analysis ToolPak’s Histogram procedure produces a frequency table based on a supplied Bin Range, and it can also create a chart from that output.
To adjust a ToolPak result, change the bin limits in the worksheet’s Bin Range and run the analysis again using Data > Data Analysis > Histogram. The procedure creates output from the selected input and bin range; editing a chart axis alone does not rewrite the frequency table to use new limits. If Data Analysis is missing, the Analysis ToolPak add-in may not be enabled. In Microsoft 365 for Windows, enable it through File > Options > Add-ins, select Excel Add-ins in the Manage box, choose Go, and check Analysis ToolPak.
For a ToolPak histogram, the bin range contains the upper limits for the groups. For example, limits of 10, 20, and 30 define successive cutoffs, with a final category for values above the last limit. Plan the limits before running the procedure, and include a suitable final limit if you do not want larger values collected in an above-range category.
Common problems and fixes
- There are no bin controls: Verify that the chart is a built-in Histogram chart, not a regular column chart that merely looks similar. Select its horizontal axis labels and reopen Format Axis.
- The chart has one giant group or too many thin columns: Check whether you entered a bin width or a number of bins, then adjust the setting to suit the data’s scale and range.
- Some records seem missing: Inspect the source column for text, blank cells, errors, or values stored as text. A histogram is intended for numeric observations; clean or convert entries that should be numbers, then verify the chart’s data range.
- The axis looks wrong for dates: Excel stores dates as serial numbers, so a numeric bin width may not correspond to the calendar grouping you had in mind. For reporting by month or year, consider adding a helper column that explicitly assigns each date to the desired period and charting those categories with a column chart.
- The chart changes when new data is added: Check whether the chart’s source range includes the new rows. A table can expand with added rows, but a chart built from a fixed range may need its source data updated. Review the axis bin settings again if the data range changes substantially.
Make the bins easier to interpret
Give the chart a descriptive title and label the horizontal axis with the measured quantity and units. Label the vertical axis with a term such as Frequency or Count, depending on what the chart displays. A histogram’s bars usually touch because the intervals represent continuous ranges; gaps between bars can suggest separate categories rather than a continuous measurement.
When comparing distributions, use the same bin boundaries and axis scales where possible. This matters especially when comparing a small sample with a much larger one: raw frequency counts will naturally tend to be higher for the larger sample. If the aim is to compare proportions rather than counts, consider whether a normalized display or a separate calculation is more appropriate; do not assume that changing the bin width automatically converts counts into percentages.
Frequently Asked Questions
How do I change the bin size in an Excel histogram?
Select the horizontal axis labels, right-click, choose Format Axis, and set Bin width under Axis Options. Enter a positive value in the same units as the source data.
How do I set the number of bins in Excel?
Open Format Axis for the histogram’s horizontal axis and choose Number of bins under Bins. Enter the number of groups you want; Excel adjusts the intervals to fit the data range.
Why can’t I see the Bins option?
The Bins controls appear for a Histogram chart when its horizontal axis is selected. If the chart is a standard column chart, or you have selected a column or the vertical axis, those controls will not appear.
Can I change histogram bins in Excel for Mac or Excel for the web?
Excel for Mac also provides histogram axis formatting, though pane labels and placement may differ by release. Excel for the web has more limited chart-editing options than the Windows desktop app; if the bin controls are unavailable in your browser, open the workbook in desktop Excel to change them.
Does changing bins change my data?
No. It changes how Excel groups and displays the observations. The worksheet values remain unchanged, although a ToolPak frequency table must be generated again if you change its bin limits.
Related
- Excel Hide Columns Based on Date: Methods That Work
- Excel Hide Columns From Certain Users: What Actually Works
- Excel Hide Column Letters? What the Setting Actually Does
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Histogram: How to Set the Number of Bins ExactlyIn Excel for Microsoft 365 on Windows, select the histogram’s horizontal axis, open Format Axis, and under Axis…
- Step Progress Chart in Excel: Build a Clear TrackerA step progress chart is a visual way to show how far a project, process, or checklist has…
- Excel Pivot Table Unique Count: How to Get ItYes—Excel can count unique values in a PivotTable. In Excel for Microsoft 365 on Windows, create the PivotTable…
- Excel Pivot Table SUMPRODUCT: When and How to Use ItYou generally cannot enter SUMPRODUCT directly as a PivotTable value calculation: a standard PivotTable summarizes source fields, while…
- Excel Pivot Table Unique Values: Count or List ThemTo list unique values in Excel, put the field in a PivotTable’s Rows area; repeated labels appear once.…
- Excel Pivot Chart Pie Chart: Multiple Columns ExplainedYes, you can make a pie PivotChart in Excel, but a pie chart can show only one data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.