Frequency Distribution (Histogram Bins)

All versionsFREQUENCYCOUNTIFS

To group numbers into bands and count each band — how many scores 0–59, 60–79, 80–100 — use FREQUENCY, or a clearer COUNTIFS per bin. It’s the data behind every histogram.

Quick formula: to count values that fall at or below each bin edge in D2:D4:

=FREQUENCY(data, bins)

FREQUENCY returns a count for each bin (plus one for everything above the last edge); enter it as a spilled/array formula next to your bin list.

Functions used (tap for the full reference guide):

FREQUENCY COUNTIFS

The example

Test scores grouped into three bands.

	A	C	D
1	Score	Band	Count
2	55	0-59	1
3	72	60-79	2
4	88	80-100	2
5	91
6	68

The formula

The COUNTIFS-per-bin approach (the clearest version):

=COUNTIFS(A2:A6, ">=60", A2:A6, "<=79") // the 60-79 band → 2

How it works

Two ways to bin; COUNTIFS is the most readable:

Define your bands — here 0–59, 60–79, 80–100.
For each band, a COUNTIFS with a lower and upper bound counts the scores inside it: ">=60" and "<=79" → 2.
The alternative, FREQUENCY(data, bins), returns all bin counts at once from a list of bin edges — but the edge logic (“up to and including”) trips people up.
COUNTIFS makes each band’s rule explicit, which is easier to read and adjust.

Excel 365: the modern way is a PivotTable or a one-formula =GROUPBY(bin, value, COUNT). For a quick chart, the built-in Histogram chart bins for you.

Try it: interactive demo

Live demo

Type scores (commas); see the count in each band.

Scores

Variations

FREQUENCY for all bins at once

One spilled formula from bin edges (59, 79):

=FREQUENCY(A2:A6, {59,79})

Percentage of total in each band

Divide the count by the total:

=COUNTIFS(A2:A6, ">=60", A2:A6, "<=79") / COUNT(A2:A6)

Bins from a column of edges

Point FREQUENCY at a bin-edge range:

=FREQUENCY(A2:A100, C2:C5)

Pitfalls & errors

FREQUENCY edges are “≤” boundaries and it returns one extra count for values above the last edge. Size the output range to bins + 1, or COUNTIFS is clearer.

Overlapping COUNTIFS bands double-count. Make bands adjacent and non-overlapping — <=79 then >=80, not <=80 then >=80.

FREQUENCY is an array formula. In older Excel, select the whole output range and press Ctrl+Shift+Enter; in 365 it spills.

Practice workbook

📊

Download the free Frequency Distribution (Histogram Bins) practice workbook

Scores with live COUNTIFS bin counts, the FREQUENCY array version, and percentage-of-total, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I create a frequency distribution in Excel?

Use a COUNTIFS per band, e.g. =COUNTIFS(data, ">=60", data, "<=79"), or FREQUENCY(data, bins) to return all bin counts at once. COUNTIFS is the most readable.

How does the FREQUENCY function work?

FREQUENCY(data, bins) counts how many values fall at or below each bin edge, returning one count per bin plus one extra for values above the last edge. Enter it as an array (Ctrl+Shift+Enter) in older Excel; it spills in 365.

How do I avoid double-counting in bins?

Make the bands adjacent and non-overlapping, like "<=79" then ">=80", so each value falls into exactly one band.

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