A frequency chart organizes raw data into a table showing how often each value or category appears. Whether you’re working with a list of survey responses, test scores, or any other dataset, the process follows the same core logic: sort your values into groups, count how many fall into each group, and display the results. Here’s how to do it from start to finish.
Decide What Type of Data You Have
The way you set up your frequency chart depends on whether your data is categorical or numerical, because each type gets organized differently.
Categorical data already has natural groups. If you surveyed 20 kids about their favorite color, your categories are simply the colors themselves: red, blue, green, purple, and so on. You list each category in one column and count how many responses fall into each. No math needed to define the groups.
Numerical data requires a bit more thought. If your values are limited and discrete (like the number of cars per household: 0, 1, 2, 3), you can treat each number as its own category. But if you have a wide spread of values, like test scores ranging from 45 to 98, you’ll need to group them into ranges called class intervals (45–54, 55–64, 65–74, and so on). Continuous measurements like height, weight, or time should always be grouped into intervals.
Choose Your Intervals for Numerical Data
If your data needs grouping, you’ll want to pick intervals that are equal in width and cover the full range of your data without overlapping. Here’s how to calculate them.
First, find your range by subtracting the smallest value from the largest. If your lowest test score is 45 and your highest is 98, the range is 53.
Next, decide how many groups to use. A common guideline called Sturges’ rule suggests using 1 + 3.3 × log₁₀(N), where N is the number of data points. For a dataset of 50 values, that works out to about 1 + 3.3 × 1.7 = 6.6, so you’d round to 7 intervals. For most everyday projects, somewhere between 5 and 10 intervals works well.
Finally, divide the range by your number of intervals to get the width. With a range of 53 and 7 intervals, each interval would be about 7.6, which you’d round up to 8 for clean numbers. Your intervals might look like 45–52, 53–60, 61–68, and so on.
Avoiding Overlap
Every data point must fall into exactly one interval. If your first interval is 60–64 and your second is 64–68, where does 64 go? The standard fix is to define intervals so they go “up to but not including” the next boundary. Your intervals would read 60 up to 64, then 64 up to 68. This way a score of 64 lands cleanly in the second group, not both. Alternatively, you can use boundaries like 60–63 and 64–67 when working with whole numbers.
Build the Table Step by Step
With your categories or intervals defined, you’re ready to construct the chart. Set up a table with three columns: the category or interval, a tally column, and a frequency column.
Read through your raw data from left to right, top to bottom. For each value, place a tally mark in the row that matches its category or interval. When you reach a fifth tally mark in any row, draw it as a diagonal line through the previous four. This “five-bar gate” technique makes counting much faster when you’re done.
Once you’ve tallied every data point, count the marks in each row and write that number in the frequency column. As a quick check, add up all the frequencies. The total should equal your original number of data points. If it doesn’t, a value was missed or double-counted somewhere.
Here’s what a simple categorical frequency chart looks like for a survey of 20 children’s favorite colors:
- Red: 2
- Orange: 2
- Yellow: 1
- Green: 3
- Blue: 4
- Purple: 3
- Pink: 4
- Clear with Sparkles: 1
The total across all categories is 20, matching the sample size.
Add Relative and Cumulative Frequencies
A basic frequency chart tells you raw counts, but two additional columns make it far more useful.
Relative frequency shows what proportion of your total dataset falls into each group. Calculate it by dividing the frequency of each row by the total number of data points. If 8 out of 40 students scored in the 70–79 range, the relative frequency is 8 ÷ 40 = 0.20, or 20%. This lets you quickly compare groups even when datasets are different sizes.
Cumulative frequency is a running total. For each row, you add its frequency to the sum of all the rows above it. This tells you how many data points fall at or below a given value. If the first three intervals have frequencies of 3, 7, and 12, the cumulative frequencies are 3, 10, and 22. Cumulative relative frequency works the same way but divides that running total by the sample size, giving you the proportion of data at or below each interval.
Create a Frequency Chart in Excel
If you’re working with a larger dataset, Excel can handle the counting for you using the FREQUENCY function.
Start by entering your raw data in one column (say A1 through A50). In a separate column, list the upper boundaries of your intervals. If your intervals are 0–10, 11–20, 21–30, you’d enter 10, 20, and 30 in cells C1 through C3. These upper boundaries are called your “bins array.”
Select one more cell than you have bins (four cells in this example, since the extra cell captures any values above your highest bin). With those cells selected, type the formula:
=FREQUENCY(A1:A50, C1:C3)
If you’re using a current version of Microsoft 365, press Enter and the results will fill in automatically as a dynamic array. In older versions, press Ctrl+Shift+Enter instead to enter it as an array formula (Excel will add curly brackets around it). The function returns one count per bin, plus one extra count for any values that exceeded your highest bin boundary.
For categorical data, a pivot table is often simpler. Select your data, insert a pivot table, drag the category field into both the Rows area and the Values area, and Excel will generate counts for each category instantly.
Turn Your Table Into a Visual Chart
A frequency table becomes a histogram or bar chart with just a few steps. The horizontal axis displays your intervals or categories, and the vertical axis displays the frequency. Each group gets a bar whose height matches its count.
For numerical intervals, use a histogram where the bars touch each other, since the intervals represent a continuous range. For categorical data, use a bar chart with gaps between the bars, since the categories are distinct and unrelated.
To draw one by hand, mark your intervals evenly along the horizontal axis and scale the vertical axis to accommodate your highest frequency. Then draw a column over each interval that reaches up to the matching frequency value. In Excel or Google Sheets, select your frequency table, insert a chart, and choose either “Bar Chart” or “Histogram” depending on your data type.
Common Mistakes to Avoid
Overlapping intervals are the most frequent error. If one group ends at 50 and the next starts at 50, any data point landing exactly on 50 has no clear home. Define your boundaries so every value fits in one group only.
Unequal interval widths can distort your chart visually. A wider interval naturally collects more data points, making it look more common than it actually is. Keep all intervals the same width unless you have a specific statistical reason not to.
Using too many or too few intervals also causes problems. Too many intervals and most bars will be short or empty, hiding the overall pattern. Too few and you’ll lump different values together, losing the detail. Sturges’ rule gives a solid starting point, but adjust based on what makes the distribution easiest to read. For datasets under 30 observations, 5 or 6 intervals usually work well. For datasets of 100 or more, 8 to 12 intervals tend to reveal the shape of the data clearly.