How to Make a Frequency Table in Excel
By ExcelReady TeamUpdated 7 min read
The short answer
A frequency table counts how often each value or range of values appears. In Excel, list the categories or bins in one column and count each with COUNTIF or COUNTIFS, for example =COUNTIFS(B2:B11,">=70",B2:B11,"<80"). You can also use the FREQUENCY function, or a PivotTable that groups numbers into bins for you.
Key takeaways
- For text categories, list each category once and count it with =COUNTIF(range,category).
- For number ranges (bins), COUNTIFS with a lower and an upper limit is the clearest method.
- FREQUENCY returns one more count than you have bins: the last one counts values above the top bin.
- Divide each count by the total to get a relative frequency, and add a running SUM for cumulative frequency.
- A PivotTable with Group can build the same table without formulas, but it needs a refresh when data changes.
On this page+
- What is a frequency table?
- How to count categories with COUNTIF
- How to make a frequency table in Excel with bins (COUNTIFS)
- How to use the FREQUENCY function
- How to add relative and cumulative frequency
- How to build a frequency table with a PivotTable
- Tips for a frequency table that stays correct
- Practise it
A frequency table shows how many times each value, or each range of values, appears in your data. To build a frequency table in Excel, you list the categories or number ranges in one column and count each one with COUNTIF or COUNTIFS. This guide shows four ways to do it, from simple formulas to a PivotTable, plus how to add percentages and running totals.
What is a frequency table?#
A frequency table is a summary with two columns: a value (or a range of values) and a count. "Frequency" just means "how often". If 3 students scored between 70 and 79, the frequency of that range is 3.
There are two kinds of frequency table:
- Categories, such as class, region or product. You count each label.
- Bins, such as score bands 50–59, 60–69 and so on. A bin is a range of numbers that you group together, also called an interval or a class.
All the examples below use this exam data in A1:B11. Column A is the class and column B is the score.
| A | B | |
|---|---|---|
| 1 | Class | Score |
| 2 | A | 52 |
| 3 | B | 67 |
| 4 | C | 71 |
| 5 | A | 85 |
| 6 | B | 90 |
| 7 | A | 78 |
| 8 | C | 64 |
| 9 | B | 73 |
| 10 | A | 88 |
| 11 | C | 95 |
How to count categories with COUNTIF#
For text categories, type each category once and count it with COUNTIF. Put the labels A, B and C in D2:D4, then type this in E2 and fill down (Windows: Ctrl+D; Mac: ⌘D):
=COUNTIF($A$2:$A$11,D2)| Class | Count |
|---|---|
| A | 4 |
| B | 3 |
| C | 3 |
The $ signs lock the data range so it doesn't move as you fill down. D2 has no $, so it becomes D3 and D4. Add =SUM(E2:E4) underneath: it should equal 10, the number of scores. If it doesn't, some labels have typos or extra spaces.
If you have Microsoft 365 or Excel 2021, =UNIQUE(A2:A11) lists the categories for you, so you don't miss one.
How to make a frequency table in Excel with bins (COUNTIFS)#
For number ranges, COUNTIFS with two conditions is the clearest method: one for the lower limit and one for the upper limit. Type the lower limit of each bin in D2:D6: 50, 60, 70, 80, 90. In E2, type:
=COUNTIFS($B$2:$B$11,">="&D2,$B$2:$B$11,"<"&D2+10)Then fill down to E6. The formula reads: count scores that are at least the lower limit and less than the lower limit plus 10.
| Lower limit | Band | Count |
|---|---|---|
| 50 | 50–59 | 1 |
| 60 | 60–69 | 2 |
| 70 | 70–79 | 3 |
| 80 | 80–89 | 2 |
| 90 | 90–99 | 2 |
Using "<" with the next limit, rather than "<="&79, matters when your data has decimals. A score of 79.5 would fall through a gap between <=79 and >=80, but <80 catches it.
The operator goes inside quotes and the cell is joined with &. Writing ">=D2" would compare with the letters D2, not the number in the cell. See the COUNTIFS function guide for the full syntax.
How to use the FREQUENCY function#
FREQUENCY counts values into bins with one formula. It takes the data and a list of upper limits. Put 59, 69, 79 and 89 in G2:G5 and type in H2:
=FREQUENCY(B2:B11,G2:G5)In Microsoft 365 and Excel 2021, the result spills into five cells: 1, 2, 3, 2, 2. FREQUENCY always returns one more count than you have bins. The last count is for values above the top bin, here the two scores of 90 and over.
In Excel 2019 and older, FREQUENCY is an array formula. Select five cells first, type the formula, then press Ctrl+Shift+Enter (Mac: Control+Shift+Return).
FREQUENCY is quick, but the upper-limit logic confuses many people. COUNTIFS is easier to read and to check, so it's the better choice in a shared file or a job test.
How to add relative and cumulative frequency#
A relative frequency is each count as a share of the total. A cumulative frequency is a running total of the counts. Both are one short formula each. With the bin counts in E2:E6:
=E2/SUM($E$2:$E$6)Format the column as a percentage (Windows: Ctrl+Shift+%; Mac: ⌃⇧%). The results are 10%, 20%, 30%, 20% and 20%, which add up to 100%.
For the running total, type this in G2 and fill down:
=SUM($E$2:E2)Only the first E2 is locked, so the range grows as you fill: 1, 3, 6, 8, 10. The last value always equals the total count, which is a handy check.
How to build a frequency table with a PivotTable#
A PivotTable can count and group numbers without any formulas. It's a good choice when the data is large or changes shape often.
- Click inside the data and choose Insert > PivotTable, then OK.
- Drag Score to the Rows area.
- Drag Score again to the Values area. It shows "Sum of Score", so click it, choose Value Field Settings and pick Count.
- Right-click any score in the PivotTable and choose Group.
- Set Starting at to 50, Ending at to 99 and By to 10, then click OK.
You get the same bands and counts as the COUNTIFS table. For categories, drag Class to Rows and any field to Values as a count. The guide to building your first pivot table walks through the basics, and counting with a pivot table covers Count versus Count Numbers.
Remember that a PivotTable doesn't update by itself. When the data changes, refresh the pivot table to see the new counts.
Tips for a frequency table that stays correct#
A frequency table is only as good as its source data and its bins. Keep these in mind:
- Make the bins cover everything. Check that the lowest bin starts at or below the minimum and the top bin ends at or above the maximum.
=MIN(B2:B11)and=MAX(B2:B11)tell you the limits. - Check the total. The counts must add up to
=COUNT(B2:B11)for numbers or=COUNTA(A2:A11)for labels. - Use a table for growing data. Turn the data into an Excel table with Ctrl+T and point the formulas at its columns, so new rows are counted automatically. The article on tables versus plain ranges explains why.
- Clean text first. "B" and "B " (with a space) are different labels to COUNTIF.
Practise it#
Practise COUNTIFS in a live spreadsheet: you'll write two-condition counts like the score bands above, and the sheet checks each formula as you type. Then build your first PivotTable in the pivot tables lesson to see the no-formula version. Or start with the exercise on this page.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
How many scores are in the 70–79 band? Click E2 and use COUNTIFS on B2:B8 with two conditions: greater than or equal to 70, and less than 80. Then press Enter.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Score | Count | ||||
| 2 | 70–79 | |||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 | ||||||
| 9 | ||||||
| 10 | ||||||
| 11 |
Tip: Use the same range twice: B2:B8,">=70" and then B2:B8,"<80".
Practise this in a lesson
Frequently asked questions
How do I create a frequency table in Excel?
Type the categories or bin limits in one column, then count each one with COUNTIF or COUNTIFS in the next column. Add a total row to check that the counts add up to the number of values.
What is the formula for frequency in Excel?
For a category, use =COUNTIF(range,category). For a number range, use =COUNTIFS(range,">="&lower,range,"<"&upper). Excel also has a FREQUENCY function that counts values into bins in one formula.
How do I make a frequency table with intervals in Excel?
Put the lower limit of each interval in a column, such as 50, 60, 70, and count each interval with COUNTIFS using >= the lower limit and < the next limit. A PivotTable can also group numbers into intervals of any size.
How do I calculate relative frequency in Excel?
Divide each count by the total count, for example =E2/SUM($E$2:$E$6), and format the result as a percentage. The relative frequencies should add up to 100%.
Why does FREQUENCY return an extra value?
FREQUENCY always returns one more count than the number of bins. The extra count is for values larger than the highest bin, so nothing gets left out.