How to Create a Frequency Distribution in Excel

Key Insights

• Excel’s FREQUENCY function is an array formula that counts how many values fall within specified ranges, making it the most direct method for creating frequency distributions from raw data.
• Choosing appropriate bin sizes matters more than the method you use—bins that are too wide hide patterns, while bins that are too narrow create noise that obscures trends.
• COUNTIFS offers more flexibility than FREQUENCY when you need dynamic ranges or want to exclude certain values, while Pivot Tables provide the fastest path from raw data to visual analysis.

Introduction to Frequency Distributions

A frequency distribution shows how often each value (or range of values) appears in a dataset. Instead of staring at hundreds of raw numbers, you get a summary that reveals patterns: where data clusters, how it spreads, and whether outliers exist.

You’ll use frequency distributions constantly in practical work. Analyzing survey responses to see which ratings customers choose most often. Breaking down sales figures by price range to understand your market segments. Summarizing test scores to identify whether students cluster around the passing grade or distribute evenly across the scale.

Excel provides several methods to create these distributions, each with tradeoffs. We’ll cover all of them so you can pick the right tool for your specific situation.

Preparing Your Data

Before calculating frequencies, your data needs to be in the right shape. Excel’s frequency functions expect a single column of numeric values with no blanks or text mixed in.

Here’s a sample dataset of 50 exam scores we’ll use throughout this article:

| Row | Score |
|-----|-------|
| 1   | 72    |
| 2   | 85    |
| 3   | 91    |
| 4   | 67    |
| 5   | 78    |
| 6   | 82    |
| 7   | 55    |
| 8   | 94    |
| 9   | 73    |
| 10  | 88    |
| 11  | 61    |
| 12  | 79    |
| 13  | 84    |
| 14  | 70    |
| 15  | 92    |
| 16  | 58    |
| 17  | 81    |
| 18  | 76    |
| 19  | 89    |
| 20  | 63    |
| 21  | 77    |
| 22  | 86    |
| 23  | 69    |
| 24  | 95    |
| 25  | 74    |
| 26  | 80    |
| 27  | 66    |
| 28  | 83    |
| 29  | 71    |
| 30  | 87    |
| 31  | 59    |
| 32  | 90    |
| 33  | 75    |
| 34  | 68    |
| 35  | 93    |
| 36  | 64    |
| 37  | 78    |
| 38  | 85    |
| 39  | 72    |
| 40  | 81    |
| 41  | 56    |
| 42  | 88    |
| 43  | 73    |
| 44  | 79    |
| 45  | 96    |
| 46  | 62    |
| 47  | 84    |
| 48  | 77    |
| 49  | 91    |
| 50  | 70    |

Place this data in column A, with “Score” as your header in A1 and values starting in A2.

Clean your data before proceeding. Use =ISNUMBER(A2) to check for non-numeric values. Remove or fix any blanks—FREQUENCY handles them, but they can cause confusion when validating results. Sort isn’t required, but it helps when manually verifying your frequency counts.

Defining Bins (Class Intervals)

Bins determine how your data gets grouped. Poor bin choices produce misleading distributions. Too few bins and you lose detail. Too many bins and the distribution looks like random noise.

A common rule of thumb: use the square root of your sample size as a starting point. For 50 data points, that suggests 7 bins. For exam scores ranging from 55 to 96, bins of 10 points each work well.

Create your bins in a separate column. The FREQUENCY function uses upper bounds, so you specify the maximum value for each bin:

| Column C | Column D    |
| Bin Upper| Label       |
|----------|-------------|
| 60       | 51-60       |
| 70       | 61-70       |
| 80       | 71-80       |
| 90       | 81-90       |
| 100      | 91-100      |

Place bin upper bounds in C2:C6 and labels in D2:D6 for clarity.

Important: FREQUENCY counts values less than or equal to each bin boundary. A score of exactly 70 falls into the “61-70” bin, not “71-80”. Design your bins with this behavior in mind.

For continuous data like measurements or prices, consider whether you want inclusive or exclusive boundaries. The boundary value always goes to the lower bin with FREQUENCY.

Using the FREQUENCY Function

FREQUENCY is Excel’s purpose-built function for this task. The syntax is straightforward:

=FREQUENCY(data_array, bins_array)

Here’s how to apply it to our exam scores:

=FREQUENCY(A2:A51, C2:C6)

The function returns an array with one more element than your bins array. That extra element counts values greater than your highest bin. For our example, we get 6 results for 5 bins.

For Excel 365 or Excel 2021 (Dynamic Arrays):

Select cell E2, enter the formula, and press Enter. Excel automatically spills the results into E2:E7.

E2: =FREQUENCY(A2:A51, C2:C6)

Results spill automatically:

| E      |
|--------|
| 3      |  (scores ≤ 60)
| 8      |  (scores 61-70)
| 16     |  (scores 71-80)
| 15     |  (scores 81-90)
| 8      |  (scores 91-100)
| 0      |  (scores > 100)

For older Excel versions (Legacy Array Formula):

Select the output range E2:E7 first, then type the formula, then press Ctrl+Shift+Enter instead of just Enter. Excel wraps the formula in curly braces to indicate it’s an array formula:

{=FREQUENCY(A2:A51, C2:C6)}

Don’t type the curly braces yourself—Excel adds them when you use Ctrl+Shift+Enter.

The results tell us most students scored between 71-90, with the distribution roughly symmetric around that range.

Alternative Methods: COUNTIFS and Pivot Tables

FREQUENCY works, but it has limitations. You can’t easily exclude certain values, handle non-contiguous ranges, or create overlapping bins. COUNTIFS and Pivot Tables solve these problems.

COUNTIFS Approach

COUNTIFS counts cells matching multiple criteria. For frequency distributions, you specify lower and upper bounds:

=COUNTIFS(A:A, ">="&C2, A:A, "<="&D2)

Set up your bins with explicit lower and upper bounds:

| C (Lower) | D (Upper) | E (Formula)                           | F (Count) |
|-----------|-----------|---------------------------------------|-----------|
| 51        | 60        | =COUNTIFS($A:$A,">="&C2,$A:$A,"<="&D2)| 3         |
| 61        | 70        | =COUNTIFS($A:$A,">="&C3,$A:$A,"<="&D3)| 8         |
| 71        | 80        | =COUNTIFS($A:$A,">="&C4,$A:$A,"<="&D4)| 16        |
| 81        | 90        | =COUNTIFS($A:$A,">="&C5,$A:$A,"<="&D5)| 15        |
| 91        | 100       | =COUNTIFS($A:$A,">="&C6,$A:$A,"<="&D6)| 8         |

COUNTIFS advantages:

• Explicit control over boundary inclusion (use “>” vs “>=” as needed)
• Easy to add exclusion criteria (e.g., exclude zeros)
• Each row is independent—you can delete or modify bins without breaking others
• Works with non-numeric criteria for categorical data

Pivot Table Method

Pivot Tables offer the fastest path when you need quick exploration rather than a permanent formula setup.

1. Select your data range (A1:A51 including header)
2. Insert → PivotTable → New Worksheet
3. Drag “Score” to both Rows and Values areas
4. Values will default to Sum—click it and change to Count

At this point, you’ll see every unique score listed. To group into bins:

5. Right-click any value in the Row Labels
6. Select “Group”
7. Set Starting at: 51, Ending at: 100, By: 10
8. Click OK

Excel creates grouped ranges automatically:

| Row Labels | Count of Score |
|------------|----------------|
| 51-60      | 3              |
| 61-70      | 8              |
| 71-80      | 16             |
| 81-90      | 15             |
| 91-100     | 8              |
| Grand Total| 50             |

Pivot Tables update automatically when source data changes. They’re ideal for exploratory analysis but less suitable when you need formulas that integrate with other calculations.

Visualizing with a Histogram

Numbers tell part of the story. A histogram makes the distribution shape immediately obvious.

Quick Histogram from Frequency Data

If you’ve already calculated frequencies using any method above:

1. Select your labels and frequency counts (D2:E6 from our FREQUENCY example)
2. Insert → Charts → Column Chart → Clustered Column
3. Right-click any bar → Format Data Series → Gap Width → Set to 0%

Setting gap width to zero makes bars touch, which is the defining visual characteristic of a histogram versus a bar chart.

Using Excel’s Built-in Histogram Chart (Excel 2016+)

Excel’s statistical charts include a histogram option that handles binning automatically:

1. Select your raw data (A2:A51)
2. Insert → Charts → Statistical → Histogram
3. Click the horizontal axis → Format Axis
4. Under Axis Options, set Bin Width to 10 (or specify Number of Bins)

This method requires no pre-calculation. Excel determines bins and counts automatically. However, you have less control over exact bin boundaries.

Analysis ToolPak Method

For more statistical rigor, enable the Analysis ToolPak:

1. File → Options → Add-ins → Go → Check “Analysis ToolPak” → OK
2. Data → Data Analysis → Histogram → OK
3. Input Range: A2:A51
4. Bin Range: C2:C6 (your upper bounds)
5. Check “Chart Output”
6. Click OK

The ToolPak generates a frequency table and histogram on a new worksheet. This approach is particularly useful when you need to document your analysis methodology or produce results that match statistical software output.

Conclusion

Creating frequency distributions in Excel comes down to choosing the right tool for your situation:

Use FREQUENCY when you need a straightforward, formula-based solution that updates automatically. It’s the most direct method for standard frequency analysis.

Use COUNTIFS when you need flexibility—custom boundary logic, exclusion criteria, or integration with other conditional calculations.

Use Pivot Tables when you’re exploring data and want quick results without setting up formulas. They’re also best when your data changes frequently and you want automatic regrouping.

Use the Analysis ToolPak when you need to document methodology or produce output that matches statistical software conventions.

Whichever method you choose, remember that bin selection drives the usefulness of your distribution. Start with the square root rule, then adjust based on what the data reveals. A good frequency distribution should show clear patterns without hiding important variation.