In Excel: use =FREQUENCY(data_array, bins_array) — it counts how many values fall into each bin and returns one more result than there are bins.
On this page8
Syntax
Arguments
| Argument | required / optional | Description |
|---|---|---|
data_array | required | The values to count. |
bins_array | required | The upper bound of each bin, in ascending order. |
Related functions
6 entries
| Value | Count | Share |
|---|---|---|
| Berlin | 3 | 50.0% |
| Munich | 2 | 33.3% |
| Hamburg | 1 | 16.7% |
Need it as an auditable file?
Ships inside the linked template — formula-driven, unlocked, audit-ready.
What this does
FREQUENCY buckets a column of numbers into bands and counts each one. Two details govern its use. It always returns one element more than the bins array, the extra being everything above the last bin — so a spilled result that looks one row too long is correct. And each bin counts values greater than the previous bound and less than or equal to its own, so the boundaries are inclusive at the top. In Excel 365 it spills; older versions need the output range selected first and Ctrl+Shift+Enter. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “excel frequency function”. Start on a copy or a tiny sample, keep the affected formula visible, and compare the result with the tool above before you touch the real workbook. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. The point is a calculation you can defend to a CFO or an auditor, but the practical win is that someone else can open the file and understand what happened without asking you.
A worked example
Scores in B2:B500 with bin bounds 20, 40, 60, 80 in D2:D5: =FREQUENCY(B2:B500, D2:D5) returns five counts — up to 20, 21-40, 41-60, 61-80, and everything above 80. That five-row block is exactly the input a histogram chart needs. FREQUENCY produces the counts a histogram needs in one step, and its off-by-one output is the detail that makes or breaks the chart. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.
In Google Sheets
If you are in Google Sheets rather than Excel, the good news is that the formula shown here is identical and the workflow barely changes — menus sit across the top instead of in a ribbon, and a few function names differ slightly, but anything you build here moves across with little or no rework. Keep this page bookmarked for the next time the same question comes up. Better still, rebuild the example once in your own sheet — doing it yourself, with the tool above to check against, is what turns a copied formula into a technique you own. The short version of “excel frequency function”: the answer is at the top of this page, the tool proves it on your own numbers, and the sections above explain why it holds so the next variation does not stump you. Excel rewards people who reference cells instead of typing values and who keep inputs separate from formulas, because that is what makes a result you can audit months later. Build it once, deliberately, with the live tool as a check, and you convert a one-off lookup into a reusable skill — which is the whole point of learning the why and not just the what.
Common mistakes
- Allowing only as many output cells as bins, which drops the final overflow count.
- Bins in the wrong order — they must ascend, or the counts are meaningless.
- Misreading the boundaries: a value exactly equal to a bin bound falls in that bin, not the next.
Frequently asked questions
Why does FREQUENCY return one more value than I have bins?
The extra element counts everything above the highest bin. It is not an error, and dropping it loses data.
Are the bin boundaries inclusive?
Yes at the top: each bin counts values greater than the previous bound and less than or equal to its own.
Can I use COUNTIFS instead?
Yes, and it is often clearer: =COUNTIFS(range,">20",range,"<=40") counts one band with visible boundaries.
Other ways people ask this
On the way here you may have searched this as “excel function frequency”, “how to use the frequency function in excel” and “how to use frequency function in excel” — it is all the same task, and this page is the single, complete answer to it.
Why do people search for this in so many different ways?
Because the same task has many names. “excel function frequency”, “how to use the frequency function in excel”, “how to use frequency function in excel” all point at the one operation explained on this page, which is why they all lead here.