A frequency distribution counts how many times each value appears in your data
A frequency distribution is a table that shows each unique value in your data and how often it appears. If you have a column of 200 customer ages, a frequency distribution tells you how many customers are 25, how many are 26, how many are 27, and so on. Excel does not build this automatically — you create it by counting occurrences yourself, using either a manual method or a formula.
The simplest approach depends on your data size and whether the values repeat in a predictable way. For small datasets (under 50 rows), you can count by hand or use a filter. For larger datasets, a COUNTIF formula does the counting for you in seconds. A third option, pivot tables, automates the entire process but requires a separate step.
Key Takeaways
- A frequency distribution is a two-column table: one column lists each unique value, the other counts how many times it appears.
- The COUNTIF formula counts occurrences automatically: type =COUNTIF($A$2:$A$201,B2) in a cell, then copy it down to count each value.
- For data with many unique values, a pivot table builds the distribution in one step without writing formulas.
- Sort your frequency distribution by count (highest to lowest) to see which values are most common at a glance.
Set up your frequency distribution table with two columns
Start by creating a blank two-column table next to your original data or on a new sheet. Label the left column "Value" and the right column "Frequency". In the left column, list every unique value that appears in your data — if your data is ages 22 through 67, you would list 22, 23, 24, and so on down to 67.
If you are not sure which values exist in your data, sort your original column first. Open the Data menu, click Sort, and choose your column. Sorting shows you the range of values and makes it easier to spot which ones are actually present. Then copy the unique values into your left column. If your data has only 10 or 15 unique values, this takes two minutes. If it has 200 unique values, use the Remove Duplicates feature instead: select your data, go to Data > Remove Duplicates, and paste the result into your left column.
Use COUNTIF to count how many times each value appears
In the first cell of your Frequency column (usually B2), type this formula: =COUNTIF($A$2:$A$201,B2). Replace A2:A201 with the actual range of your data. The dollar signs ($) lock the range so it does not change when you copy the formula down. B2 (without dollar signs) is the value you are counting — it will change to B3, B4, and so on as you copy.
Press Enter. The formula counts how many times the value in B2 appears anywhere in your data range and shows the result. Now select that cell again and copy the formula down to every row in your frequency table. Click the cell, then drag the small square at the bottom-right corner down to the last row, or double-click that square to auto-fill. Each row now shows how many times that value appears in your original data.
If a value appears zero times, COUNTIF will show 0 — that is correct. You can delete those rows if you want only values that actually exist in your data, or keep them if you want to see the complete range.
Use a pivot table to build the distribution automatically
A pivot table does all the counting work for you without formulas. Select your original data (including the header row), then go to the Insert menu and click Pivot Table. Excel opens a dialog asking where you want the pivot table to appear — choose a new sheet or a blank area of your current sheet.
In the Pivot Table Fields panel on the right, drag your data column (for example, "Age") into both the Rows area and the Values area. The Rows area lists each unique value; the Values area counts them. Excel automatically builds your frequency distribution. The Values area defaults to Sum, but since you are counting text or categories, it usually switches to Count automatically. If it shows Sum instead, click the field in the Values area and change it to Count.
Pivot tables are faster than formulas if you have a large dataset or if your data changes often — you can refresh the pivot table in one click and it recounts everything. They are also easier if you are not comfortable with formulas. The trade-off is that pivot tables live on their own sheet and take up more space than a straightforward two-column table.
Sort your frequency distribution to see patterns
Once your frequency column is complete, sort the table by frequency (highest to lowest) to see which values are most common. Select both your Value and Frequency columns, go to the Data menu, and click Sort. Choose to sort by the Frequency column in descending order. Now the most frequent values appear at the top.
Sorting makes patterns visible when ready. If you are analyzing customer purchase amounts, you might see that most customers spend between $50 and $100, with very few spending over $500. If you are tracking error codes, you might see that one error code accounts for half of all failures. This view is much harder to spot in an unsorted list.
Add a percentage column to show relative frequency
To see what fraction of your data each value represents, add a third column called "Percentage". In the first cell of this column, type =C2/SUM($C$2:$C$201)*100 (replace C2:C201 with your actual frequency range). This divides each count by the total count and multiplies by 100 to show a percentage.
Copy this formula down to every row. Now you can see not just how many times each value appears, but what percentage of your total data it represents. If you have 1,000 rows of data and one value appears 250 times, the percentage column shows 25%. This is especially useful when comparing datasets of different sizes — percentages make the comparison fair.
Check your work by summing the frequency column
The sum of your entire Frequency column should equal the number of rows in your original data. If your data has 500 rows and your frequencies add up to 500, you counted correctly. If they add up to 480 or 520, you either missed some values or counted something twice.
To check quickly, click an empty cell below your Frequency column and type =SUM(C2:C201) (use your actual range). If the result matches your original row count, you are done. If not, look for values you missed in your Value column, or check that your COUNTIF formula is pointing to the right range.
Frequently Asked Questions
What if my data has text values instead of numbers?
COUNTIF works exactly the same way with text. If your column contains city names or product categories, list the unique text values in your left column and use the same COUNTIF formula. Excel counts text matches just as it counts numbers.
Can I create a frequency distribution for data in multiple columns?
Yes, but you build a separate frequency distribution for each column. Select one column at a time, create its frequency table, then repeat for the next column. If you want to count combinations (for example, how many customers are age 30 AND live in Texas), use a pivot table with both columns in the Rows area instead.
What is the difference between frequency and cumulative frequency?
Frequency shows how many times each value appears. Cumulative frequency adds up all the frequencies from the top down — so if value 1 appears 10 times and value 2 appears 15 times, the cumulative frequency for value 2 is 25. Add a fourth column with the formula =SUM($C$2:C2) and copy it down to see cumulative totals.
Do I have to list every possible value, even if it does not appear in my data?
No. If your data contains only ages 22, 25, 28, and 31, you can list only those four values. COUNTIF will show 0 for any value that does not exist. Listing every possible value (22 through 67) is useful only if you want to see the complete range and identify which values are missing.
Can I make a frequency distribution chart instead of a table?
Yes. Once your frequency table is complete, select both columns and insert a bar chart or column chart. Excel plots each value on the horizontal axis and its frequency on the vertical axis. This visual format makes patterns even easier to spot than a table.