Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to build a frequency distribution in Excel and Google Sheets
Topic:COUNT & counting patternsExcel & Google Sheets
=FREQUENCY(A2:A11,C2:C5)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Put your raw numeric data in A2:A11 (or however many rows you have).
- 2Define bin upper bounds in C2:C5 in ascending order — for example 25, 50, 75, 100. Each bin captures values greater than the previous bound and up to and including the current bound.
- 3In Excel 2019 and earlier, select D2:D6 (one extra cell beyond the number of bins for the overflow bucket), enter =FREQUENCY(A2:A11,C2:C5), and confirm with Ctrl+Shift+Enter. In Excel 365 and Sheets, just press Enter and the result spills.
- 4Add bin labels in E2:E6 (e.g. 0-25, 26-50, 51-75, 76-100, >100) to make the distribution readable.
- 5To compute percentages, divide each frequency by COUNT(A2:A11). In Sheets a single ARRAYFORMULA does this in one step.
Tips
- FREQUENCY always returns one more value than the bins array — the last element is the overflow count (values above the highest bin). Always select that extra cell.
- Bins must be in ascending order; out-of-order bins produce incorrect counts silently with no error.
- Combine FREQUENCY with a bar chart (select the frequency column and the labels column) to produce an instant histogram.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Why does FREQUENCY return more zeros than expected?
Check that your data contains actual numbers, not text-formatted numbers. FREQUENCY ignores text values. Select a data cell and confirm the format is Number, not Text.
Can I use dynamic bin widths?
Yes — generate your bins with SEQUENCE: =SEQUENCE(4,1,25,25) produces 25, 50, 75, 100. Reference the SEQUENCE result as the bins argument.
More on COUNT & counting patterns
See all →How-to
How to count how many of a weekday are in a month
Count the number of Mondays, Fridays, or any specific weekday occurring within a given month using SUMPRODUCT and WEEKDAY.
How-toHow to count overlapping date ranges
Count overlapping date ranges in Excel or Google Sheets with SUMPRODUCT — detect how many date intervals share at least one day with a reference range, no VBA required.