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.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.