What frequency means in Excel and why you might need it

Frequency in Excel means counting how many times a value appears in a list. If you have a column of test scores, frequency tells you how many students scored 85, how many scored 90, and so on. You use it when you need to see patterns in data — which sales amounts come up most often, which customer complaint types repeat, which age groups show up in your survey responses.

Excel gives you several ways to count repeated values, ranging from a single formula you can type in seconds to a built-in tool that creates a full distribution table. Which one you use depends on whether you want a quick answer or a detailed breakdown.

Key Takeaways

  • COUNTIF is the fastest way to count how many times one specific value appears in a column or range.
  • The FREQUENCY function creates a table showing how many values fall into each range you define (like how many scores fall between 70–79, 80–89, and so on).
  • A pivot table lets you count repeated values and group them by category without writing any formulas.
  • Histogram charts in Excel 2016 and later can automatically bin your data and show frequency visually.

Using COUNTIF to count one specific value

COUNTIF is the simplest approach when you want to know how many times a single value appears. The formula is =COUNTIF(range, criteria). In plain terms: look at this range of cells, and count how many match this value.

Say you have customer feedback in column A (rows 1 through 100), and you want to know how many people wrote "Excellent". Click an empty cell and type =COUNTIF(A1:A100,"Excellent"). Excel returns the count. If you want to count blank cells instead, use =COUNTIF(A1:A100,""). If you want to count cells that contain a word but aren't exactly that word — for instance, any feedback that includes the word "fast" — use =COUNTIF(A1:A100,"*fast*").

You can also reference another cell instead of typing the value directly. If the word you're searching for is in cell C1, write =COUNTIF(A1:A100,C1). This way you can change what you're counting without editing the formula.

Using FREQUENCY to create a distribution table

The FREQUENCY function is different from COUNTIF. Instead of counting one value, it counts how many values fall into each range or "bin" you define. This is useful when you have continuous data like test scores, ages, or sales amounts, and you want to see the shape of the distribution.

The formula is =FREQUENCY(data_array, bins_array). Your data array is the column of numbers you're analyzing. Your bins array is a separate column listing the upper limit of each range. If your test scores are in A1:A50 and you want to count how many fall into 0–69, 70–79, 80–89, and 90–100, you would put 69, 79, 89, and 100 in cells C1:C4. Then select a range (D1:D4) where you want the results to appear, type the formula =FREQUENCY(A1:A50,C1:C4), and press Ctrl+Shift+Enter (not just Enter). Excel fills all four cells with the counts.

One important detail: FREQUENCY counts values that are less than or equal to each bin limit. So a score of exactly 79 goes into the 70–79 bin, and a score of 80 goes into the 80–89 bin. The last bin should be higher than any value in your data so nothing gets left out.

Building a frequency table by hand with COUNTIF

If the FREQUENCY function feels confusing, you can build the same table manually using COUNTIF, which gives you more control and is easier to understand. Create two columns: one for the range labels (like "70–79", "80–89") and one for the counts.

In the count column, use COUNTIFS (the plural version) to count values that fall within a range. The formula =COUNTIFS(A1:A50,">=70",A1:A50,"<=79") counts all scores in A1:A50 that are greater than or equal to 70 AND less than or equal to 79. Copy this formula down, changing the numbers for each row. This method takes a few more clicks but is clearer to read and easier to adjust later.

Using a pivot table to count and group values

A pivot table is Excel's tool for reorganizing and summarizing data without formulas. If you want to count how many times each unique value appears — for instance, how many orders came from each state, or how many complaints fall into each category — a pivot table does this in seconds.

Select your data (including headers), go to the Insert tab, and click Pivot Table. Choose where you want the table to appear. In the pivot table field list on the right, drag the column you want to count into the Rows area and also into the Values area. Excel automatically counts each unique value. You can then sort by count to see which values appear most often.

Pivot tables are especially useful when you have multiple columns and want to count by more than one category at once — for example, counting complaints by type and by month together.

Creating a histogram to visualize frequency

If you want to see frequency as a chart rather than numbers, Excel 2016 and later have a built-in histogram. Select your data column, go to the Insert tab, click the Charts dropdown, and look for Histogram. Excel automatically bins your data into ranges and draws bars showing how many values fall into each bin. You can adjust the bin size by right-clicking the horizontal axis and choosing Format Axis, then changing the Bin Width.

A histogram is useful when you're presenting data to others or when you want to spot patterns visually — for instance, whether your data clusters around one value or spreads evenly across a range.

Frequently Asked Questions

What's the difference between COUNTIF and FREQUENCY?

COUNTIF counts how many times one specific value appears. FREQUENCY counts how many values fall into each range you define. Use COUNTIF for categories (like counting "Excellent" reviews) and FREQUENCY for continuous data like scores or ages.

Why do I have to press Ctrl+Shift+Enter for FREQUENCY instead of just Enter?

FREQUENCY is an array formula, meaning it fills multiple cells at once with one formula. Ctrl+Shift+Enter tells Excel to treat it as an array formula. If you press just Enter, it only fills the first cell. Newer versions of Excel sometimes handle this automatically, but Ctrl+Shift+Enter always works.

Can I count values that meet more than one condition?

Yes, use COUNTIFS (plural). For example, =COUNTIFS(A1:A100,">=80",A1:A100,"<=90") counts values between 80 and 90. You can add more conditions by adding more range-criteria pairs.

What if I want to count unique values only, not how many times each appears?

Use =SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100)) to count how many different values exist in the range. This counts each unique value once, regardless of how many times it appears.