How to Create a Frequency Distribution Table in Excel

Frequency distribution tables are one of the most practical ways to make sense of raw data. Whether you're analyzing survey responses, test scores, sales figures, or website traffic, grouping your data into intervals and counting how often values appear turns a messy spreadsheet into something genuinely readable. Excel gives you several ways to build one — and which method works best depends heavily on your data size, Excel version, and comfort level with formulas.

What a Frequency Distribution Table Actually Does

A frequency distribution table organizes data into groups (called bins or classes) and shows how many data points fall into each group. For example, if you have 200 exam scores ranging from 40 to 100, you might group them into ranges like 40–49, 50–59, 60–69, and so on, then count how many scores fall in each range.

The table typically includes:

• Bin ranges — the intervals you define
• Frequency — the count of values in each bin
• Relative frequency (optional) — the percentage each bin represents
• Cumulative frequency (optional) — a running total

Excel doesn't generate this automatically from scratch, but it gives you the building blocks to construct it quickly.

Method 1: Using the FREQUENCY Function (Array Formula)

The FREQUENCY function is Excel's dedicated tool for this task. It's a statistical array function that counts how many values fall within each bin you specify.

How to set it up:

1. Enter your raw data in one column (e.g., column A)
2. Define your bin upper limits in a separate column (e.g., column C) — these are the top of each range, so for 40–49, you'd enter 49
3. Select a blank range in the next column that has one more row than your bin list
4. Type =FREQUENCY(A2:A201, C2:C7) (adjusting ranges to match your data)
5. On older Excel versions, press Ctrl + Shift + Enter to enter it as an array formula; on Excel 365 and Excel 2021, pressing Enter alone handles it automatically

The result is a vertical array of counts — one per bin, plus an extra count for values above your highest bin.

⚠️ A common stumbling block: the bin column holds the upper boundary of each range, not labels. If you want display labels like "40–49," you'll add those separately in an adjacent column for readability.

Method 2: Using COUNTIFS for More Control

If you want more flexibility — especially for non-uniform bin sizes or specific conditions — COUNTIFS gives you explicit control over both the lower and upper boundaries of each range.

Example formula for a bin of 50–59:

=COUNTIFS(A2:A201, ">=50", A2:A201, "<=59") 

You'd write a separate formula for each bin. This approach is more manual but easier to understand, easier to audit, and more adaptable when your bins aren't evenly spaced.

Method 3: Pivot Tables for Categorical Data

If your data is categorical (like product names, departments, or response choices rather than numerical ranges), a Pivot Table is often the fastest path to a frequency distribution.

1. Select your data and insert a Pivot Table
2. Drag your category field to the Rows area
3. Drag the same field to the Values area and set it to Count

For numerical data, Pivot Tables can also group values into ranges — right-click a row label and choose Group, then define your interval size. This works well for a quick visual summary but gives you less formula-level control than FREQUENCY or COUNTIFS.

Method 4: Data Analysis Toolpak (Histogram Tool)

Excel's Analysis Toolpak add-in includes a Histogram tool that generates a frequency table and optional chart in one step. 📊

To enable it: File → Options → Add-ins → Analysis Toolpak → Go → Check the box → OK

Once enabled:

1. Go to Data → Data Analysis → Histogram
2. Set your Input Range (your data) and Bin Range (your upper boundaries)
3. Choose an output location
4. Check Chart Output if you want a histogram alongside the table

The Toolpak produces a static table — it won't update if your source data changes. For a snapshot analysis of a fixed dataset, it's efficient. For ongoing reporting where data refreshes, the formula-based methods are more practical.

Adding Relative and Cumulative Frequency

Once you have raw counts, extending the table is straightforward:

• Relative frequency: Divide each bin count by the total count. For example, =B2/SUM(B2:B8) — format the column as a percentage.
• Cumulative frequency: Use a running sum. In the first row it equals the first frequency; in subsequent rows, add the current frequency to the cell above: =D2+B3

These columns transform a basic count table into a more analytically useful summary, especially when you need to identify medians, percentiles, or distribution shape.

The Variables That Shape Your Approach

The "right" method isn't universal — it shifts based on several factors:

• Excel version: Dynamic array support in Excel 365 and 2021 makes FREQUENCY easier to use without the old Ctrl+Shift+Enter requirement. Older versions require more care.
• Data size: Thousands of rows work fine with any method, but Pivot Tables and the Toolpak handle large datasets with less manual setup.
• Bin structure: Uniform intervals favor FREQUENCY; irregular or condition-based groupings favor COUNTIFS.
• Update frequency: Static data suits the Toolpak; live or frequently updated data suits formula-based approaches that recalculate automatically.
• Downstream use: If the table feeds a chart or further calculations, formula methods integrate more cleanly. If it's a one-time report, any method gets the job done.

How useful each approach feels in practice also depends on your familiarity with array formulas — what's effortless for one person can be a frustrating entry point for another, which makes the "best" method genuinely personal to your workflow and your data.