There is no single correct number of bins for an Excel histogram: let Excel choose a starting point, then adjust the count or bin width until the chart shows the data’s shape without becoming crowded or overly simplified. As a practical first try, use roughly 5 to 20 bins for a typical dataset, then check whether important clusters, gaps, and outliers remain visible.
Why the number of bins matters
A histogram groups numeric observations into intervals, called bins, and shows how many values fall in each interval. A chart of exam scores, for example, might use intervals of 50–59, 60–69, and 70–79. The height of each bar represents the count of scores in that range. Unlike a regular column chart, the bars in a histogram usually touch because the intervals represent adjacent stretches of a number line.
The bin count affects the story the chart tells. Too few bins can flatten meaningful differences: a group of lower values and a group of higher values may appear as one broad mound. Too many bins can make ordinary variation look like a collection of sharp peaks and gaps. There is no universally best count because the useful level of detail depends on the sample size, the spread and shape of the values, and the question you want the chart to answer.
How Excel chooses bins
In Excel for Microsoft 365 on Windows, select a column of numeric observations and choose Insert > Insert Statistic Chart > Histogram. Excel creates a histogram and initially chooses bins automatically. This automatic setting is convenient for a first look, but it is not a guarantee that the selected grouping is best for your analysis. Review the chart before interpreting it.
To change the grouping, right-click the horizontal axis and choose Format Axis. In the Format Axis pane, under Axis Options, find the Bins controls. Depending on the chart and Excel version, you can set a Number of bins or a Bin width. You can also set an Overflow bin or Underflow bin to group values above or below a chosen cutoff. These settings change how observations are grouped; they do not change the source data.
Number of bins and bin width are two ways of controlling the same basic choice, but they answer different questions. Choose a bin count when you want a particular number of bars. Choose a width when the interval size has a meaningful interpretation, such as five dollars, ten minutes, or two degrees. The actual number of bars produced from a chosen width depends on the data range and where the intervals fall.
How to choose a sensible starting point
Start with the purpose of the chart, not a rule in isolation. If readers need to compare performance in familiar ranges, intervals such as five-point score bands may be easier to understand than an arbitrary number of bins. If you are exploring a distribution, try a moderate count and inspect whether the broad shape is stable when you make the bins a little narrower or wider.
- Consider the sample size. A small dataset cannot support many informative bins: most bars may contain only one or two observations. A larger dataset can often show finer detail without producing a mostly empty chart.
- Look at the data’s range. The same bin width can create very different charts for tightly grouped values and widely spread values.
- Check for skew and outliers. A long tail or a few extreme values can stretch the horizontal axis and compress the main cluster into a small part of the chart.
- Keep the audience in mind. Rounded, meaningful intervals are often easier to explain than a technically precise but awkward width.
- Compare nearby settings. If the main conclusion changes when you move from, say, 10 bins to 11, explain that the pattern is sensitive to the grouping rather than treating one setting as definitive.
A familiar statistical starting point is Sturges’ rule, which takes the base-2 logarithm of the number of observations, adds one, and rounds up. For 200 values, that gives about nine bins. The rule is simple, but it can be too coarse for large datasets or data with complicated shapes. Another approach, the Freedman–Diaconis rule, sets width using the interquartile range and the cube root of the sample size. It adapts to the spread of the central values and is less driven by extreme observations, but Excel’s standard histogram chart does not ask you to select that rule by name. Treat both as starting points, not commands.
Worked examples: counts and widths
Suppose a class has 40 test scores between 52 and 98. A seven-bin chart is a reasonable initial view for a small sample, while ten bins may reveal more detail if many scores are clustered together. If the goal is to report results in familiar ten-point bands, use score intervals such as 50–59 and 60–69 instead. Those purposeful bands may be more useful than an automatically selected count, provided you handle the endpoints consistently.
Now consider 1,000 package delivery times. A chart with only five bins may conceal a short-delay group and a separate long-delay group. Starting with around 10 to 20 bins can make those features easier to see. If a 10-minute interval is useful to operations staff, choose a bin width of 10 rather than trying to force a particular bar count. Then check whether the last interval or any unusually long delays make the scale hard to read.
These examples do not establish universal targets. A histogram of 40 values with a clean, narrow spread may be readable with fewer bins; 1,000 values with several distinct clusters may need more. The useful test is whether the chart answers the question while remaining understandable, and whether the broad pattern persists under a small change in settings.
Set and refine the histogram in Excel
- Put one numeric observation in each cell of a single column. A descriptive header is fine, but avoid mixing text labels, blank rows, and unrelated numbers into the data range.
- Select the observations and choose Insert > Insert Statistic Chart > Histogram.
- Right-click the horizontal axis and choose Format Axis.
- Under Axis Options, select Number of bins or Bin width, then enter a value suited to your purpose.
- If extreme values dominate the scale, consider an underflow or overflow bin. Use these only when grouping the tail is appropriate, and make the cutoff clear to readers.
- Add a descriptive chart title and axis labels where needed. State the measurement unit and, if the width is not obvious, the bin interval.
After changing the setting, confirm that the chart still includes the full range you intend to describe. An overflow bin is not the same as deleting large values: it gathers values beyond a cutoff into a final group. Likewise, an underflow bin groups values below a cutoff. If those groups contain many observations, the chart may be hiding important detail, so reconsider the cutoff or use a different width.
Read bin boundaries and avoid misleading interpretations
Every bin has boundaries, and observations near a boundary can move from one bar to another when the width or starting point changes. This is one reason a histogram can look different even when it uses the same data. When a precise business or scientific threshold matters, align the intervals with that threshold and describe the boundaries. Do not assume that a visually attractive chart automatically uses the categories your readers expect.
Bar height in a standard Excel histogram represents the number of observations in a bin, not a percentage or a probability. If you want to compare datasets of very different sizes, raw counts can be misleading: the larger dataset will tend to have taller bars. Consider whether a normalized display or a separate calculation is needed for your comparison, and label the measurement clearly. Also remember that a histogram summarizes a distribution; it does not show the exact value of every individual observation.
Common problems and alternatives
If Excel creates a chart that looks empty or odd, first check that the selected range contains actual numeric values. Dates and times are stored as numbers in Excel, but text that merely looks like a number may not be treated as numeric data. Remove accidental totals from the observations, check for blank cells, and make sure the chart is based on the intended column.
A frequent misconception is that more bins always mean more accurate information. More bars provide finer grouping, but they can also make random variation look important. The opposite misconception is that fewer bars are always clearer: broad bins can hide multiple clusters or a long tail. Try a couple of reasonable settings and compare the conclusions rather than choosing solely by appearance.
Use a histogram when you want to examine the distribution of one numeric variable. Use a regular column chart when the horizontal categories are named groups, such as departments or product types. A box-and-whisker chart can be a useful alternative for comparing medians, spread, and potential outliers across several groups, though it does not show the same interval-by-interval counts. If you need exact custom intervals or a table of counts, you can create bins and calculate frequencies separately, then chart those results; that approach offers control but requires careful boundary definitions.
Excel for Mac has histogram chart support, though exact ribbon placement and pane appearance can vary by release. Excel for the web also supports many chart tasks, but the available formatting controls may differ from the Windows desktop application. If you cannot find the Bins controls, verify that the chart is a histogram rather than a column chart and check the current version’s chart-formatting options.
Frequently Asked Questions
How many bins should I use for 100 data points?
Try roughly 8 to 15 bins as an initial view, then adjust based on the spread and shape. A statistical rule such as Sturges’ gives about eight bins for 100 observations, but it is only a starting point; meaningful measurement intervals or visible clusters may justify another choice.
Should I use bin width or number of bins in Excel?
Use bin width when the interval itself has meaning, such as five minutes or ten dollars. Use number of bins when you want to control the chart’s overall level of detail. Check the resulting boundaries either way, because a chosen width does not necessarily produce an exact target count of bars.
Why does Excel’s automatic histogram look different from the one I expected?
Automatic binning is a starting choice, not a promise to match familiar categories or a particular statistical rule. Open Format Axis and set the number of bins or width yourself. Also check that the source range contains only the observations you intend to chart.
Can a histogram have too many bins?
Yes. If most bars contain very few observations, the chart can become noisy and difficult to interpret. Reduce the count or increase the width, then see whether the broad distribution remains visible.
Do histogram bars show percentages?
In a standard Excel histogram, bar height shows the count of observations in that interval. It does not automatically mean percentage. If percentages are needed, calculate and present them explicitly, and label the chart so readers know what the bars represent.
Related
- Excel Heat Map by ZIP Code: Setup, Limits, and Fixes
- Excel Group Rows Upwards? Put Summary Rows Above
- Excel Grouped Scatter Plot: Setup, Steps, and Tips
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Histogram Axis Labels: Set Bins and Fix IssuesIn Excel for Microsoft 365 on Windows, histogram axis labels are controlled by the chart’s bin settings: select…
- Best Excel Gantt Chart Templates for Every ProjectFor most projects, the Vertex42 Gantt Chart Template is the best place to start: it gives you a…
- Excel Sparkline Shortcut: Insert Line, Column, or Win/LossIn Excel for Microsoft 365 on Windows, select the cell where the sparkline should appear, then press Alt,…
- How to Make a Histogram Start at 0 in ExcelTo make histogram bins start at 0 reliably in Excel, create bin intervals beginning at 0, count the…
- Excel Half Circle Chart: How to Create OneTo make a half-circle chart in Excel, create a doughnut chart with two visible values and a third…
- Excel Exponential Trendline Formula and How to Use ItThe exponential trendline model is y = a ebx, where a is the value predicted when x is…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.