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 list of 200 survey responses where people picked "yes," "no," or "maybe," a frequency table shows you that 87 people said yes, 78 said no, and 35 said maybe — all in one organized view instead of scrolling through 200 rows.

You build a frequency table when you want to see patterns at a glance: which product gets the most complaints, what age range shows up most in your customer list, or how many times a particular word appears in your notes. Spreadsheets are the right tool for this because they can count automatically and update if your data changes.

The alternative — counting by hand or using Find to search one value at a time — takes hours and produces mistakes. A frequency table takes minutes and stays accurate.

Key Takeaways

  • A frequency table lists each unique value in your data and counts how many times it appears, making patterns visible at a glance.
  • The simplest method uses COUNTIF in Excel or Google Sheets to count occurrences of each value automatically.
  • You need a list of unique values first — either by typing them manually or using a formula to extract them from your data.
  • Sorting your frequency table by count (highest to lowest) shows you which values matter most in your dataset.
  • Pivot tables are faster for large datasets with many unique values, but COUNTIF works well for smaller lists or when you need more control over the layout.

Set up your data and list unique values

Start with your raw data in one column. Open a new column next to it where you will build your frequency table. In that new column, list each unique value that appears in your data — each answer, category, or response type, but only once.

If your data is small (under 50 rows), you can type the unique values by hand. If your data is larger, use a formula. In Google Sheets, type =UNIQUE(A:A) in a blank cell, replacing A:A with the range of your data. In Excel, there is no UNIQUE function in older versions, so either type the values manually or use the Remove Duplicates feature: select your data, go to Data menu, choose Remove Duplicates, and copy the result into your frequency table column.

Once you have your list of unique values, you are ready to count them.

Use COUNTIF to count each value

Next to your list of unique values, create a "Count" column. In the first cell of that column, type a COUNTIF formula. The formula tells the spreadsheet to count how many times a specific value appears in your original data.

The formula looks like this: =COUNTIF($A$2:$A$201, B2). Replace A2:A201 with the actual range of your data, and replace B2 with the cell containing the unique value you are counting. The dollar signs ($) lock the data range so it does not change when you copy the formula down.

Type the formula in the first count cell, then copy it down to all the other rows. The spreadsheet will automatically adjust the second part of the formula (B2, B3, B4, and so on) while keeping the data range locked. Each row now shows how many times that value appears in your original data.

Sort your frequency table to see what matters most

Once your counts are complete, select both columns (unique values and counts) and sort by the count column from highest to lowest. This puts the most frequent values at the top, making it straightforward to see which answers or categories dominate your data.

In Google Sheets, select your data, go to Data menu, choose Sort range, and pick the count column as your sort key. In Excel, select your data, go to Data menu, choose Sort, and set the count column as the sort column with Z to A (descending) order.

Sorting is optional if you want to keep your values in a specific order (like age ranges from youngest to oldest), but it is usually the clearest way to present a frequency table.

Add a percentage column to show relative frequency

If you want to know not just the count but also what percentage each value represents, add a third column. In the first cell, type =B2/SUM($B$2:$B$10)*100, replacing B2 with your first count cell and B2:B10 with the range of all your counts. This divides each count by the total and multiplies by 100 to show a percentage.

Copy this formula down to all rows. Now you can see that 87 out of 200 responses is 43.5%, which is often more meaningful than the raw count alone. Format these cells as numbers with one or two decimal places so they are straightforward to read.

The percentage column is especially useful when you are comparing datasets of different sizes or presenting your findings to someone else.

Use a pivot table for larger or more complex data

If your dataset has hundreds of rows and dozens of unique values, or if your data is spread across multiple columns, a pivot table is faster than building a frequency table by hand.

In Google Sheets, select your data, go to Insert menu, choose Pivot table, and follow the prompts. Drag the column you want to count into the Rows section and the same column into the Values section. Google Sheets will automatically count occurrences. In Excel, select your data, go to Insert menu, choose Pivot Table, and follow the same steps.

Pivot tables are more powerful than COUNTIF for complex analysis, but they are also less flexible if you want to customize the layout or combine your frequency table with other calculations. For most straightforward frequency counting, COUNTIF is simpler and faster to set up.

Common mistakes and how to fix them

The most common error is forgetting the dollar signs in your COUNTIF formula. Without them, the data range shifts when you copy the formula down, and your counts become wrong. Always use $A$2:$A$201 (with dollar signs) for the range and B2 (without dollar signs) for the value you are counting.

Another mistake is including the header row in your data range. If your data starts in A1 with a header like "Response," start your COUNTIF range at A2 instead. Otherwise, the formula tries to count the word "Response" as a data value.

If your unique values list is incomplete, your frequency table will be incomplete too. Double-check that you have captured every distinct value that appears in your original data. Sorting your original data column alphabetically can help you spot values you missed.

Frequently Asked Questions

What if my data has blank cells or extra spaces?

Blank cells and spaces are treated as separate values, so your frequency table may show a count for "blank" or for values that look identical but have different spacing. Clean your data first: select your column, use Find and Replace to remove extra spaces, and delete any truly blank rows. Then build your frequency table.

Can I make a frequency table from text that is not in a single column?

Yes, but you need to move it to a single column first. If your data is spread across multiple columns or mixed with other information, copy just the values you want to count into a new column by itself. Then build your frequency table from that column.

How do I show a frequency table as a chart?

Once your frequency table is complete, select both columns (values and counts) and insert a bar chart or column chart. The spreadsheet will automatically create a visual showing which values are most frequent. This is often clearer than a table alone, especially for presentations.

What is the difference between frequency and relative frequency?

Frequency is the count — how many times a value appears. Relative frequency is the percentage — what portion of the total that count represents. A frequency table shows both when you add the percentage column described in this guide.

Can COUNTIF count values that match a pattern instead of an exact match?

Yes. Use wildcards: =COUNTIF(A:A, "yes*") counts all cells starting with "yes," and =COUNTIF(A:A, "*error*") counts all cells containing the word "error." This is useful when your data has slight variations in spelling or formatting.