What a frequency distribution does, and why you'd build one

A frequency distribution is a table that counts how many times each value (or range of values) appears in your data. If you have a column of test scores, a frequency distribution tells you how many students scored 90–99, how many scored 80–89, and so on. Excel doesn't have a single button for this — you build it by hand using a combination of formulas and sorting — but the process is straightforward once you know the steps.

You create one when you want to see the shape of your data at a glance. Instead of staring at 500 individual numbers, you see that most of them cluster in the middle, or that they're spread evenly, or that they bunch up at one end. This matters for spotting patterns, checking whether data looks normal, or deciding what kind of analysis makes sense next.

Key Takeaways

  • A frequency distribution groups your data into ranges (called bins or classes) and counts how many values fall into each range.
  • You build one in Excel by creating a list of ranges, then using the COUNTIFS function to count values that fall within each range.
  • The FREQUENCY function can do this automatically if your data is numeric and you want equal-sized ranges, but COUNTIFS gives you more control.
  • Once built, a frequency distribution is easiest to read as a chart — usually a column chart or histogram — rather than as raw numbers in a table.

Setting up your ranges (bins) in Excel

Start by deciding how many groups you want and what range each group covers. If you're working with test scores from 0 to 100, you might use ranges like 0–9, 10–19, 20–29, and so on. If you're working with ages, you might use 18–24, 25–34, 35–44. The choice depends on your data and what you're trying to see — too many ranges and the pattern disappears; too few and you lose detail.

Create two columns: one for the range label (like "90–99") and one for the count. In the label column, type out each range. In the count column, you'll put formulas that count how many values from your original data fall into each range. For example, if your test scores are in column A (rows 2 through 501), and you want to count scores between 90 and 99, you'll use a formula in the count column.

Using COUNTIFS to count values in each range

The COUNTIFS function counts cells that meet multiple conditions at once. This is what you use to count values that fall within a range. The syntax is: =COUNTIFS(range, ">=lower", range, "<=upper")

If your test scores are in cells A2:A501, and you want to count how many are between 90 and 99, you'd write: =COUNTIFS($A$2:$A$501,">=90",$A$2:$A$501,"<=99") The dollar signs lock the range so it doesn't change if you copy the formula down. Put this formula in the count column next to the 90–99 label. Then copy it down and change the numbers for each row: 80–89 becomes ">=80" and "<=89", and so on.

Make sure your ranges don't overlap and don't leave gaps. If one range ends at 89 and the next starts at 90, a score of exactly 89.5 won't be counted twice. If one ends at 89 and the next starts at 91, a score of 90 falls through the crack. Use "less than or equal to" and "greater than or equal to" consistently so every value lands in exactly one bin.

The FREQUENCY function as an alternative

Excel has a built-in FREQUENCY function that does this work automatically, but it has limits. It works only with numeric data and only creates equal-sized ranges (bins). The syntax is: =FREQUENCY(data_array, bins_array)

You provide your data (the test scores) and an array of the upper boundaries of each bin. If you want bins of 0–9, 10–19, 20–29, you'd list the boundaries as 9, 19, 29. The function returns the count for each bin. The catch: FREQUENCY is an array formula, which means you have to select the cells where the results will go, type the formula, then press Ctrl+Shift+Enter (not just Enter) to make it work. Many people find COUNTIFS easier because it behaves like a normal formula.

Turning your frequency distribution into a chart

Once your counts are in place, select the range labels and counts together, then insert a chart. A column chart or bar chart works well for most frequency distributions. The x-axis shows your ranges, and the height of each bar shows the count. This visual form makes it much easier to spot whether your data is clustered, spread out, or skewed to one side.

If you're working with continuous data (like heights or weights) and want the bars to touch each other (which is typical for a histogram), right-click the bars, select "Format Data Series", and set the gap width to 0. This makes it clear that the ranges are continuous and adjacent, not separate categories.

Checking your work and fixing common mistakes

Add up all the counts in your frequency column. The total should equal the number of rows in your original data. If it doesn't, you have overlapping or missing ranges. Check that your COUNTIFS formulas use the same data range (with dollar signs) and that your upper and lower boundaries don't skip any values.

If you have blank cells or text values mixed into numeric data, COUNTIFS will ignore them — which is usually what you want, but it means your total count might be lower than your row count. If that surprises you, use COUNTA to count all non-blank cells in your original column and compare it to your frequency total.

Frequently Asked Questions

How many bins should I use?

A common rule is to use the square root of the number of data points. If you have 100 values, use about 10 bins. If you have 400, use about 20. This is a starting point, not a rule — adjust based on what you see. Too many bins and the chart looks spiky; too few and you lose detail.

What if my data has decimals?

COUNTIFS works fine with decimals. Just make sure your ranges account for them. If you have values like 85.5 and 89.3, don't use a range of 85–89 and 90–94 — use 85.0–89.9 and 90.0–94.9, or adjust your boundaries to match your data's precision.

Can I create a frequency distribution for text data?

Not in the traditional sense. Frequency distributions are for numeric ranges. For text (like product names or categories), use a pivot table instead — it counts how many times each unique text value appears, which is the text equivalent of a frequency distribution.

Why does my FREQUENCY formula return an error?

The most common reason is forgetting to press Ctrl+Shift+Enter instead of just Enter. FREQUENCY is an array formula and needs that key combination to work. Also check that your data and bins are both numeric and that you've selected enough cells for the results — you need one cell for each bin.

Should I include a bin for values outside my main range?

Yes, if you expect outliers. Add a final bin like "100+" to catch any values above your highest expected range. This prevents surprises and makes sure your total count matches your data count.