Creating a frequency table in Excel means sorting your data into groups and counting how many times each value appears

A frequency table organizes raw data by listing each unique value and how often it occurs. In Excel, you build one by entering your data, identifying the unique values, and using a formula to count occurrences. The simplest method uses the COUNTIF function, which searches your data range and returns a count for each value you specify.

The process takes about five minutes for a small dataset. Larger datasets benefit from a pivot table instead, which Excel builds automatically. Both methods produce the same result — a clear summary that shows patterns in your data at a glance.

Key Takeaways

  • A frequency table lists each unique value in your dataset alongside a count of how many times it appears.
  • The COUNTIF formula method works well for small datasets and gives you full control over the layout and appearance.
  • Pivot tables are faster for large datasets and automatically handle the counting without manual formulas.
  • You can sort your frequency table by count or by value to spot the most common items or arrange them in order.
  • Excel's UNIQUE function (available in Excel 365) can extract all distinct values automatically, saving manual typing.

The COUNTIF method for small to medium datasets

Start by opening a new column next to your data. In the first cell of that column, type the header "Frequency" or "Count". Below it, enter the formula =COUNTIF($A$2:$A$100,A2), replacing A2:A100 with your actual data range. The dollar signs lock the range so it does not change when you copy the formula down.

The formula looks at every cell in your range and counts how many match the value in A2. When you copy this formula down to the next row, it automatically adjusts to count matches for A3, then A4, and so on. If your data has 50 rows, copy the formula down 50 times. Each row will show how many times that value appears in the entire dataset.

Remove duplicate rows afterward so each unique value appears only once. Select your data range, go to the Data tab, and click Remove Duplicates. Excel will delete every repeated row, leaving you with one entry per unique value and its count beside it.

Using pivot tables for larger datasets

A pivot table counts frequencies automatically without formulas. Select your data range including headers, then go to the Insert tab and click Pivot Table. Excel opens a dialog asking where you want the table to appear — choose a new worksheet to keep it separate from your raw data.

In the pivot table builder panel on the right, drag your data column into the Rows area and also into the Values area. Excel automatically counts occurrences and displays them as a frequency table. The Values area defaults to counting, which is exactly what you need. You can then sort this table by frequency (highest to lowest) or alphabetically by dragging the column header.

Pivot tables update automatically if you change the original data. If you add new rows to your source data, right-click the pivot table and select Refresh to include the new values in your frequency count.

Extracting unique values first with Excel 365

If you have Excel 365, the UNIQUE function eliminates the need to manually type or remove duplicates. In a blank column, enter =UNIQUE(A2:A100) where A2:A100 is your data range. Excel instantly lists every distinct value from your data, one per row, in the order they first appear.

Next to this list, use COUNTIF to count each unique value. In the adjacent column, type =COUNTIF($A$2:$A$100,B2) where B2 is your first unique value. Copy this formula down alongside your unique list. You now have a complete frequency table without manually removing duplicates or typing values yourself.

This method is fastest for datasets with hundreds or thousands of rows. The UNIQUE function handles the extraction, and COUNTIF handles the counting, leaving you only to copy formulas down.

Sorting your frequency table to find patterns

Once your frequency table is built, sort it to answer different questions about your data. To see the most common values first, select your frequency table (both the value column and the count column), go to the Data tab, and click Sort. Choose to sort by the Count column in descending order. The highest frequencies appear at the top.

To arrange values alphabetically or numerically instead, sort by the value column in ascending order. This layout helps when you need to locate a specific item and its frequency quickly. You can also sort by count in ascending order to see which values are rarest in your dataset.

Always select both columns together when sorting. If you sort only the count column, the values will no longer match their counts, and your table becomes useless.

Adding a percentage column to show relative frequency

A frequency table becomes more useful when you add a column showing what percentage each count represents. Next to your Count column, add a header "Percentage". In the first data cell below it, enter =B2/SUM($B$2:$B$100)*100, where B2 is your first count and B2:B100 is your entire count range.

This formula divides each count by the total of all counts, then multiplies by 100 to convert to a percentage. Copy it down for every row. Now you can see at a glance that one value might represent 25% of your dataset while another represents only 3%.

Format this column as a number with one or two decimal places for readability. Select the percentage column, right-click, choose Format Cells, and set the decimal places. Round percentages make the table easier to read in reports or presentations.

Handling text and numeric data differently

COUNTIF works the same way for both text and numbers, but the results depend on how your data is stored. If your column contains numbers stored as text (common when data is imported), COUNTIF may not count them correctly. To check, click a cell in your data column — if it aligns left instead of right, it is stored as text.

Convert text numbers to actual numbers by selecting the column, going to the Data tab, and clicking Text to Columns. Click Finish without changing settings. Excel converts the column to numeric format, and your COUNTIF formulas will then count accurately.

For text data like names or categories, COUNTIF is case-insensitive, meaning "apple", "Apple", and "APPLE" all count as the same value. If you need to treat different cases separately, use SUMPRODUCT with EXACT instead: =SUMPRODUCT(--EXACT(A2:A100,B2)).

Frequently Asked Questions

What is the difference between COUNTIF and a pivot table?

COUNTIF is a formula you write yourself, giving you control over layout and formatting. A pivot table is built automatically by Excel and updates when your data changes. For datasets under 1,000 rows, either works. For larger datasets, pivot tables are faster and require less manual work.

Can I create a frequency table for data in multiple columns?

Yes, but build a separate frequency table for each column. Select one column of data, follow the steps above, and repeat for the next column in a different area of your worksheet. If you need to count combinations (like how often "red" appears with "large"), use a pivot table with multiple fields instead.

How do I handle blank cells in my frequency table?

COUNTIF counts blank cells as a value if you include them in your range. If blanks are errors or missing data you want to exclude, use =COUNTIF(A2:A100,A2) without the blank cells in your range, or filter them out before building the table. Alternatively, sort your table and delete the blank row after counting.

What if my data has decimal numbers or very similar values?

COUNTIF matches exact values, so 10.5 and 10.50 count as the same, but 10.5 and 10.51 count separately. If rounding errors create false duplicates, round your data first using =ROUND(A2,1) to round to one decimal place, then build your frequency table from the rounded values.

Can I update my frequency table automatically when new data is added?

Pivot tables update when you right-click and select Refresh. For COUNTIF formulas, use a dynamic range like =COUNTIF(A:A,B2) which counts the entire column A, so new rows are included automatically. Avoid specifying a fixed range like A2:A100 if your data grows regularly.