Creating Frequency Distributions in Excel with Array Formulas

Raw data in its unorganized form is nearly impossible to interpret. Whether you’re looking at student exam scores, monthly sales figures, or customer response times, a long column of numbers tells you almost nothing at a glance. That’s exactly where frequency distributions come in – and Excel’s FREQUENCY array formula makes building them fast, accurate, and dynamic. This guide walks you through exactly how it works, step by step.

What is a frequency distribution? 🔗

A frequency distribution is a summary that shows how often values in a dataset fall within defined ranges. Instead of reading through hundreds of individual data points, you get a clear count of how many values belong in each group. This makes it easy to spot patterns, identify where data is concentrated, and flag any outliers worth investigating.

For example, if you have exam scores for 60 students, a frequency distribution might tell you that 5 students scored below 50, 18 scored between 50-65, 22 scored between 66-80, and 15 scored above 80. That one table tells you far more than scrolling through 60 individual scores. According to DataCamp, frequency distributions are vital for identifying patterns, trends, and potential outliers, providing deeper insights into data.

These grouped ranges are called bins – a core concept you’ll need to understand before using Excel’s FREQUENCY function.

Understanding the FREQUENCY function 🔗

Excel’s FREQUENCY function is a built-in statistical tool that counts how many values from your dataset fall into each bin you define. What makes it different from regular Excel formulas is that it’s an array formula – it processes an entire range of values at once and returns multiple results simultaneously.

As Microsoft’s official documentation explains, the FREQUENCY function calculates how often values occur within a range and returns a vertical array of numbers. Because it returns multiple values, it must be entered differently from a standard formula – more on that shortly.

Syntax 🔗

The function has a straightforward syntax:

=FREQUENCY(data_array, bins_array)

data_array is the range containing your raw data values – the numbers you want to analyze. bins_array is the range containing your bin thresholds – the upper limits of each group. Both arguments are required. If data_array is empty, the function returns zeros. If bins_array is empty, it simply returns the total count of all values.

The extra result rule 🔗

One important behavior to know upfront: according to Exceljet, the FREQUENCY function always returns one more result than the number of bins you define. This extra result captures any values in your data that are greater than the highest bin value. So if you define 4 bins, you need to select 5 output cells to see all results, including that overflow count.

Setting up your data and bins 🔗

Before entering the formula, your spreadsheet needs to be organized properly. Good setup prevents errors and makes your frequency table easy to read.

Step 1 – Organize your raw data 🔗

Place all your data values in a single column. There should be no blank rows within the data range, and the column should contain only numeric values. The FREQUENCY function automatically ignores blank cells and text, but keeping your data clean is still good practice.

For this example, assume you have 50 employee productivity scores (0-100) in cells A2:A51.

Step 2 – Define your bins 🔗

Bins are the upper boundary values for each group. If you want to group scores into ranges of 0-20, 21-40, 41-60, 61-80, and 81-100, your bins column should contain: 20, 40, 60, 80, 100.

Enter these values in cells C2:C6. A few principles for choosing good bins, as recommended by DataCamp:

• Make bins equal width wherever possible so comparisons across groups are fair.
• Use enough bins to show meaningful variation, but not so many that every bin has a count of 1 or 2.
• Ensure bins cover the full range of your data, including any edge values.

Using the FREQUENCY array formula: step by step 🔗

Step 3 – Select the output range 🔗

Since you have 5 bins (20, 40, 60, 80, 100), you need to select 6 output cells – one extra for the overflow. Click on cell D2, then hold Shift and click D7 to select the range D2:D7.

This selection must be made before typing the formula. This is what distinguishes array formula entry from regular formula entry in older Excel versions.

Step 4 – Enter the formula 🔗

With D2:D7 selected, type the formula:

=FREQUENCY(A2:A51, C2:C6)

Do not press Enter yet.

Step 5 – Confirm as an array formula 🔗

Here’s the critical difference from a normal formula. As TechOnTheNet explains, array formulas must be confirmed with Ctrl + Shift + Enter (on Mac: Command + Shift + Enter) instead of just Enter. Excel will then surround the formula with curly braces { }, like this:

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

Those curly braces are Excel’s way of flagging that this is an array formula operating across multiple cells simultaneously. Do not type the curly braces yourself – they must be inserted by Excel through the Ctrl + Shift + Enter keystroke.

Step 6 – Excel 365 and Excel 2021 users 🔗

If you’re on Microsoft 365 or Excel 2021, the process is simpler. Excel-Easy notes that these versions support dynamic arrays natively, so you can simply click the first output cell (D2), type the FREQUENCY formula, and press Enter normally. The results will automatically “spill” into the cells below – no need to pre-select the output range or use Ctrl + Shift + Enter. The curly braces won’t appear either, which is normal behavior for dynamic array formulas.

Reading and labeling your frequency table 🔗

Once the formula runs, you’ll see counts appear in your output range. Using the productivity score example, your results might look like this:

• D2: 4 – (scores ≤ 20)
• D3: 9 – (scores 21-40)
• D4: 17 – (scores 41-60)
• D5: 14 – (scores 61-80)
• D6: 6 – (scores 81-100)
• D7: 0 – (scores above 100, the overflow bin)

To make the table readable, add a label column next to your bins. In column B, you might write: “0-20”, “21-40”, “41-60”, “61-80”, “81-100”, “>100”. This turns your raw output into a properly formatted frequency distribution table that others can interpret at a glance.

Practical applications of frequency tables in Excel 🔗

The FREQUENCY function isn’t just a classroom exercise – it’s widely used across business functions. LiveFlow outlines several real-world uses, including market research, resource planning, quality control, and data validation. Here are the most common practical scenarios:

Academic and HR performance analysis 🔗

Schools use frequency distributions to understand the spread of exam scores across grade ranges. HR departments use the same logic to analyze performance review ratings – quickly identifying how many employees fall into each performance tier without manually counting rows of data.

Sales and revenue reporting 🔗

Sales teams group deal sizes or monthly revenues into brackets to understand where most activity is concentrated. A frequency table might reveal that 60% of sales are clustered in the $5,000-$15,000 range, which directly informs pricing strategy and sales targets.

Customer and survey data analysis 🔗

Survey responses – like satisfaction ratings or age groups – are a natural fit for frequency distributions. Rather than reporting an average rating alone, a frequency table shows the full spread of responses, making it clear whether scores are tightly clustered or widely dispersed across the scale.

Building histograms 🔗

Frequency tables are the foundation of histograms. Once your FREQUENCY formula populates the count column, you can select the label and count columns and insert a bar or column chart to create a visual histogram instantly. As GoSkills points out, the frequency table is dynamic – if your source data changes, the counts update automatically, and any chart built from the table updates too.

Adding relative and cumulative frequency 🔗

A basic frequency count is useful, but you can extend your table with two additional columns to make it even more informative.

Relative frequency (percentage) 🔗

Relative frequency expresses each bin’s count as a percentage of the total. If your total is 50 data points and one bin has 17 counts, the relative frequency is 34%. The formula in cell E2 (assuming D2 holds the count and D2:D7 is the full output range) would be:

=D2/SUM($D$2:$D$7)

Copy this formula down for all rows, then format the column as a percentage.

Cumulative frequency 🔗

Cumulative frequency shows the running total of counts as you move through bins – useful for answering questions like “what percentage of employees scored 60 or below?” In cell F2, start with =D2, then in F3 use =F2+D3, and copy that formula downward. Format as a percentage if needed. Together, these three columns – frequency, relative frequency, and cumulative frequency – form a complete statistical summary of your dataset.

Common mistakes to avoid 🔗

A few errors come up repeatedly when working with FREQUENCY for the first time:

Not selecting enough output cells. If you select exactly the same number of output cells as bins, you’ll miss the overflow count. Always select bins + 1 cells.

Pressing Enter instead of Ctrl + Shift + Enter. In older Excel versions, pressing Enter alone only fills the first selected cell with a single result instead of populating the entire array. Check for curly braces in the formula bar to confirm the array formula is active.

Editing a single cell in an array range. You cannot edit one cell of an array formula independently. To modify the formula, select the entire output range and delete it, then re-enter the corrected formula using Ctrl + Shift + Enter.

Bins not covering your full data range. If your highest bin value is lower than the highest data value, some data points will only show up in the overflow bin and may be missed in your analysis. Always verify your bins span the complete range of values in your dataset.

What do you think? If you were analyzing customer satisfaction survey scores on a scale of 1-10, how would you decide how many bins to use – and would a wider or narrower bin range give you more useful insights for decision-making? How might the picture change if you looked at relative frequency percentages rather than raw counts?

References

1. https://www.datacamp.com/tutorial/frequency-distribution-excel
2. https://support.microsoft.com/en-us/office/frequency-function-44e3be2b-eca0-42cd-a3f7-fd9ea898fdb9
3. https://exceljet.net/functions/frequency-function
4. https://www.techonthenet.com/excel/formulas/frequency.php
5. https://www.excel-easy.com/examples/frequency.html
6. https://liveflow.com/product-guides/frequency-function-in-excel-explained
7. https://www.goskills.com/Excel/Resources/FREQUENCY-Excel