How to count value frequency in Excel
Before you analyse a column, you want to know what's in it — how often each value or range appears. Excel counts frequency with COUNTIF, FREQUENCY and histograms. Here's how, and the one-click alternative.
When to count frequency first
Knowing what's in a column — and how common each value is — comes before any deeper analysis.
Know your data
What categories exist and how common each is, before deeper analysis.
Spot the shape
Is it bunched, spread, or skewed? Frequency reveals it.
Find errors
Odd or rare values often mean typos or bad records.
How to do it in Excel
Categories suit COUNTIF or a PivotTable; numbers suit FREQUENCY or the built-in histogram.
Count one value
=COUNTIF(A:A, "North") counts exact matches; use wildcards like "North*" for partials.
Count every category
List the unique values, then COUNTIF each — or use a PivotTable with Count for speed.
Bin numbers with FREQUENCY
Define bin edges, select the output cells and enter =FREQUENCY(data, bins) as an array.
Chart it
Plot the counts as a column chart, or use Insert › Statistic Chart › Histogram to bin automatically.
For categories, a PivotTable set to Count is usually faster than a wall of COUNTIFs. For numbers, the choice of bin width changes the whole story — too few bins hide the shape, too many make it noisy. Start with the built-in histogram's automatic bins, then adjust the bin width until the distribution reads clearly.
The faster way — skip the setup
Several tools answer one question, and array formulas trip people up. DataHub Pro reads the distribution at a glance.
Several tools, one question
- COUNTIF per value or a PivotTable
- FREQUENCY needs an array formula
- Pick bin widths by trial and error
- Rebuild on new data
Distribution at a glance
- Upload the column — value counts computed
- Sensible bins chosen automatically
- Rare values and likely typos flagged
- Straight into a dashboard or report
FAQ
How do I count how many times a value appears in Excel?
Use =COUNTIF(range, value) — for example =COUNTIF(A:A, "North"). For partial matches use wildcards like "North*", and for multiple conditions use COUNTIFS.
What is the FREQUENCY function in Excel?
FREQUENCY counts how many values fall into each of a set of bins you define. You select the output range, enter =FREQUENCY(data_array, bins_array), and in older Excel confirm it as an array formula with Ctrl+Shift+Enter.
How do I make a frequency distribution or histogram?
Either count each bin with FREQUENCY and chart the result as columns, or select your data and use Insert › Statistic Chart › Histogram, which bins the values automatically. Adjust the bin width until the shape is clear.
Keep exploring
More visual analysis you can run on your own data — no sign-up needed to read.
Know your data before you trust it
Upload a column and DataHub Pro counts every value, bins the numbers and flags the oddities for you. Free to try, no card.
Start free →