In Excel for Microsoft 365 on Windows, select a histogram and use Format Axis to change its bins, then format the series, labels, and chart elements to control its appearance. The main options are bin width or number of bins, optional underflow and overflow bins, axis and data labels, bar spacing, colors, and chart titles.
What a histogram shows
A histogram groups numeric observations into ranges, called bins, and shows how many values fall in each range. For example, a set of delivery times might be grouped into intervals of 0–10 minutes, 10–20 minutes, and 20–30 minutes. The height of each bar represents the count in that interval. The chart helps reveal the distribution: its center, spread, skew, gaps, or unusually high and low values.
This is different from an ordinary column chart. A column chart usually compares separate categories, such as sales by department, and leaves a gap between categories. In a histogram, the bins represent adjoining ranges on a numeric scale, so the bars normally touch. A histogram is useful only when the source data are numeric measurements or values that can sensibly be grouped into numeric intervals.
In Excel, select the numeric data, including an optional header, and choose Insert > Insert Statistic Chart > Histogram. If the chart appears as an unexpected set of categories, check that the selected values are numeric and that the correct column was included.
Where the formatting options are
Click the chart to show the chart tabs, then select the part you want to change. To adjust bins, right-click the horizontal axis and choose Format Axis. This opens the Format Axis pane, where the axis settings include options for how Excel divides values into bins. The precise controls shown depend on the selected chart element.
For other changes, use the chart’s contextual tabs or the pane for the selected element:
- Chart Design offers chart styles, color choices, and commands to add or change chart elements.
- Format contains controls for selected chart elements, including shape and text formatting.
- Right-clicking a bar, axis, title, or chart area opens a shortcut menu with commands related to that selection, such as Format Data Series or Format Axis.
If the pane is open but shows the wrong settings, click the axis or series again. For example, selecting the horizontal axis exposes bin controls, while selecting a bar exposes series formatting. On Excel for Mac, the same general chart elements and formatting panes are available, but the ribbon and pane layout can differ. Excel for the web supports chart editing, but some advanced formatting controls may be less extensive than in the desktop app.
Choose bin width or number of bins
The most consequential histogram format choice is how many bins to use and how wide they should be. In Format Axis, under Axis Options, Excel can determine bins automatically, or you can set a specific bin width or number of bins. These are alternative ways to control the grouping: choose the setting that matches your goal rather than trying to force both at once.
- Bin width sets the numeric span represented by each bin. Use it when the interval itself has a meaningful interpretation, such as grouping test scores in steps of 10 points or transaction amounts in steps of $50.
- Number of bins asks Excel to divide the observed range into a chosen number of groups. Use it when you want a particular level of visual detail but do not require a particular interval size.
- Automatic lets Excel select a grouping based on the data. It is a useful starting point, not a guarantee that the result best explains the data.
For instance, if delivery times run from about 2 to 58 minutes, a 10-minute bin width produces intervals that are easy to explain. A larger width will make the chart simpler, but may hide variation; a smaller width may reveal clusters, but can make random fluctuations look important. Review the axis labels after changing a setting, since Excel may choose a different number of visible intervals or a different starting point.
There is no universally best bin count. A small dataset may look misleading when split into many narrow bins because most bars will be empty or contain only one observation. With a large dataset, very wide bins can conceal separate peaks or meaningful differences. Compare a couple of reasonable settings and choose one that is legible and appropriate to the question.
Set underflow and overflow bins
Underflow and overflow bins collect values outside a specified threshold. In the axis options, an Underflow bin groups values at or below a boundary, while an Overflow bin groups values above a boundary. These settings can keep extreme observations from creating several nearly empty bins at the edges of a chart.
Suppose most customer wait times are below 30 minutes, but a few are much longer. An overflow bin can group times above a chosen cutoff together, making the central pattern easier to see while still accounting for the long waits. Do not use this option to make inconvenient observations disappear: the grouped values remain part of the distribution, but the chart no longer shows their individual spread. If the extremes are important, show them separately or explain the threshold in a note.
Format axes, titles, and data labels
Axis text should make the groupings understandable. The horizontal axis communicates bin ranges; the vertical axis normally communicates frequency, or count. Add a chart title and axis titles when a reader might not know what the values measure. Select the chart and use Chart Design > Add Chart Element to add chart elements, or use the chart’s plus button when it is available in the desktop interface.
Data labels can make exact counts visible, particularly when the chart has only a few bins. They can also crowd a chart with many bars. To add them, select the series and use Chart Design > Add Chart Element > Data Labels, then choose a placement if the menu offers one. Check that labels do not overlap or obscure the distribution. The vertical axis already communicates approximate counts, so labels are not always necessary.
Use axis number formats carefully. A frequency axis should usually display readable whole-number counts, while bin labels should retain units or decimal precision that matter to the data. For example, rounding a measurement to whole units may be reasonable for a broad overview but confusing when the bins are narrow. If a label seems unclear, revise the source units or add an axis title rather than relying on a reader to infer the scale.
Adjust bar spacing, color, and chart style
Histogram bars typically touch because each bin follows the previous one. If you want to change their appearance, select a bar, open Format Data Series, and look under Series Options for Gap Width where it is available. A zero or small gap preserves the continuous look. A visible gap may help distinguish bins in a dense chart, but too much spacing can make the histogram resemble a categorical column chart.
For a quick visual change, use Chart Design > Change Colors or select a built-in chart style from Chart Design. To set a particular fill, select the series and use the formatting pane’s fill controls. A single restrained color is often effective for one distribution. If you use a different color for one bar, make sure the emphasis has a clear purpose, such as drawing attention to a target range, and explain it if the meaning is not obvious.
Formatting should support comparison, not replace it. Keep scales consistent when comparing histograms across groups. Different bin widths or axis ranges can make two similar distributions appear different, or conceal a real difference. A clear title, units, readable axis labels, and a sensible bin width usually matter more than decorative effects.
Histogram chart versus the Analysis ToolPak
The built-in Histogram chart and the Histogram command in the Analysis ToolPak are related but not identical workflows. The chart is convenient for a chart that responds to its source data and offers the familiar chart formatting controls. The ToolPak’s Histogram analysis produces a frequency table from specified input data and bin boundaries, and can optionally produce a chart. It is useful when you need a visible frequency output table as well as a chart.
To use the ToolPak on Windows, enable it if necessary through File > Options > Add-ins. At the bottom, choose Excel Add-ins in the Manage box and select Go, then check Analysis ToolPak. Once enabled, find Data > Data Analysis > Histogram. The dialog asks for an input range and, optionally, a bin range. ToolPak bin entries are upper bounds: a value is counted in the first bin whose upper bound it does not exceed, with values above the final bound reported in an overflow category.
The ToolPak chart is not the same as an automatically created histogram chart with the same interactive bin settings. If you need to change the analysis boundaries, edit the bin range and rerun the analysis. If the output chart needs extensive styling or you want bins to update from chart-axis settings, the built-in chart may be more convenient. The ToolPak is an add-in; availability and setup steps can differ by platform and installation.
Common problems and how to fix them
- The histogram command is unavailable or the chart looks wrong: confirm that the selected source contains numeric values. Text-formatted numbers, blank cells, or a selection that includes unrelated columns can interfere with the intended result.
- There are too many or too few bars: select the horizontal axis, open Format Axis, and adjust bin width or number of bins. Check whether automatic grouping is obscuring the pattern you want to inspect.
- Bars have gaps: inspect the series’ gap width. A histogram usually benefits from touching or nearly touching bars, unlike a conventional column chart.
- Extreme values dominate the scale: consider an overflow or underflow bin, but disclose the cutoff. Alternatively, keep the full range and discuss the outliers rather than hiding them.
- The chart does not update as expected: verify that new records are included in the chart’s source range. If the source is an Excel table, adding rows to that table generally makes it easier for the chart range to expand with the data; still check the chart after changing the source.
A related misconception is that changing a histogram’s format changes the underlying observations. Bin settings change how values are grouped for display; they do not edit the source measurements. Conversely, a polished chart does not correct missing, duplicated, or incorrectly entered data. Check the source before drawing conclusions from the shape.
How to choose settings for a clear result
Start with the question the chart should answer. If a manager needs to know how many orders took 0–1, 1–2, or 2–3 days, choose a meaningful fixed bin width. If you are exploring an unfamiliar dataset, begin with Excel’s automatic bins and then test alternatives. For a report, include enough context that someone can interpret the horizontal and vertical axes without seeing the source worksheet.
- Confirm that the data are numeric, consistently measured, and in the same units.
- Create the histogram and inspect the automatic grouping as a first draft.
- Choose a bin width or count that reveals the pattern without producing excessive empty or noisy bars.
- Decide whether extreme values need separate visibility or can reasonably be grouped in an overflow or underflow bin.
- Add a descriptive title, units, and only the labels that improve interpretation.
- Check the chart at its final display size and compare bin settings if the conclusion changes noticeably.
For example, a histogram of 200 exam scores might use 10-point bins to communicate the share of scores in broad ranges. If the purpose is to identify a sharp cluster around a pass mark, narrower bins near that threshold may provide more detail, but the chart should still show enough context to avoid implying that the chosen cutoff is a natural boundary in the data. The right settings depend on both the distribution and the reader’s question.
Frequently Asked Questions
How do I change the bin size in an Excel histogram?
Right-click the horizontal axis, choose Format Axis, and under Axis Options set Bin width. If you want a chosen number of groups rather than a chosen numeric interval, use Number of bins instead.
Can I set both bin width and number of bins?
They are alternative ways to control the grouping in the built-in histogram chart. Choose the one that suits your goal; Excel determines the other aspect from the data and setting.
Why do my histogram bars touch?
Adjacent bars represent adjoining numeric intervals, so touching bars are the normal histogram convention. You can adjust gap width in the series formatting options where available, but large gaps can make the chart look like a categorical column chart.
Can I change the labels shown on the horizontal axis?
The labels are based on the bins and the data, so adjust the bin settings to change the intervals. You can also add an axis title to describe the measurement and units. If you need fully custom categories or boundaries, consider preparing a frequency table and charting that output.
What is the difference between an overflow bin and deleting outliers?
An overflow bin groups values above a threshold into one displayed category; it does not remove them from the chart’s count. Deleting outliers changes the data being represented and can mislead if the exclusions are not justified and disclosed.
Related
- Excel Goal Seek With Two Variables: Solver Options
- Excel Goal Seek With Multiple Inputs: Use Solver
- Excel Goal Seek Precision: Accuracy and Limits
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Stacked Line Chart ExplainedAn Excel stacked line chart plots each series as a running total of the series before it, rather…
- Excel Chart Shortcut: Create a Chart in Seconds on WindowsIn Excel for Microsoft 365 on Windows, select the data you want to chart and press Alt+F1 to…
- Excel Chart With Two Different Scales: How to Make OneYes. In Excel for Microsoft 365 on Windows, put one data series on a secondary axis: select the…
- Excel Line Chart With Multiple Lines: How to Create OneTo create an Excel line chart with multiple lines, put the shared categories—such as dates—in the first column…
- Histogram in Excel Using Formulas: Build Bins and ChartsTo make a histogram in Excel with formulas, list your data, set bin limits, and use FREQUENCY to…
- Balloon Chart in Excel: A Step-by-Step TutorialTo make a balloon chart in Excel for Microsoft 365 on Windows, arrange your data in columns for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.