How to Create a Frequency Distribution in Excel

Frequency distributions are one of the most practical tools in data analysis — they let you see how often values fall within defined ranges, turning a messy column of numbers into a clear, readable summary. Excel offers several ways to build one, and the method that works best depends on your data size, Excel version, and how you plan to use the results.

What Is a Frequency Distribution?

A frequency distribution organizes data into groups (called bins or classes) and counts how many values fall into each group. For example, if you have 200 test scores, a frequency distribution might show how many students scored 0–59, 60–69, 70–79, 80–89, and 90–100.

The output answers a fundamental question: where is the data concentrated, and how is it spread?

Method 1: The FREQUENCY Function (Array Formula)

The FREQUENCY function is Excel's built-in tool for this task. It returns a vertical array of counts — one for each bin you define.

How to set it up:

1. Enter your raw data in one column (e.g., A2:A201)
2. Define your bin upper limits in a separate column (e.g., D2:D6 with values 59, 69, 79, 89, 100)
3. Select a range of empty cells one row taller than your bin range (e.g., E2:E7)
4. Type the formula: =FREQUENCY(A2:A201, D2:D6)
5. In older Excel versions, press Ctrl + Shift + Enter to enter it as an array formula
6. In Excel 365 and Excel 2019+, pressing Enter alone is sufficient — dynamic arrays handle this automatically

The result is a count for each bin, plus an overflow count for any values above the highest bin.

📊 The bin values represent upper boundaries — a bin of 69 captures everything from the previous bin's upper limit up to and including 69.

Method 2: PivotTable (No Formulas Required)

If your data is already in a table or list, a PivotTable can generate a frequency distribution without writing a single formula — and it's easier to update when data changes.

Steps:

1. Select your data range and insert a PivotTable (Insert > PivotTable)
2. Drag your numeric field into the Rows area
3. Drag the same field into the Values area and set it to Count
4. Right-click any row value in the PivotTable and select Group
5. Set the Starting at, Ending at, and By values to define your bin size

Excel groups the values automatically and displays counts per group. This approach is particularly useful when you need to adjust bin sizes on the fly or filter by other variables.

Method 3: The Data Analysis ToolPak (Histogram Tool)

Excel's Analysis ToolPak add-in includes a dedicated Histogram tool that outputs a frequency table and an optional chart in one step.

Enable it first:

• Go to File > Options > Add-ins
• Select Excel Add-ins from the Manage dropdown and click Go
• Check Analysis ToolPak and click OK

Using the Histogram tool:

1. Go to Data > Data Analysis > Histogram
2. Set the Input Range (your data) and Bin Range (your bin upper limits)
3. Choose an output location
4. Optionally check Chart Output to generate a histogram automatically

This method is best for one-time analysis or when you need a static snapshot with a chart attached.

Comparing the Three Methods

Defining Your Bins: The Variable That Changes Everything

The bins you choose have a significant effect on what the distribution reveals. Too few bins and the data looks oversimplified. Too many bins and patterns get lost in noise.

A few common approaches:

• Equal-width bins — divide the data range into equal intervals (e.g., every 10 units). Simple and easy to explain.
• Custom bins — use domain knowledge to define meaningful boundaries (e.g., grade cutoffs, age brackets, price tiers)
• Sturges' Rule — a formula suggesting the number of bins: k = 1 + 3.322 × log₁₀(n), where n is the number of data points. Useful as a starting point, not a hard rule.

Working with Large or Dynamic Datasets

For large datasets (thousands of rows), the FREQUENCY function and PivotTable scale well without performance issues in most cases. The ToolPak Histogram is less suited here since it produces a static output that doesn't update.

If your data lives in a named table (Insert > Table), PivotTable-based distributions will automatically expand as new rows are added — a meaningful advantage for ongoing data collection.

For users on Excel 365, the FREQUENCY function also pairs well with newer dynamic array functions like UNIQUE and SEQUENCE, enabling more automated bin generation without manually typing boundary values.

Excel Version Differences Worth Knowing

• Excel 365 / 2019+: Dynamic arrays mean FREQUENCY spills results automatically — no Ctrl+Shift+Enter needed
• Excel 2016 and earlier: Array entry is required; forgetting this step produces incorrect results
• Excel Online: Supports FREQUENCY and PivotTable, but the ToolPak is not available in the browser version
• Mac versions of Excel: ToolPak is available but must be enabled separately; behavior is otherwise consistent

The right approach for building a frequency distribution in Excel shifts considerably depending on whether you need results that update live, whether you're more comfortable with formulas or visual tools, and what version of Excel you're working in. Each method produces the same core output — but the workflow, flexibility, and chart options they offer are genuinely different.