How to Make a Frequency Table in Excel (Step by Step)

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

If you want to know how to make a frequency table on Excel, the fastest route is the FREQUENCY function. You list the upper limit of each bin in one column, point FREQUENCY at your raw data and your bins, and Excel returns the count of values in each bin. This works for exam scores, survey ratings, ages, prices, or any column of numbers.

Quick Answer

Put your raw values in one column, for example B2:B31.
Type the upper limit of each bin in a second column, for example 59, 69, 79, 89, 100.
Select the empty cells next to the bins, type =FREQUENCY(data_range, bins_range), and confirm it.
FREQUENCY returns one count per bin, plus one extra count for values above the highest bin.
Select the bins and counts, then insert a clustered column chart to see the distribution.

Before You Start

A frequency table counts how many values fall into each interval. The intervals are called bins or classes, and each bin has an upper limit. A score of 59 belongs to the bin ending at 59, and a score of 60 belongs to the next bin. FREQUENCY counts values that are less than or equal to each upper limit and greater than the previous upper limit.

You need two things in place before you write any formula.

First, your raw data must sit in a single column with no blank cells inside the range. Blank cells are ignored by the count, but a blank row in the middle usually means you selected the wrong range.

Second, decide your bins. Bins must be sorted from smallest to largest, and each upper limit must be larger than the one above it. Overlapping or unsorted bins give counts that look plausible but are wrong.

For a quick rule on how many bins to use, the square root of the number of values is a common starting point. With 30 values, that is about 5 or 6 bins. Equal-width bins are easier to read and easier to chart.

If you want the underlying concept before the mechanics, see what a frequency table is and how to build one.

Step by Step

Enter your raw values in a column. In the example below they go in B2:B31, with a header in B1.

Enter your bin upper limits in a second column. Put a header such as "Bin Upper" in D1 and the limits in D2 downward, smallest first.

Select the block of empty cells beside the bins. If you have five bins in D2:D6, select E2:E6.

Type the formula. With the cells still selected, type =FREQUENCY($B$2:$B$31,$D$2:$D$6).

Confirm the formula. In Excel 365, press Enter and the results spill down the selected cells. In older versions, press Ctrl+Shift+Enter to enter it as an array formula.

Read the counts. Each cell in E2:E6 holds the number of scores in that bin.

Add a chart. Select D1:E6, then go to Insert > Charts > Insert Column or Bar Chart > Clustered Column. That gives you a frequency bar chart of the distribution.

The dollar signs in $B$2:$B$31 and $D$2:$D$6 lock the ranges. That matters if you copy the formula, and it also makes the intent clear when someone else opens the file.

If you are new to writing formulas at all, how to make a formula in Excel covers the basics of cell references and operators.

Worked Example

The dataset is 30 student exam scores, one score per student, listed in column B.

	A	B	D	E
1	Student	Score	Bin Upper	Frequency
2	Ana	78	59	`=FREQUENCY($B$2:$B$31,$D$2:$D$6)` -> 7
3	Ben	45	69	`=FREQUENCY($B$2:$B$31,$D$2:$D$6)` -> 5
4	Cara	92	79	`=FREQUENCY($B$2:$B$31,$D$2:$D$6)` -> 7
5	Dan	67	89	`=FREQUENCY($B$2:$B$31,$D$2:$D$6)` -> 6
6	Ella	85	100	`=FREQUENCY($B$2:$B$31,$D$2:$D$6)` -> 5
7	Finn	73
8	Gia	58
9	Hugo	96
10	Ivy	81
11	Jax	64
12	Kira	89
13	Leo	52
14	Mia	77
15	Noah	70
16	Omar	43
17	Pia	91
18	Quinn	66
19	Rosa	84
20	Sam	75
21	Tara	59
22	Umar	98
23	Vera	82
24	Wes	61
25	Xena	88
26	Yuri	54
27	Zane	79
28	Amy	71
29	Bret	47
30	Cleo	93
31	Drew	68

The five bins cover 0 to 59, 60 to 69, 70 to 79, 80 to 89, and 90 to 100. FREQUENCY returns 7, 5, 7, 6, and 5, which adds up to all 30 scores. In Excel 365 a sixth cell, E7, also shows 0 for scores above 100.

The first result, in E2, counts scores in the 0 to 59 bin. The same single array formula returns the counts for the 60 to 69, 70 to 79, 80 to 89, and 90 to 100 bins in E3 through E6.

To build the table in Excel, select E2:E6, type =FREQUENCY($B$2:$B$31,$D$2:$D$6), then press Ctrl+Shift+Enter, or just Enter in Excel 365. Then select D1:E6 and go to Insert > Charts > Insert Column or Bar Chart > Clustered Column.

The result is a frequency distribution in Excel that shows how the scores cluster. You can add a relative frequency column by dividing each count by the total, and a cumulative frequency column by adding the counts as you go down. For the cumulative version, see how to calculate cumulative frequency.

Other Ways to Do It

FREQUENCY is the standard method, but it is not the only one.

COUNTIFS. For each bin you can write a formula that counts values between a lower and upper bound. For the 60 to 69 bin, that is =COUNTIFS($B$2:$B$31,">=60",$B$2:$B$31,"<=69"). This is easier to read and easier to adjust one bin at a time, but you write one formula per bin and you must keep the boundaries consistent by hand.

PivotTable. Select your data, insert a PivotTable, drag the numeric field into both the Rows area and the Values area, then group the row values into bins. This is fast for large datasets and it updates when the data changes, but the grouping steps differ between Excel versions.

Data Analysis ToolPak. The Histogram tool in the Analysis ToolPak produces a frequency table and an optional chart from a data range and a bin range. You need the add-in enabled first.

Once you have counts, a chart makes the shape obvious. A clustered column chart is the usual choice, and how to make a bar chart in Excel walks through the chart formatting. If your bins are equal width and you want the classic histogram look with no gaps between bars, how to make a histogram in Excel covers that version. For a general charting overview, see how to make a chart in Excel.

Troubleshooting

The formula returns a single number instead of a column. You selected one cell instead of the full block. Select the whole range beside the bins, then re-enter the formula.

The counts do not add up to the number of values. Check for values above your highest bin. FREQUENCY returns an extra count for those, and if you did not select an extra cell for it, that count is dropped. Also check for text values stored as numbers, which are ignored.

The counts look shifted by one bin. Your bins are probably not sorted, or the first bin does not start low enough. Every value must fall at or below the highest bin limit.

The formula shows a spill error. Something is blocking the cells the results need. Clear the cells below the formula and try again.

Editing the formula changes only one cell. In older Excel versions the formula is an array formula. Select the whole range, edit it, and confirm with Ctrl+Shift+Enter.

Common Mistakes

Leaving gaps in the bin column. FREQUENCY reads the bin range as a list of upper limits. A blank cell in the middle breaks the mapping. Fix: fill every bin cell with a number, sorted ascending.
Forgetting the extra bin. FREQUENCY returns one more count than there are bins, covering values above the top limit. Fix: select one extra cell below your last bin so that count has somewhere to land, or set the top bin high enough to cover every value.
Selecting the wrong data range. A range that stops early or includes the header row gives wrong counts. Fix: select only the numeric cells, and lock the range with dollar signs.
Using unequal bins without saying so. Bins of different widths make the chart misleading because a tall bar may just mean a wider interval. Fix: use equal-width bins unless you have a reason not to, and label the intervals clearly.
Treating the counts as percentages. Frequency counts are raw counts. Fix: divide each count by the total if you want relative frequency, and label the column accordingly.
Typing the formula into one cell and dragging. Dragging a FREQUENCY formula down does not reproduce the array result. Fix: select the full output range first, then enter the formula once.

Limitations

FREQUENCY is a static calculation. It does not recalculate into new cells when you add rows, so if your data grows past the original range, the counts silently miss the new values. You have to widen the range and re-enter the formula.

The function also gives you counts only. It does not compute percentages, cumulative totals, or summary statistics, and it does not tell you whether your bin choice was sensible. Change the bins and the shape of the distribution changes, even though the underlying data did not. That is a property of any frequency table, not a flaw in Excel, but it means the bin boundaries are a judgment call you should state when you present the results.

Frequently Asked Questions

How do you create a frequency table in Excel with the FREQUENCY function?

Put your raw values in one column and your bin upper limits in another. Select the empty cells beside the bins, type =FREQUENCY(data_range, bins_range), and confirm it. In Excel 365 press Enter, and in older versions press Ctrl+Shift+Enter. The selected cells fill with one count per bin.

Why does FREQUENCY return one more value than I have bins?

FREQUENCY always returns an extra count for values greater than the highest bin limit. If you have five bins, the formula returns six numbers. Select one extra cell below your last bin so that count has a home, or set your top bin high enough to include every value.

What is the difference between a frequency table and a frequency distribution in Excel?

They describe the same output. A frequency table is the two-column layout of bins and counts. A frequency distribution is the pattern those counts show across the intervals. In Excel you build the table with FREQUENCY and read the distribution from the counts or the chart.

Can I make a frequency table with COUNTIF instead of FREQUENCY?

Yes. Use =COUNTIFS(range,">=lower",range,"<=upper") for each bin, with the lower and upper bounds typed into the formula or read from cells. It is more readable and easier to adjust one bin at a time, but you write one formula per bin and you must keep the boundaries consistent.

How do I turn a frequency table into a chart in Excel?

Select the bin column and the frequency column together, including the headers. Then go to Insert > Charts > Insert Column or Bar Chart > Clustered Column. The bins become the category axis and the counts become the bar heights.

References

This article draws on the standard references listed under Further Reading.