How to Create a Cumulative Frequency Table in Excel
Cumulative frequency answers a simple but powerful question: how many observations fall at or below a given value? While a standard frequency table tells you how many data points exist in each...
Key Insights
- Cumulative frequency tables reveal how data accumulates across intervals, making it easy to answer questions like “what percentage of students scored below 80?” with a single lookup.
- Excel’s FREQUENCY function handles bin counting efficiently, but understanding COUNTIF logic gives you more flexibility for non-standard intervals and troubleshooting.
- The ogive (cumulative frequency graph) transforms your table into a visual tool for identifying medians, quartiles, and distribution shape at a glance.
Introduction to Cumulative Frequency
Cumulative frequency answers a simple but powerful question: how many observations fall at or below a given value? While a standard frequency table tells you how many data points exist in each category, cumulative frequency shows the running total as you move through your data range.
This matters in practical analysis. When a professor wants to know what percentage of students passed an exam, when a sales manager needs to identify the revenue threshold that captures 80% of transactions, or when a quality engineer must determine how many products fall within acceptable tolerances—cumulative frequency provides the answer directly.
The technique appears constantly in statistical work: constructing percentiles, creating ogive charts, performing goodness-of-fit tests, and summarizing large datasets into digestible distributions. Excel handles these calculations efficiently once you understand the underlying logic.
Preparing Your Data
Before building any frequency table, your raw data needs structure. Start with a single column containing your observations—no headers mixed with data, no blank cells in the middle, no text values hiding among numbers.
Here’s a sample dataset of 50 exam scores we’ll use throughout this tutorial:
Score Data (A1:A51, with header in A1)
A1: Score
A2: 67
A3: 72
A4: 85
A5: 91
A6: 58
A7: 73
A8: 69
A9: 82
A10: 77
A11: 64
A12: 88
A13: 95
A14: 71
A15: 66
A16: 79
A17: 83
A18: 92
A19: 61
A20: 74
A21: 87
A22: 70
A23: 76
A24: 81
A25: 68
A26: 93
A27: 75
A28: 84
A29: 62
A30: 78
A31: 89
A32: 65
A33: 73
A34: 86
A35: 71
A36: 80
A37: 94
A38: 63
A39: 77
A40: 82
A41: 69
A42: 90
A43: 74
A44: 85
A45: 67
A46: 79
A47: 88
A48: 72
A49: 81
A50: 76
A51: 70
Next, determine your class intervals (bins). For this dataset ranging from 58 to 95, I’ll use intervals of 10: 50-59, 60-69, 70-79, 80-89, and 90-100. The choice of bin width involves tradeoffs—too few bins obscure patterns, too many create noise. A common heuristic suggests using between 5 and 20 bins, with the square root of your sample size as a starting point.
Set up your frequency table structure in columns C through G:
C1: Class Interval
D1: Frequency
E1: Cumulative Frequency
F1: Relative Frequency
G1: Cumulative Relative Frequency
C2: 50-59
C3: 60-69
C4: 70-79
C5: 80-89
C6: 90-100
Creating a Basic Frequency Table
Two approaches work well for counting frequencies in Excel: COUNTIF combinations and the FREQUENCY array function.
Method 1: COUNTIF Approach
This method counts values between boundaries by subtracting overlapping ranges:
# For the 50-59 interval (D2):
=COUNTIF($A$2:$A$51,">=50")-COUNTIF($A$2:$A$51,">=60")
# For the 60-69 interval (D3):
=COUNTIF($A$2:$A$51,">=60")-COUNTIF($A$2:$A$51,">=70")
# For the 70-79 interval (D4):
=COUNTIF($A$2:$A$51,">=70")-COUNTIF($A$2:$A$51,">=80")
# For the 80-89 interval (D5):
=COUNTIF($A$2:$A$51,">=80")-COUNTIF($A$2:$A$51,">=90")
# For the 90-100 interval (D6):
=COUNTIF($A$2:$A$51,">=90")-COUNTIF($A$2:$A$51,">100")
The logic is straightforward: count everything at or above the lower bound, then subtract everything at or above the next interval’s lower bound. What remains is the count within your target interval.
Method 2: FREQUENCY Array Function
The FREQUENCY function handles this more elegantly but requires understanding array formulas. First, create a helper column with your bin upper limits:
H1: Bin Upper Limit
H2: 59
H3: 69
H4: 79
H5: 89
H6: 100
In modern Excel (365/2021), enter this formula in D2 and it will spill automatically:
=FREQUENCY(A2:A51,H2:H6)
In older Excel versions, select D2:D6, type the formula, and press Ctrl+Shift+Enter to enter it as an array formula.
For our sample data, the frequency column should show:
D2: 1 (scores 50-59)
D3: 9 (scores 60-69)
D4: 18 (scores 70-79)
D5: 15 (scores 80-89)
D6: 7 (scores 90-100)
Verify your work: these should sum to 50, matching your total observations.
Calculating Cumulative Frequency
The cumulative frequency column shows running totals. Each cell contains the sum of all frequencies up to and including that row.
Method 1: Running SUM Formula
This approach uses an expanding range with a fixed start point:
# In E2 (first cumulative frequency):
=SUM($D$2:D2)
# Copy this formula down to E3:E6
# Excel adjusts the end reference while keeping the start fixed
The result:
E2: 1 (1 score at or below 59)
E3: 10 (10 scores at or below 69)
E4: 28 (28 scores at or below 79)
E5: 43 (43 scores at or below 89)
E6: 50 (all 50 scores at or below 100)
Method 2: Simple Addition
Alternatively, add each frequency to the previous cumulative total:
# In E2:
=D2
# In E3:
=E2+D3
# Copy E3 down to E4:E6
Both methods produce identical results. The SUM approach handles inserted or deleted rows more gracefully; the addition approach is easier to audit visually.
Calculating Relative and Cumulative Relative Frequency
Raw counts become more meaningful when expressed as proportions or percentages. Relative frequency shows what fraction of total observations falls in each interval.
Relative Frequency (Column F):
# In F2:
=D2/SUM($D$2:$D$6)
# Copy down to F3:F6
Or, if you know your total is 50:
=D2/50
The results as decimals:
F2: 0.02 (2% of scores in 50-59)
F3: 0.18 (18% of scores in 60-69)
F4: 0.36 (36% of scores in 70-79)
F5: 0.30 (30% of scores in 80-89)
F6: 0.14 (14% of scores in 90-100)
Format these cells as percentages for clearer presentation.
Cumulative Relative Frequency (Column G):
Apply the same running total logic to relative frequencies:
# In G2:
=SUM($F$2:F2)
# Copy down to G3:G6
Results:
G2: 0.02 (2% scored 59 or below)
G3: 0.20 (20% scored 69 or below)
G4: 0.56 (56% scored 79 or below)
G5: 0.86 (86% scored 89 or below)
G6: 1.00 (100% scored 100 or below)
This column directly answers percentile questions. If passing requires scoring above 69, you can see immediately that 20% of students failed.
Visualizing with an Ogive Chart
An ogive (pronounced “oh-jive”) plots cumulative frequency against class boundaries, creating a curve that shows how your data accumulates. It’s particularly useful for reading off percentiles and comparing distributions.
Step 1: Prepare Chart Data
Create a two-column range for your chart. Use upper class boundaries as X values and cumulative frequencies (or cumulative relative frequencies) as Y values:
I1: Upper Boundary J1: Cumulative Frequency
I2: 59 J2: 1
I3: 69 J3: 10
I4: 79 J4: 28
I5: 89 J5: 43
I6: 100 J6: 50
For a proper ogive, add a starting point at (49, 0) to anchor the curve at zero before the first interval.
Step 2: Create the Chart
- Select your data range (I1:J6, or I1:J7 if you added the anchor point)
- Go to Insert → Charts → Line Chart → Line with Markers
- Right-click the chart → Select Data → ensure Upper Boundary is the X axis
- Add chart title: “Cumulative Frequency of Exam Scores”
- Label axes: “Score” (X) and “Cumulative Frequency” (Y)
Step 3: Reading the Ogive
The ogive’s shape reveals distribution characteristics. A steep section indicates many observations in that range; a flat section means few. To find the median, draw a horizontal line from Y = 25 (half of 50 observations) to the curve, then drop vertically to read the corresponding score—approximately 77 for this dataset.
Practical Tips and Common Errors
Choosing Bin Sizes
Start with the Sturges formula: k = 1 + 3.322 × log₁₀(n), where n is your sample size. For 50 observations, this suggests about 7 bins. Adjust based on your data’s natural groupings and the precision you need.
Handling Boundary Values
Decide upfront whether interval boundaries are inclusive. If your bins are 60-69 and 70-79, where does a score of exactly 70 go? The FREQUENCY function places boundary values in the lower bin (70 goes with 60-69). COUNTIF comparisons give you explicit control. Document your choice.
Off-by-One Errors
The most common mistake is miscounting boundaries. Always verify that your frequency column sums to your total observation count. If it doesn’t, you’ve either double-counted or missed values at the boundaries.
Data Analysis ToolPak Alternative
For quick analysis, enable Excel’s Data Analysis ToolPak (File → Options → Add-ins → Analysis ToolPak). Then use Data → Data Analysis → Histogram. This generates frequency tables automatically but offers less customization than manual formulas.
Dealing with Ties
When many observations share identical values, consider whether grouped frequency (bins) or ungrouped frequency (exact values) better serves your analysis. For discrete data with few unique values, ungrouped tables often work better.
Cumulative frequency tables transform raw data into actionable insights. Master these Excel techniques, and you’ll handle distribution analysis efficiently across any domain.