Creating Frequency Distribution Tables with Array Formulas in Excel

Every business generates numbers that pile up fast: daily sales figures, customer transaction values, employee ages, survey ratings. A raw list of fifty or a hundred numbers tells you almost nothing on its own. A frequency distribution table fixes that by grouping the data and showing how often values fall into each group. In Excel, the fastest way to build one is the FREQUENCY function, an array formula that does in one step what would otherwise take several COUNTIF formulas. This post walks through what a frequency distribution is, how the FREQUENCY function works, and how to build a working table from scratch.

What a frequency distribution table actually shows 🔗

A frequency distribution organizes a dataset into intervals, usually called bins or class intervals, and counts how many data points fall into each one. Instead of scrolling through a long column of numbers, you get a short table that reveals the shape of the data at a glance. It is one of the most basic tools in statistics, and it’s what most calculations after it are built on, including the mean, median, and measures of variance.

The idea works the same way whether the data is exam marks, ages, or sales figures. You decide on sensible ranges, count how many values land in each range, and lay it out in a table. In a business context, this turns a spreadsheet of raw numbers into something a manager can actually act on. Grouping data into a frequency table is usually the very first step before any deeper statistical analysis, such as building a histogram or checking for outliers.

Why array formulas make this possible 🔗

A regular Excel formula returns one value in one cell. An array formula can return multiple values at once, spread across a range of cells. FREQUENCY is built exactly for this. Its syntax is straightforward: =FREQUENCY(data_array, bins_array), where data_array is the range containing your raw numbers and bins_array is the range containing the upper limits of each group. Because it counts across the entire dataset in one shot, it’s faster and less error-prone than writing a separate COUNTIF for every single bin, especially once you have more than four or five groups. According to Microsoft’s own documentation, FREQUENCY ignores blank cells and text entirely and only works with numeric values.

Entering the formula: two different methods 🔗

How you confirm the formula depends on your Excel version. In older versions, you first select the entire output range, type the formula into the top-left cell, and press Ctrl+Shift+Enter instead of just Enter. Excel wraps the formula in curly braces to show it’s an array formula. In Microsoft 365, this extra step isn’t needed. You can type the formula into a single cell and press Enter, and the results automatically spill into the cells below it without needing curly braces at all.

Setting up your data before applying FREQUENCY 🔗

Arranging the data array 🔗

Before you touch the formula, get your raw data into shape. Place every value in a single column with no blank rows in between. FREQUENCY only counts numbers, so if your column has stray text or empty cells mixed in, those get skipped rather than counted. This matters when you’re pulling data from a larger sheet where some rows might be incomplete.

Defining the bins array 🔗

The bins_array is a short list of numbers that mark the upper boundary of each group. If you want groups of ≤30, 31-40, 41-50, 51-60, and above 60, your bins_array only needs to contain four numbers: 30, 40, 50, and 60. Excel automatically works out where each value falls based on these boundaries, and it always adds one extra result for anything above the last bin. This is a common source of confusion for first-time users, since the bins only define upper limits, not full ranges, and the function figures out the “greater than the previous bin, less than or equal to the current bin” logic on its own.

Step-by-step: building a working example 🔗

Suppose you’re reviewing the monthly revenue (in ₹ thousands) generated by 15 sales executives at a retail chain: 42, 55, 38, 61, 47, 29, 58, 63, 51, 34, 45, 39, 57, 62, and 48. You want to see how many executives fall into each revenue bracket.

First, list the data in cells B2:B16. Then set up the bins in D2:D5 as 30, 40, 50, and 60. Select the output range E2:E6, which is one cell more than the number of bins, and enter:

=FREQUENCY(B2:B16, D2:D5)

In older Excel versions, confirm with Ctrl+Shift+Enter. In Excel 365, just press Enter in cell E2 and let it spill downward. The result looks like this:

Add up the frequency column and it matches the total number of executives: fifteen. That’s a quick way to double-check the formula was entered correctly.

Reading the output: the overflow bin 🔗

Notice the table has five results even though there are only four bin values. This is by design. The FREQUENCY function always returns one more result than the number of items in bins_array, and that last extra number is the count of everything above the highest bin. If you forget to select that extra cell before entering the formula, Excel simply won’t show that overflow count, and your totals won’t add up to the full dataset. This is one of the most common mistakes people make the first time they use this function.

Going further: relative and cumulative frequency 🔗

A raw count is useful, but percentages often communicate more clearly, especially in a report meant for people outside the finance or analytics team. Relative frequency converts each count into a percentage of the total by dividing each frequency by the overall count of data points. Cumulative frequency goes a step further, adding up the running total as you move down the table, which is handy for answering questions like “what share of executives generated ₹50,000 or less.” Both columns build directly on the output of the FREQUENCY formula and don’t need any additional array logic. Statistical teaching material generally treats relative frequency as a natural extension of the basic frequency table, since it makes comparisons across datasets of different sizes much easier.

Common mistakes to watch for 🔗

A few errors come up repeatedly, especially with legacy versions of Excel.

Not selecting enough output cells: Since FREQUENCY returns one more value than the number of bins, selecting too few cells cuts off the overflow count.

Forgetting Ctrl+Shift+Enter: On non-365 versions, pressing just Enter returns a single, often incorrect, number instead of the full array.

Unsorted or duplicate bins: The bins_array needs to be in ascending order for the groupings to make logical sense.

Mixed data types in the data array: Text or blanks inside the data range get silently ignored, which can make your total count look lower than expected if you’re not careful.

Where this skill fits in a business career 🔗

Frequency distribution isn’t just a classroom exercise. Retail businesses use it to study how transaction values are spread across customers, HR teams use it to understand employee age or salary bands, and market researchers use it to summarize survey responses. It’s also the foundation for a histogram, which is often the first chart analysts reach for when presenting data patterns to a non-technical audience. Understanding how to build a frequency distribution directly in a spreadsheet, rather than depending on specialized statistical software, is a practical skill that shows up early in most data-related roles.

What do you think? If you had last month’s sales data for your own class project or a part-time job, what bin sizes would actually be useful to you? And where else in a typical business report do you think a frequency table would explain something faster than a paragraph of text?

References

1. https://www.cuemath.com/data/frequency-distribution-table/
2. https://www.geeksforgeeks.org/maths/frequency-distribution-table/
3. https://support.microsoft.com/en-us/office/frequency-function-44e3be2b-eca0-42cd-a3f7-fd9ea898fdb9
4. https://exceljet.net/functions/frequency-function
5. https://www.goskills.com/Excel/Resources/FREQUENCY-Excel
6. https://www.geeksforgeeks.org/maths/frequency-distribution/
7. https://www.datacamp.com/tutorial/frequency-distribution-excel