Histograms and Frequency Distributions
A histogram wants one column of raw values and does the grouping itself, so feeding it counts you already totalled is why a first attempt looks wrong. Get the input right and the remaining decision is how coarsely to cut: Automatic derives a width from the spread of your data and rarely picks a round one, so an explicit width, a fixed count of groups, or thresholds that gather each tail into a single end column are all worth setting deliberately from Format Axis. Worth settling before any of that is whether this is the right chart at all, because a histogram carries meaning in the horizontal position — reordering its bars would be nonsense, where reordering a bar chart of regions or products is free. That is the cleanest test of which one you are looking at. Excel 2016 brought the chart type; earlier versions reach the same picture through the Analysis ToolPak or FREQUENCY.
The formula
=FREQUENCY(B2:B21, D2:D6)
D2:D6 are the bin UPPER bounds, and each bin means "greater than the one
below it, less than or equal to this one". The result is always one number
longer than the bin list: the extra final value counts everything above the
highest bound. Excel 365 spills it; in older versions select a column one
cell taller than the bin range and commit with Ctrl+Shift+Enter.
=COUNTIFS(B2:B21,">69",B2:B21,"<=79") one bin written out on its own, when a spilled array is unwelcomeA worked example
B2:B21 holds twenty exam scores — 44, 52, 61, 61, 66, 68, 70, 70, 70, 72, 74, 79, 81, 83, 85, 88, 90, 91, 97 and 100 — and D2:D6 holds the bin upper bounds 59, 69, 79, 89 and 99.
=FREQUENCY(B2:B21,D2:D6) in E2, then Insert > Insert Statistic Chart > Histogram over B2:B21 for the picture.
Six numbers come back for five bins: 2, 4, 6, 4, 3 and 1. The first counts everything at or below 59, each of the next four counts what is above the previous bound and up to its own, and the sixth is the overflow — the single score of 100 sitting above the highest bound. They total twenty, and that total is the check worth running every time: if it falls short of the row count, values escaped past the ends of the bin list. The chart drawn straight from B2:B21 shows the same distribution, but with bins Excel chose for itself, which is why Format Axis is nearly always the next step.
Which one do I need?
| If you want to… | Use |
|---|---|
| One column of raw measurements, and the shape of the distribution is the question | Insert > Insert Statistic Chart > Histogram, which does the binning for you |
| The count per bin has already been worked out in a table | A clustered column chart with Format Data Series > Gap Width set to 0, which draws the same picture from counts. The Histogram type would bin the counts themselves |
| Excel 2013 or earlier, or a colleague who has to open the file there | File > Options > Add-ins > Analysis ToolPak, then Data > Data Analysis > Histogram against a column of bin upper bounds |
| The bins are too wide, too narrow, or land on untidy numbers | Select the horizontal axis and open Format Axis, where "Bin width" and "Number of bins" both override Automatic — which decides for you and rarely decides on round numbers |
| A handful of extreme values is stretching the whole axis flat | Format Axis > tick Overflow bin and Underflow bin and set their thresholds, which gathers each tail into one column |
| The data is categories — regions, products, statuses — rather than measurements | A bar or column chart. Binning needs a numeric variable to cut into ranges |
| Categories ordered largest first with a cumulative percentage line over them | A Pareto chart, which sits directly beside Histogram in the same Statistic Chart menu |
| Several groups need their spreads compared on a single picture | A box and whisker chart — comparing groups as histograms costs one chart per group |
| A smooth normal curve is wanted rather than counted bars | That is a plotted NORM.DIST curve over a range of x values, not a tally of your rows |
| The counts are needed as numbers in cells | =FREQUENCY(data, bins) returns them as an array one element longer than the bin list |
Frequently asked questions
How do I change the bin size on an Excel histogram?
Select the horizontal axis and open Format Axis > Axis Options, where four radio buttons decide the cut. "Bin width" fixes the size of each group directly, so 10 gives you 0-10, 10-20 and so on. "Number of bins" divides the whole range into that many equal slices instead, letting the width fall where it may. "By Category" abandons cutting and treats each distinct entry as its own group. "Automatic" derives a width from the spread of your data, which is why two charts built from related columns often refuse to line up until a width is typed into both.
Why did Excel label my histogram with text instead of number ranges?
Two causes, both fixable in Format Axis. Either By Category is selected, which tells Excel to treat each distinct entry as a ready-made group instead of cutting the range up, or the column is not numeric at all — figures imported as text are left-aligned by default and cannot be binned. Convert them with Data > Text to Columns, finishing on the General column format, then set the axis back to an explicit width.
What are the overflow and underflow bins for?
They collapse each tail into a single column. Set Overflow bin to 200 and every value above 200 is counted together and labelled as greater than 200, instead of spreading a few extreme readings across a dozen near-empty bins and squashing the rest of the chart into the left-hand corner. Underflow bin does the same at the bottom end. Both are tickboxes in Format Axis.
How do I make a histogram in Excel 2013 or earlier?
The chart type does not exist there, so you produce the counts yourself and plot them. Either enable the Analysis ToolPak and run Data > Data Analysis > Histogram against a Bin Range column of upper bounds, which writes a frequency table and can draw the chart output, or enter =FREQUENCY(data,bins) as an array with Ctrl+Shift+Enter and plot the result as a column chart with the gap width set to zero.
New guides and tools, once a month
DE + EN · double opt-in · no spam