How to Make a Frequency Table in Excel | Counts You Can Trust

A frequency table is one of those Excel skills that pays off right away. You drop in a messy list of values, then you get an instant snapshot of what shows up most, what barely appears, and what’s missing.

This comes up a lot in schoolwork and real projects: quiz scores, survey responses, product sizes, ticket categories, time-to-complete data, and more.

What A Frequency Table Shows In Excel

A frequency table has two core parts: the thing you’re counting and the count for each thing.

For categories, the “thing” is the label itself (like “Blue,” “Late,” or “Refund”). For numbers, the “thing” can be each distinct number, or it can be a range of numbers, like 0–9, 10–19, 20–29.

Once the counts are in place, you can sort, add percentages, and build a chart without scrolling through the raw list again and again.

Before You Count: Clean The Column So The Counts Match Reality

When a frequency table looks wrong, the problem is often the input, not Excel.

Spend a short setup pass here, and your table will behave the way you expect.

Remove The “Invisible Differences”

• Trailing spaces: “Yes” and “Yes ” look identical on screen, but they count as two different values.
• Mixed types: The number 12 and the text “12” may not group together.
• Case splits: “Blue” and “BLUE” can end up as separate labels in some workflows.

Decide What Blanks Mean In Your Sheet

Empty cells can mean “missing data,” or they can mean “not applicable.” Your frequency table will reflect whichever choice you make.

If blanks should not count, filter them out or clean them before you build the summary. If blanks matter, keep them and label them clearly.

Make The Source Range Expandable

If you expect new rows later, convert the range into an Excel Table (the kind you get from Home → Format as Table).

This helps your summary stay connected to the full dataset as it grows, instead of freezing on last week’s rows.

How to Make a Frequency Table in Excel Step By Step

This method uses a PivotTable. It’s the smoothest option when you want a clean frequency table with sorting, filters, and a simple refresh when data changes.

You can use it for text categories, repeated labels, or even numbers you want to count as distinct values.

Step 1: Select A Cell In The Data

Click any cell inside the column you want to summarize. If your data is an Excel Table, that’s enough.

If your data is a plain range, include the header row and the full set of rows you want counted.

Step 2: Insert A PivotTable

Go to Insert → PivotTable. Choose New Worksheet if you want the result on a clean page.

If you want Microsoft’s official steps and screenshots, use Microsoft’s PivotTable setup instructions.

Step 3: Put The Field In Rows

In the PivotTable Fields pane, drag the column you’re counting into Rows.

You should see each distinct value appear as a row label.

Step 4: Put The Same Field In Values And Set It To Count

Drag that same field into Values.

For text, Excel usually defaults to Count. For numbers, it may default to Sum. If you see a sum, switch it.

Switch “Sum” To “Count”

Click the Values field drop-down, choose Value Field Settings, then pick Count.

Now your PivotTable is a frequency table: each label on the left, the count on the right.

Step 5: Sort And Filter The Output

To see the most common items first, sort the count column from largest to smallest.

If you see a “(blank)” row and you don’t want blanks counted, filter “(blank)” out using the Row Labels filter.

Step 6: Add Percent Share

Counts tell you volume. Percent share tells you weight.

Right-click a count value, choose Show Values As, then select % of Grand Total.

If you want both count and percent at once, add the same field to Values a second time, then set only one copy to percent.

Making A Frequency Table In Excel With Ranges And Buckets

Sometimes counting each distinct value is too noisy. If you have hundreds of different numbers, a frequency table by ranges is easier to read and easier to chart.

This is common with test scores, time spent, ages, weights, prices, and any measurement-style column.

Option A: Group Numbers Inside A PivotTable

Build the PivotTable the same way as the steps above, with the numeric field in Rows and Count in Values.

Then right-click any row label number and choose Group. Enter a starting number, an ending number, and a group size (like 5 or 10).

Excel will replace individual numbers with ranges, and your counts become a binned frequency table.

Option B: Make Custom Buckets With A Helper Column

Grouping works well when you want equal steps. It’s less handy when you want uneven buckets like “Under 18,” “18–24,” “25–34,” “35–49,” “50+.”

For that pattern, create a helper column that assigns each row to a bucket using IF logic, then count buckets with a PivotTable.

The helper column makes the rules visible in the worksheet, which is useful when you need to explain how you built the bins.

Option C: Build A Frequency Table With COUNTIF

COUNTIF is a strong choice when you want a frequency table that updates instantly, without a refresh step.

It’s also great when you need a fixed order. A PivotTable may sort labels in a way you don’t want, while a COUNTIF table follows your list order.

Set Up The Layout

Put your category list (or bucket labels) in one column. Next to it, create a Count column.

In the Count cells, use COUNTIF to count how often each label appears in the source data.

Use COUNTIF With Clear References

Lock the source range with dollar signs so the formula copies down cleanly.

Microsoft’s official COUNTIF explanation is here: Use the COUNTIF function in Microsoft Excel.

Add Percent And Cumulative Columns

To add percent share, divide each count by the total of the Count column.

To add a cumulative count, sum from the first count down to the current row. A cumulative percent is that running sum divided by the grand total.

Choose The Best Method For Your Sheet

All of these methods can produce a solid frequency table. The best one depends on how your data changes and how you want the table to behave.

This comparison keeps it simple, so you can pick fast and move on.

Method	Best Fit	What To Watch
PivotTable (categories)	Text labels, repeated values	Needs refresh after edits
PivotTable grouping	Numbers that need equal-step bins	Bin settings can shift if the field changes
Helper column + PivotTable	Custom buckets like “50+”	Helper rules must stay consistent
COUNTIF table	Fixed order lists, live updating	Source range must include new rows
COUNTIFS table	Counts with filters (two conditions)	Formulas get longer, keep them readable
PivotChart from PivotTable	Fast charting from the summary	Chart reflects PivotTable filters
Range bins + COUNTIF	Hand-built numeric bands	Define band rules clearly to avoid overlap
Excel Table source	Any setup that needs to grow	Keep headers clean and consistent

Make Your Frequency Table Easier To Read

A frequency table is supposed to reduce work, not add it. A few layout moves can make it clear at a glance.

Sort With A Goal

Sort by count when you want to see the most common values first.

Sort by label when you want a logical sequence, like days of the week, grade bands, or size ranges.

Label Bins Like A Human Would Read Them

If you’re using ranges, write the labels the same way across the column: “0–9,” “10–19,” “20–29.”

Keep the dash style consistent, and keep spacing consistent, so the table feels tidy.

Keep The Summary Close To The Data

If this is for a class assignment, placing the frequency table on the same sheet can help graders follow your work.

If this is for a report or dashboard, put the frequency table on a Summary sheet and keep the raw data separate.

Common Problems And Fixes When Counts Look Wrong

When the numbers don’t make sense, the fix is often simple. It’s usually a hidden character, a range that didn’t expand, or a mismatch between text and numbers.

Use this troubleshooting table to match what you see to a clean fix.

What’s Off	What You Notice	Clean Fix
Trailing spaces	Two labels look identical but count separately	Clean with TRIM or remove extra spaces with Find/Replace
Mixed case	“Blue” and “BLUE” show as separate rows	Normalize text in a helper column using LOWER or UPPER
Numbers stored as text	Grouping fails or labels look odd	Convert to numbers with VALUE or Text to Columns
Blanks included	“(blank)” appears in the summary	Filter blanks out, or fill missing cells with a clear label
New rows not counted	Recent entries are missing from the totals	Use an Excel Table source or adjust the counted range
PivotTable not refreshed	Counts lag behind edits	Right-click the PivotTable and select Refresh
Bin edges misread	Values land in the “wrong” band	Confirm whether your bin list uses upper bounds and label bands to match
Hidden characters	One label refuses to group with the same label	Re-type the value, or clean with CLEAN in a helper column

Build A Repeatable Setup That Holds Up On New Data

If you’ll do frequency tables often, build the sheet so the next one is painless.

The goal is simple: your counts should still be correct after someone adds rows, edits labels, or pastes new data into the column.

Use A Clear Data Block

Keep one header row, then your data rows, with no blank rows inside the block.

This helps Excel detect the right range, and it helps you avoid leaving out rows by mistake.

Keep “Cleaning” Separate From “Reporting”

If you need TRIM, CLEAN, or case changes, do them in helper columns, not in the final summary table.

That keeps the summary focused on results, while the helper columns show how you made the results dependable.

Do A Final Check Before You Share Or Submit

• Do your counts match the number of nonblank rows you intended to count?
• Do percent values add up to 100% (small rounding drift is normal)?
• Do your labels mean one thing, with no stray spaces or hidden characters?
• If you used bins, do the bin labels match the bin rules?

Once those checks pass, your frequency table is ready for charts, summaries, and clean write-ups that don’t require a second pass to verify the counts.