Raw data rarely tells a clear story on its own. Whether you are analyzing exam scores, sales figures, or survey responses, a long list of numbers can be difficult to interpret at a glance. A frequency distribution table solves this problem by organizing data into a structured format that reveals patterns, trends, and the overall shape of a dataset.
What Is a Frequency Distribution Table?
A frequency distribution table is a structured summary that organizes raw data into categories and shows how many times each category occurs. Instead of scanning through hundreds of individual values, a reader can look at the table and immediately understand how the data is distributed.
The table typically consists of two core columns: one listing the distinct values or class intervals, and one recording the frequency — that is, the count of how often each value or interval appears in the dataset. Depending on the level of detail required, additional columns can be added to show relative frequency (the proportion or percentage each category represents) and cumulative frequency (a running total of frequencies from the first category through to the last).
Frequency distribution tables work with both categorical data, such as survey response options, and numerical data, such as test scores or temperatures. When dealing with numerical data, values can either be listed individually — known as an ungrouped distribution — or grouped into intervals called classes, producing a grouped distribution.
When to Use a Frequency Distribution Table
A frequency distribution table is most useful when raw data is too large or disorganized to interpret directly. As a general rule, if you have more than 20 observations and want to communicate patterns clearly, a frequency distribution table is worth constructing.
You are working with a large dataset
When a dataset contains dozens, hundreds, or thousands of values, listing every individual observation is impractical. Grouping the data into a table makes it manageable and immediately readable. A dataset of 500 customer ages, for example, becomes far more informative when condensed into ten class intervals.
You want to identify patterns and distributions
A frequency distribution table makes it easy to see where data concentrates, where it is sparse, and whether the distribution is symmetric or skewed. This is particularly valuable in fields such as education, healthcare, economics, and quality control, where understanding the shape of a distribution informs decisions.
You are preparing data for further analysis
Frequency distribution tables serve as a foundation for constructing histograms, frequency polygons, and ogive curves. They also support calculations of the mean, median, and mode for grouped data, and underlie probability estimates in inferential statistics.
You are comparing two or more datasets
Placing two frequency distributions side by side allows for direct comparison of how different groups or time periods are distributed across the same range of values — for instance, comparing exam score distributions between two classes.
You are presenting findings to a non-technical audience
A well-constructed frequency distribution table communicates complex data clearly and concisely. It requires no specialist knowledge to read, making it one of the most effective tools for presenting statistical summaries in reports, presentations, and publications.
Steps to Create a Frequency Distribution Table
Step 1: Collect and review your raw data
Begin by gathering all observations into a single list. Check for obvious errors, outliers, or missing values before proceeding. For this walkthrough, the following dataset represents the scores of 30 students on a mathematics test:
45, 52, 61, 63, 67, 68, 70, 71, 72, 73, 74, 75, 75, 76, 78, 79, 80, 81, 82, 83, 85, 86, 87, 88, 90, 91, 92, 94, 96, 98
Step 2: Find the range
Subtract the smallest value from the largest to determine the spread of the data.
Range = Maximum − Minimum = 98 − 45 = 53
Step 3: Choose the number of classes
Decide how many class intervals your table will have. Between five and twenty classes is standard, depending on the size of the dataset. A common guideline is Sturges’ Rule:
k = 1 + 3.322 × log₁₀(n)
Where k is the number of classes and n is the number of observations. For 30 data points:
k = 1 + 3.322 × log₁₀(30) = 1 + 3.322 × 1.477 ≈ 6
Six classes is a reasonable choice for this dataset.
Step 4: Calculate the class width
Divide the range by the number of classes, then round up to a convenient whole number.
Class Width = Range ÷ k = 53 ÷ 6 ≈ 8.83 → round up to 9
Rounding up ensures that all values are captured within the intervals. For cleaner presentation, many analysts round to the nearest 5 or 10 — in this case, a class width of 10 produces tidier intervals and is perfectly acceptable.
Step 5: Determine the class limits
Start the first class at a value at or below the minimum observation. Using a starting point of 40 and a class width of 10:
| Class Interval |
|---|
| 40 – 49 |
| 50 – 59 |
| 60 – 69 |
| 70 – 79 |
| 80 – 89 |
| 90 – 99 |
Ensure the intervals are mutually exclusive (no value can fall into two classes) and collectively exhaustive (every value fits into exactly one class).
Step 6: Tally the frequencies
Go through the raw data and count how many observations fall into each class interval.
| Class Interval | Tally | Frequency |
|---|---|---|
| 40 – 49 | | | 1 |
| 50 – 59 | | | 1 |
| 60 – 69 | ||| | 4 |
| 70 – 79 | |||| ||| | 9 |
| 80 – 89 | |||| ||| | 9 |
| 90 – 99 | |||| | | 6 |
| Total | 30 |
Always verify that the frequencies sum to the total number of observations.
Step 7: Add relative and cumulative frequency columns
Relative frequency expresses each class’s count as a proportion of the total. Cumulative frequency is a running total across the classes.
| Class Interval | Frequency | Relative Frequency | Cumulative Frequency |
|---|---|---|---|
| 40 – 49 | 1 | 3.3% | 1 |
| 50 – 59 | 1 | 3.3% | 2 |
| 60 – 69 | 4 | 13.3% | 6 |
| 70 – 79 | 9 | 30.0% | 15 |
| 80 – 89 | 9 | 30.0% | 24 |
| 90 – 99 | 6 | 20.0% | 30 |
| Total | 30 | 100% | — |
The completed table gives a clear picture of the distribution: the majority of students scored between 70 and 89, while very few fell below 60.
How to Create a Frequency Distribution Table in Excel
Excel offers several methods for building a frequency distribution table, ranging from manual entry to built-in functions. The three most practical approaches are covered below, using the same 30-student test score dataset from the previous section.
Method 1: Manual Count Using COUNTIFS
This method gives you full control over your class intervals and is straightforward to audit and update.
Step 1: Enter your raw data
Paste your data into column A, with a header in cell A1 (e.g., Score) and values in A2:A31.
Step 2: Set up your class interval table
In columns C and D, enter the lower and upper bounds of each class interval:
| C | D | |
|---|---|---|
| 1 | Lower | Upper |
| 2 | 40 | 49 |
| 3 | 50 | 59 |
| 4 | 60 | 69 |
| 5 | 70 | 79 |
| 6 | 80 | 89 |
| 7 | 90 | 99 |
Step 3: Enter the COUNTIFS formula
In cell E2, enter the following formula and drag it down through E7:
=COUNTIFS($A$2:$A$31,">="&C2,$A$2:$A$31,"<="&D2)This counts every value in column A that falls within the lower and upper bounds of each row. The dollar signs lock the data range so it does not shift when you drag the formula down.
Step 4: Add relative and cumulative frequency columns
In F2, calculate relative frequency:
=E2/SUM($E$2:$E$7)Format column F as a percentage. In G2, enter the cumulative frequency:
=E2In G3 and below:
=G2+E3Method 2: Using the FREQUENCY Array Function
Excel’s built-in FREQUENCY function calculates frequencies automatically against a set of upper-boundary values called bins.
Step 1: Define your bin values
Enter the upper boundary of each class interval in column C:
| C | |
|---|---|
| 1 | Bin |
| 2 | 49 |
| 3 | 59 |
| 4 | 69 |
| 5 | 79 |
| 6 | 89 |
| 7 | 99 |
Step 2: Enter the FREQUENCY formula
Select the range D2:D7 (one cell per bin), then type:
=FREQUENCY(A2:A31,C2:C7)In older versions of Excel, press Ctrl + Shift + Enter to confirm this as an array formula. In Excel 365 and Excel 2021, pressing Enter alone is sufficient, as these versions handle dynamic arrays natively.
Excel will populate all six frequency values simultaneously. Note that FREQUENCY assigns each value to the interval whose bin boundary it does not exceed, so a bin of 49 captures all values from the start of your data up to and including 49.
Method 3: Using a PivotTable
PivotTables offer the fastest route to a frequency distribution and require no formulas.
Step 1: Format your data as a table
Click anywhere in your data range and press Ctrl + T to convert it to an Excel table.
Step 2: Insert a PivotTable
Go to Insert → PivotTable and place it in a new worksheet.
Step 3: Configure the PivotTable fields
Drag the Score field into both the Rows area and the Values area. In the Values area, ensure the field is set to Count rather than Sum.
Step 4: Group the row values
Right-click any value in the Row Labels column and select Group. In the dialog box, set:
- Starting at: 40
- Ending at: 99
- By: 10
Excel will immediately group the scores into class intervals of width 10 and display the count for each group — producing a complete frequency distribution table in seconds.
Choosing the Right Method
| Method | Best For |
|---|---|
| COUNTIFS | Custom or unequal class intervals |
| FREQUENCY | Quick array-based calculation with uniform bins |
| PivotTable | Large datasets and exploratory analysis |
For most classroom and professional use cases, COUNTIFS offers the best balance of transparency and flexibility. PivotTables are the preferred choice when working with large datasets that may require frequent updates or regrouping.
Advantages and Limitations of Frequency Distribution Tables
