To make a frequency table in Excel, list your group limits (bins) in one column, select the cell next to the first bin and type =FREQUENCY(data, bins), such as =FREQUENCY(B2:B10, D2:D5). In Microsoft 365 press Enter and the counts spill down; in older versions select the output cells first and press Ctrl+Shift+Enter.
A frequency table tells you how many items fall into each group. How many orders were delivered in 1 to 2 days, 3 to 4 days, 5 days or more. How many clients attended 1, 2 or 3 sessions. It turns a long column of numbers into a short summary you can read in seconds, and Excel has a function built for exactly this job.
Set up your data and your bins
Say column B, rows 2 to 10, holds the number of days each order took to arrive. Decide your groups first. Excel calls the upper limit of each group a bin.
| Cell | Bin value | Counts orders that took |
|---|---|---|
| D2 | 2 | Up to 2 days |
| D3 | 4 | More than 2, up to 4 days |
| D4 | 6 | More than 4, up to 6 days |
| D5 | 8 | More than 6, up to 8 days |
Each bin counts values above the previous bin and up to and including its own. Type the bins in order, smallest first.
Building the table with FREQUENCY
- Click E2, next to your first bin.
- Type =FREQUENCY(B2:B10, D2:D5).
- If you have a current version of Microsoft 365, press Enter. Microsoft says the results spill into the cells below as a dynamic array.
- In older versions, select E2 to E6 first, type the formula, and press Ctrl+Shift+Enter. Excel adds curly brackets around the formula to show it's an array formula.
- Add labels in column C, such as 1 to 2 days, so the table reads clearly.
The extra row nobody expects
This is the catch. Microsoft says FREQUENCY always returns one more result than you have bins. The extra number, at the bottom, counts every value above your highest bin. With four bins you get five results, and the fifth counts orders that took more than 8 days.
In Microsoft 365 that extra row spills automatically, so leave space for it. In older versions, Microsoft's advice is to select one more cell than you have bins before you press Ctrl+Shift+Enter, otherwise the over-the-top count is lost. Label that last row something like More than 8 days.
What FREQUENCY skips
Microsoft says FREQUENCY ignores blank cells and text. That's helpful when a few orders haven't arrived yet and the cell is empty, but it also means any delivery times typed as text, such as 3 days, won't be counted at all. Keep the column to plain numbers and put any notes in a separate column.
A COUNTIF version that's easier to edit
If array formulas feel fiddly, you can build the same table with COUNTIF or COUNTIFS, one cell at a time. COUNTIF counts the cells that meet a condition, and COUNTIFS handles two conditions, which is what a range needs:
- Up to 2 days: =COUNTIF(B2:B10, "<=2")
- 3 to 4 days: =COUNTIFS(B2:B10, ">2", B2:B10, "<=4")
- 5 to 6 days: =COUNTIFS(B2:B10, ">4", B2:B10, "<=6")
- More than 6 days: =COUNTIF(B2:B10, ">6")
It's more typing, but each row is independent, so you can change one group without touching the others. For a simple table of exact values, such as how many clients attended 1, 2 or 3 sessions, a plain =COUNTIF(B2:B10, 1) for each value is often all you need.
Turning the table into a chart
Once you've got your counts, select the labels and the results and insert a column chart from the Insert tab. A frequency table drawn as columns is a histogram in all but name, and it shows at a glance whether most orders arrive quickly or whether there's a long tail of slow deliveries.
If you'd like percentages as well as counts, divide each count by the total number of orders. My guide to dividing in Excel covers that formula and the divide-by-zero error you'll hit if the data's empty. For a chart that does the grouping for you, my guide to making a histogram in Excel covers the built-in chart and its bins.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Choosing your groups
Bins are a judgement call. Too many and each group holds one or two items; too few and the table hides the pattern. For a small business list of a few dozen rows, four to six groups usually works. Keep the groups the same width where you can, such as every 2 days, so the counts compare fairly.
Keeping the table up to date
A frequency table is only as current as its data range. If you add new orders below row 10, extend the data range in the formula, or turn the order list into a table with Ctrl+T and refer to the table column instead, so new rows are included automatically. With the dynamic array version, the counts recalculate the moment you change a delivery time, which makes it easy to keep a weekly check on how quickly orders are arriving. I'd keep the bins on the same sheet, clearly labelled, so anyone reading the table understands the groups without asking.
The short version from me
List your bins, use =FREQUENCY(data, bins), and leave room for the extra row that counts everything above the top bin. If you'd rather avoid array formulas, COUNTIFS does the same job one row at a time. I'd start with FREQUENCY on Microsoft 365, because it spills the whole table with one Enter.
Frequently asked questions
What is a bin in an Excel frequency table?
It's the upper limit of each group. FREQUENCY counts values above the previous bin and up to that bin.
Why does FREQUENCY return one more value than my bins?
The extra value counts everything above your highest bin, as Microsoft explains.
Do I need Ctrl+Shift+Enter for FREQUENCY?
Not in a current version of Microsoft 365, where Enter works. Older versions need Ctrl+Shift+Enter.
Can I make a frequency table without FREQUENCY?
Yes. Use COUNTIF for single limits and COUNTIFS for ranges between two limits.
Does FREQUENCY count text or blank cells?
No. Microsoft says it ignores both.
