How to Create a Relative Frequency Table in Excel

Key Insights

Relative frequency tables express data as proportions or percentages of the total, making it easy to compare distributions across datasets of different sizes
The formula-based approach using COUNTIF and simple division gives you full control, while PivotTables automate the process for larger datasets
Always verify your relative frequencies sum to 1.0 (or 100%) to catch errors from rounding, blank cells, or misconfigured formulas

Introduction to Relative Frequency Tables

Absolute frequency tells you how many times something occurred. Relative frequency tells you what proportion of the total that represents. This distinction matters more than most analysts realize.

Consider two survey datasets: one with 50 responses, another with 5,000. Saying “15 people chose Option A” means very different things in each context. Relative frequency—30% versus 0.3%—makes the comparison immediate and meaningful.

Relative frequency is calculated by dividing the count of each category by the total number of observations. The result can be expressed as a decimal (0.25), a fraction (1/4), or a percentage (25%). Excel handles all three formats, but percentages dominate in business reporting because they’re intuitive to non-technical stakeholders.

This technique forms the foundation for probability distributions, hypothesis testing, and any analysis where you need to compare “how much” across groups with different sample sizes.

Preparing Your Data

Before building any frequency table, your data needs to be clean and consistently formatted. Excel’s frequency functions are literal—“Yes”, “yes”, and “YES” count as three different values.

Start with your raw data in a single column. Here’s a sample dataset representing customer satisfaction ratings from a survey:

| A              |
|----------------|
| Rating         |
| Excellent      |
| Good           |
| Excellent      |
| Fair           |
| Good           |
| Poor           |
| Good           |
| Excellent      |
| Good           |
| Fair           |
| Excellent      |
| Good           |
| Good           |
| Fair           |
| Excellent      |
| Poor           |
| Good           |
| Excellent      |
| Good           |
| Fair           |

Before proceeding, run these checks:

Remove duplicates in spacing: Use =TRIM(A2) to eliminate leading/trailing spaces
Standardize case: Use =PROPER(A2) or =UPPER(A2) for consistency
Check for blanks: Filter the column and look for empty cells
Identify typos: Sort alphabetically to spot variations like “Excelent” or “Goood”

For numerical data like test scores, decide whether you want frequencies for individual values or grouped ranges (bins). A dataset with scores from 0-100 works better with ranges like 0-59, 60-69, 70-79, 80-89, 90-100.

Creating an Absolute Frequency Table First

Relative frequency requires absolute frequency as its input. You need to know the raw counts before you can calculate proportions.

Set up your frequency table in a separate area of your worksheet. In column D, list each unique category. In column E, you’ll calculate the count for each.

| D         | E                        |
|-----------|--------------------------|
| Category  | Absolute Frequency       |
| Excellent | =COUNTIF($A$2:$A$21,D2)  |
| Good      | =COUNTIF($A$2:$A$21,D3)  |
| Fair      | =COUNTIF($A$2:$A$21,D4)  |
| Poor      | =COUNTIF($A$2:$A$21,D5)  |

The COUNTIF function takes two arguments: the range to search and the criteria to match. The dollar signs create absolute references, so the formula searches the same data range regardless of which row it’s in.

For our sample data, the results would be:

| D         | E                  |
|-----------|--------------------|
| Category  | Absolute Frequency |
| Excellent | 6                  |
| Good      | 8                  |
| Fair      | 4                  |
| Poor      | 2                  |

Add a total row to verify your counts:

=SUM(E2:E5)

This should equal the number of data points in your original dataset (20 in our example). If it doesn’t match, you have either missing categories in your frequency table or data quality issues in your source.

For numerical ranges, use COUNTIFS with multiple criteria:

=COUNTIFS($A$2:$A$101,">="&D2,$A$2:$A$101,"<"&E2)

This counts values greater than or equal to the lower bound AND less than the upper bound of each range.

Calculating Relative Frequencies

With absolute frequencies in place, calculating relative frequency requires one division operation per category.

Add a new column for relative frequency:

| D         | E                  | F                          |
|-----------|--------------------|----------------------------|
| Category  | Absolute Frequency | Relative Frequency         |
| Excellent | 6                  | =E2/SUM($E$2:$E$5)         |
| Good      | 8                  | =E3/SUM($E$2:$E$5)         |
| Fair      | 4                  | =E4/SUM($E$2:$E$5)         |
| Poor      | 2                  | =E5/SUM($E$2:$E$5)         |

The key detail: use absolute references ($E$2:$E$5) for the SUM range so every row divides by the same total. Without the dollar signs, copying the formula down would shift the SUM range and produce incorrect results.

The raw output shows decimals:

| D         | E                  | F                  |
|-----------|--------------------|---------------------|
| Category  | Absolute Frequency | Relative Frequency  |
| Excellent | 6                  | 0.30                |
| Good      | 8                  | 0.40                |
| Fair      | 4                  | 0.20                |
| Poor      | 2                  | 0.10                |
| Total     | 20                 | 1.00                |

To display as percentages, select the relative frequency cells and either:

Press Ctrl+Shift+5 (Windows) or Cmd+Shift+5 (Mac)
Right-click > Format Cells > Percentage
Use the Percentage button in the Number group on the Home ribbon

You can also multiply by 100 directly in the formula if you prefer to store the percentage value rather than format it:

=E2/SUM($E$2:$E$5)*100

Add a total row for relative frequency as well. It must equal 1.0 (or 100%). This serves as your error check.

Using PivotTables for Automated Relative Frequency

For larger datasets or repeated analysis, PivotTables eliminate manual formula writing. Excel calculates both absolute and relative frequencies automatically.

Here’s the step-by-step process:

Select your data range including the header row (A1:A21 in our example)
Insert a PivotTable: Go to Insert > PivotTable. Choose “New Worksheet” or “Existing Worksheet” based on your preference.
Configure the PivotTable fields:
- Drag “Rating” to the Rows area
- Drag “Rating” to the Values area (it will default to “Count of Rating”)
Add relative frequency: Drag “Rating” to Values a second time. You now have two value columns.
Convert the second column to percentages:
- Click on any cell in the second value column
- Right-click > Show Values As > % of Grand Total

Your PivotTable now shows both absolute and relative frequency:

| Row Labels | Count of Rating | Count of Rating2 |
|------------|-----------------|------------------|
| Excellent  | 6               | 30.00%           |
| Fair       | 4               | 20.00%           |
| Good       | 8               | 40.00%           |
| Poor       | 2               | 10.00%           |
| Grand Total| 20              | 100.00%          |

Rename the columns by clicking on “Count of Rating2” and typing “Relative Frequency” in the formula bar.

The PivotTable approach has significant advantages: it updates automatically when source data changes, handles new categories without formula modification, and sorts/filters without breaking calculations.

Visualizing Relative Frequency Data

Numbers in tables communicate precision. Charts communicate patterns. For relative frequency data, bar charts and pie charts are the standard choices.

To create a bar chart from your frequency table:

Select the category labels and relative frequency values (D1:D5 and F1:F5, holding Ctrl to select non-adjacent ranges)
Go to Insert > Charts > Column Chart > Clustered Column
Add data labels: Click the chart > Chart Elements (+) > Data Labels

For a cleaner presentation, format the chart:

Chart Title: "Customer Satisfaction Distribution"
Y-Axis: Format as percentage, range 0% to 50%
Data Labels: Show percentage values on bars
Legend: Remove (unnecessary with single data series)
Gridlines: Remove or lighten for cleaner look

Pie charts work for relative frequency because the slices represent parts of a whole—exactly what relative frequency measures. However, avoid pie charts when you have more than 5-6 categories or when differences between categories are small. Bar charts make small differences easier to perceive.

For PivotTable data, insert a PivotChart directly: click anywhere in the PivotTable, then Insert > PivotChart. The chart stays linked to the PivotTable and updates automatically.

Practical Tips and Common Errors

Rounding issues: Relative frequencies often produce repeating decimals. 1/3 becomes 0.333… which Excel might display as 33.33%. When you have multiple such values, displayed percentages may sum to 99% or 101%. This is a display issue, not a calculation error. Use the ROUND function if you need exact display values:

=ROUND(E2/SUM($E$2:$E$5),2)

Blank cells in source data: COUNTIF ignores blank cells, but they still affect your total count if you’re using COUNTA or manual counts. Decide whether blanks represent missing data (exclude from analysis) or a valid “no response” category (include explicitly).

Mixed data types: If your column contains both numbers and text that look like numbers (1 vs “1”), COUNTIF treats them as different values. Use Data > Text to Columns or VALUE() function to standardize.

Percentage vs. decimal storage: Decide early whether to store relative frequencies as decimals (0.25) or percentages (25). Mixing formats causes calculation errors. My recommendation: store as decimals, format as percentages. This keeps formulas simple and avoids divide-by-100 errors.

Cumulative relative frequency: For ordinal data (like our satisfaction ratings), cumulative relative frequency shows the proportion at or below each category. Add a column with a running sum:

=SUM($F$2:F2)

This is essential for analyzing distributions and calculating percentiles.

Relative frequency tables are foundational. Master this technique and you’ll find it appearing everywhere—from basic descriptive statistics to probability distributions to the chi-square tests you’ll run to determine whether observed frequencies differ significantly from expected ones.