Histogram with FREQUENCY

All versionsFREQUENCY

A histogram buckets values into ranges and charts the counts. FREQUENCY does the binning in one array formula; a column chart of the result is your histogram — works in every version.

Quick formula: with data in A2:A100 and bin upper-bounds in C2:C6:

=FREQUENCY(A2:A100, C2:C6)

Returns how many values fall in each bin. Enter as a spilling array (365) or Ctrl+Shift+Enter in older Excel, then chart the counts as columns.

Functions used (tap for the full reference guide):

FREQUENCY COUNTIFS

The example

Test scores binned into ranges.

	A	B
1	Bin (≤)	Count
2	60	3
3	70	8
4	80	12
5	90	9

The formula

Bin the data, then chart it:

=FREQUENCY(A2:A100, C2:C6) // counts per bin → column chart = histogram

How it works

FREQUENCY counts values into bins:

List your bin boundaries (the upper limit of each bucket) in a column.
FREQUENCY(data, bins) returns the count in each bin — it spills in Excel 365, or use Ctrl+Shift+Enter across the result cells in older versions.
The result has one more row than the bins (the overflow bucket above the top boundary).
Make a column chart of the counts with no gap between bars — that’s a histogram.

2016+ has a built-in Histogram chart (Insert → Statistic Chart) that bins automatically. FREQUENCY still wins when you want control over the exact bins, or the counts as values for other formulas. COUNTIFS with two bounds is another readable way to bin.

Try it: interactive demo

Live demo

Enter values; binned 0-60-70-80-90-100.

Variations

Built-in histogram (2016+)

Insert → Statistic Chart → Histogram.

COUNTIFS bins

Readable two-bound count:

=COUNTIFS(data,">60", data,"<=70")

Relative frequency

Share of total:

=FREQUENCY(data,bins)/COUNT(data)

Pitfalls & errors

One extra row. FREQUENCY returns bins+1 values — the last is everything above the top boundary. Size the output range accordingly.

Array entry in old Excel. Before 365, select the whole output range and press Ctrl+Shift+Enter, or you only get the first bin.

Close the gap. A histogram has no space between bars — set the column chart’s gap width to 0.

Practice workbook

📊

Download the free Histogram with FREQUENCY practice workbook

A FREQUENCY histogram with a column chart, the COUNTIFS and relative-frequency variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I make a histogram in Excel?

Bin the data with =FREQUENCY(data, bins), then make a column chart of the counts with zero gap width. In 2016+, Insert → Statistic Chart → Histogram bins automatically.

Why does FREQUENCY return one extra number?

It adds an overflow bin counting everything above the top boundary, so the result has bins+1 values.

How do I bin without FREQUENCY?

Use COUNTIFS with two bounds: =COUNTIFS(data,">60", data,"<=70") counts values in each range.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: FREQUENCY · COUNTIFS