How to Create a Frequency Table in Excel

A frequency table is one of the most practical tools in data analysis — it shows how often each value (or range of values) appears in a dataset. Whether you're summarizing survey responses, sales figures, or test scores, Excel gives you several ways to build one. The method that works best depends on your data type, Excel version, and how much flexibility you need.

What Is a Frequency Table (and Why Build One)?

A frequency table organizes raw data into a summary showing how many times each value or category occurs. For example, if you have 200 customer ages, a frequency table groups them into ranges (20–30, 31–40, etc.) and counts how many customers fall into each group.

This turns a wall of numbers into something readable — and it's the foundation for histograms, pivot charts, and statistical reporting.

Method 1: Using the COUNTIF Function

Best for: Categorical data (text values, discrete numbers, yes/no responses)

COUNTIF is the most straightforward approach when your data contains distinct categories rather than continuous ranges.

Steps:

1. List your unique categories in one column (e.g., column D)
2. In the adjacent column, enter: =COUNTIF($A$2:$A$100, D2)
3. Drag the formula down for each category

The formula counts how many times the value in D2 appears in your data range. The $ symbols lock the data range so it doesn't shift as you copy the formula down.

When it works well: Product names, departments, survey options, pass/fail results — anywhere each data point belongs to a named category.

Method 2: Using the FREQUENCY Function

Best for: Numerical data grouped into ranges (bins)

The FREQUENCY function is Excel's dedicated tool for this job. It takes two inputs: your data array and a bins array (the upper boundary of each range).

Steps:

1. Enter your bin boundaries in a column (e.g., 10, 20, 30, 40)
2. Select a range of empty cells one row taller than your bins column
3. Type: =FREQUENCY(A2:A100, D2:D5)
4. Press Ctrl + Shift + Enter (array formula in older Excel versions)

In Excel 365 and Excel 2019+, FREQUENCY spills results automatically without the Ctrl+Shift+Enter step.

The output gives you a count for each bin, plus an extra cell at the bottom counting values above the highest bin boundary.

Method 3: Using a PivotTable

Best for: Large datasets, grouped numerical ranges, or when you want to filter and explore dynamically 📊

PivotTables are the most powerful option and require no formulas.

Steps:

1. Select your dataset and go to Insert → PivotTable
2. Drag your data field into the Rows area
3. Drag the same field into the Values area (it will default to Count)
4. Right-click a row value → Group to define numeric ranges (e.g., group by intervals of 10)

The result is a live frequency table you can filter, sort, and expand. If your source data updates, refreshing the PivotTable (right-click → Refresh) instantly updates the counts.

This method handles tens of thousands of rows without slowing down, and grouping options let you adjust bin sizes on the fly.

Method 4: Using the Data Analysis ToolPak (Histogram)

Best for: Quick statistical output with automatic bin suggestions

If the Analysis ToolPak add-in is enabled (File → Options → Add-ins → Analysis ToolPak), you get a dedicated Histogram tool that generates a frequency table and chart in one step.

Steps:

1. Go to Data → Data Analysis → Histogram
2. Set your Input Range (your data column) and optionally a Bin Range
3. Check Output Range to place results on the same sheet, or choose a new sheet
4. Optionally check Chart Output to generate a histogram automatically

Excel will calculate bin boundaries automatically if you leave the Bin Range blank — useful for exploratory analysis when you haven't decided on groupings yet.

Relative Frequency: Adding Percentages

A basic frequency table shows raw counts. A relative frequency column adds the percentage each group represents of the whole — often more meaningful than raw numbers.

Add a column next to your frequency counts with the formula:

=B2/SUM($B$2:$B$10)

Format the column as a percentage. This shows not just how many but what proportion — which matters when comparing datasets of different sizes. ✅

Key Variables That Affect Which Method You Should Use

• Data type: Categorical data suits COUNTIF; continuous numerical data suits FREQUENCY or PivotTable grouping
• Dataset size: PivotTables handle large datasets more efficiently than array formulas
• Excel version: FREQUENCY spills automatically in Excel 365; older versions require Ctrl+Shift+Enter
• Need for interactivity: PivotTables allow live filtering and regrouping; formula-based tables are static
• Bin flexibility: The ToolPak suggests bins automatically; manual methods give you full control
• Skill level: COUNTIF and PivotTables have gentler learning curves; FREQUENCY's array behavior can trip up newer users

When Results Differ Across Setups

Someone using Excel 365 will find the FREQUENCY function simpler to use — it spills results without special keystrokes. Someone on Excel 2016 needs to remember the Ctrl+Shift+Enter step or the formula will only return a single value instead of the full array.

A user analyzing 500 rows of sales data will get fast results from any method. Someone working with 50,000+ rows may notice PivotTables respond more fluidly than large arrays of COUNTIF formulas recalculating across the sheet.

If your categories change frequently (new products added, new survey options), a PivotTable that refreshes on demand will save significant time versus manually updating a COUNTIF formula list. 🔄

The right approach shifts based on data volume, how often the data changes, what version of Excel you're running, and what you plan to do with the frequency table once it's built — whether that's a one-time summary or a living dashboard.