What a frequency table does and when you need one
A frequency table counts how many times each value appears in your data. If you have a column of survey responses, sales regions, or test scores, a frequency table shows you the pattern at a glance — how many people chose each answer, which region appears most often, or how many students scored in each range. Excel does not build this automatically, but the process takes about five minutes once you know the steps.
You need a frequency table when you want to see the shape of your data rather than the raw list. A spreadsheet with 200 customer responses is hard to read; a frequency table showing that 87 people chose "yes," 56 chose "no," and 57 chose "maybe" tells you what actually happened. The same applies to numerical data — if you have 300 test scores, grouping them into ranges (60–70, 71–80, 81–90) and counting how many fall into each range shows you the distribution without forcing you to scan the whole column.
Key Takeaways
- A frequency table lists each unique value in your data and counts how many times it appears, making patterns visible in large datasets.
- The simplest method uses the COUNTIF function to count occurrences of each value, which works best when your data has a limited number of distinct entries.
- For numerical data with many values, group the numbers into ranges first, then use COUNTIF to count how many entries fall into each range.
- Excel's Pivot Table feature can build a frequency table automatically if you prefer not to write formulas, though it requires an extra step to set up.
Set up your data and list unique values
Start with your raw data in one column. For this example, assume you have customer feedback in column A, rows 1 through 50, with responses like "Good," "Poor," "Excellent," and "Fair" repeated throughout. The first step is to create a list of each unique value — each distinct response that appears in your data.
In a blank area to the right (say, column C), type each unique value once. If your data contains "Good," "Poor," "Excellent," and "Fair," type those four words in cells C1 through C4. Do not include duplicates — the point is to list each distinct entry only once. If you are unsure what values appear in your data, scroll through the column and write down what you see, or use the filter feature to see all unique entries at once.
Next to your unique values, in column D, you will add the count. Type a header in D1 if you want — something like "Count" or "Frequency" — then move to D2 to start entering the formula.
Use COUNTIF to count occurrences of each value
The COUNTIF function counts how many cells in a range match a specific value. The formula structure is =COUNTIF(range, criteria). In your case, the range is your original data column (A1:A50), and the criteria is the unique value in the current row.
In cell D2, type =COUNTIF($A$1:$A$50,C2). The dollar signs lock the range so it does not change when you copy the formula down, but C2 will shift to C3, C4, and so on as you copy. Press Enter. Excel will return the number of times the value in C2 appears in your data.
Now copy this formula down to the other cells. Click D2, then drag the small square at the bottom-right corner of the cell down to D5 (or however many unique values you have). Excel will automatically adjust the formula for each row. You now have a complete frequency table showing each unique value and how many times it appears.
Create a frequency table for numerical ranges
If your data is numerical and spread across a wide range — for example, 300 test scores from 45 to 98 — listing every single score as a unique value creates a cluttered table. Instead, group the numbers into ranges, then count how many scores fall into each range.
Decide on your ranges first. For test scores, you might use 40–50, 51–60, 61–70, 71–80, 81–90, and 91–100. In column C, type a label for each range: "40–50" in C1, "51–60" in C2, and so on. In column D, you will count how many scores fall into each range.
The formula for ranges uses COUNTIFS (note the S at the end), which counts cells that meet multiple criteria. In D1, type =COUNTIFS($A$1:$A$300,">=40",$A$1:$A$300,"<=50"). This counts all scores in column A that are greater than or equal to 40 AND less than or equal to 50. Adjust the numbers for each range as you go down. Copy the formula to the cells below, changing the range limits for each row.
Use a Pivot Table for automatic frequency counting
If writing formulas feels unfamiliar, Excel's Pivot Table feature can build a frequency table without any formula work. A Pivot Table reorganizes your data into a summary automatically, and setting one up for frequency counting takes about three steps.
First, select your data column (including the header). Click the Insert tab at the top, then click Pivot Table. A dialog box will open asking where you want the table to go — choose a new worksheet or a blank area of your current sheet. Click OK. Excel will create a blank Pivot Table frame with a list of fields on the right side.
Drag the field name (your column header) from the field list into the Rows area and also into the Values area. Excel will automatically count how many times each value appears. The result is a frequency table identical to what COUNTIF would produce, but without writing any formula. If you want to sort by frequency (highest count first), click the count column and use the sort buttons on the Data tab.
Check your work and interpret the results
Once your frequency table is complete, scan it for obvious errors. The total of all counts should equal the number of rows in your original data. If you started with 50 customer responses, your frequency counts should add up to 50. If they do not, you may have missed a unique value or included a blank cell by accident.
Look at the pattern. Which value appears most often? Which appears least? Are the counts evenly distributed, or is one value dominant? This is the insight a frequency table provides — it shows you what your data actually contains, which is much harder to see in a raw list. If you are presenting this to someone else, consider adding a straightforward bar chart next to the table. Select the frequency table and insert a chart; Excel will create a visual representation that makes the pattern even clearer.
Frequently Asked Questions
What if my data has blank cells?
COUNTIF will count blank cells as a value if you include them in your unique list. If you want to exclude blanks, straightforward do not type a blank cell as one of your unique values. If you want to count how many blanks exist, type a blank cell in your unique list (leave it empty) and COUNTIF will count those rows.
Can I use a frequency table with text that has different capitalization?
COUNTIF treats "Good," "good," and "GOOD" as different values, so your frequency table will split them into separate rows. If your data is inconsistent, clean it first by using Find and Replace to standardize capitalization, or accept that the counts will be split across similar entries.
How do I update the frequency table if my data changes?
If you used COUNTIF formulas, they update automatically whenever the data in your original column changes. If you used a Pivot Table, right-click it and select Refresh to recalculate the counts. If you add new unique values to your data, you will need to add them to your unique value list manually before the count will appear.
What is the difference between COUNTIF and COUNTIFS?
COUNTIF counts cells that match one condition. COUNTIFS counts cells that match multiple conditions at the same time. Use COUNTIF for straightforward lists of values; use COUNTIFS when you need to count numbers within a range, like scores between 70 and 80.
Can I make a frequency table from data in multiple columns?
Yes, but you will need to combine the columns first or create separate frequency tables for each column. If you want a single frequency table from multiple columns, copy all the data into one column, then follow the steps above. If the columns represent different things, it usually makes more sense to create a separate frequency table for each one.