How to Use the FREQUENCY Function in Excel: Want to organize your data into ranges and see how often values occur? 📈 The FREQUENCY function in Excel is your go-to tool for creating frequency distributions—it tells you how many values fall into specific numeric bins. Whether you’re analyzing test scores, survey data, or sales figures, this function works like a charm on Windows, Mac, and Ubuntu (via Excel Online). 🧠✅
This guide covers how to use the FREQUENCY function in Excel, along with practical examples, formatting tips, and the keyboard shortcuts that’ll save you hours of manual counting!
🔍 What Is the FREQUENCY Function?
Syntax:
=FREQUENCY(data_array, bins_array)
| Argument | Description |
|---|---|
data_array | The actual values to be analyzed |
bins_array | The intervals (or “bins”) that group those values |
📌 The result is an array of numbers, where each number represents the count of values within each bin.
💡 FREQUENCY Function Example
Suppose you have test scores in cells A2:A11:
78, 89, 92, 67, 88, 73, 95, 84, 79, 90
And you want to group them into score ranges:
60, 70, 80, 90
Place those bins in B2:B5, then enter this formula (select C2:C6 first):
=FREQUENCY(A2:A11, B2:B5)
🎯 Press Ctrl + Shift + Enter (or Cmd + Shift + Return on Mac) to enter it as an array formula.
✅ The result will show how many scores fall into each range:
- ≤60
- 61–70
- 71–80
- 81–90
- 90
📊 Real-World Use Cases
| Scenario | FREQUENCY Use |
|---|---|
| Test scores | Group students by score range |
| Sales analytics | Count how many sales fall into price brackets |
| Age distribution | Categorize survey responses into age groups |
| Inventory control | Sort products by quantity on hand |
| Employee productivity | Track task counts in specific performance bins |
📦 It’s especially useful for building histograms and data analysis dashboards.
⌨️ Keyboard Shortcuts for FREQUENCY Function
| Action | Windows/Linux | macOS |
|---|---|---|
| Start formula entry | = | = |
| Auto-complete FREQUENCY | Type FREQUENCY + Tab | Same on Mac |
| Enter array formula | Ctrl + Shift + Enter | Cmd + Shift + Return |
| Copy down formula | Ctrl + D | Cmd + D |
| Open function helper | Shift + F3 | Fn + Shift + F3 |
💡 Excel 365 automatically supports dynamic arrays, so you can just hit Enter!
🔧 Tips to Use FREQUENCY Like a Pro
- Ensure bins_array is sorted in ascending order
- The output includes an extra row to count values above the highest bin
- Use
TRANSPOSE()if you want a horizontal output - Combine with
CHARTSto visualize your distribution as a histogram
🧠 Bonus: For dynamic charts, pair FREQUENCY() with OFFSET() and MATCH()!
🛠️ Common Errors and Fixes
| Problem | Cause | Solution |
|---|---|---|
| All zeros | Data or bin ranges are incorrect | Double-check references |
#N/A error in cells | Array size mismatch | Match number of bins + 1 in result range |
| No output after formula | Didn’t press Ctrl + Shift + Enter | Use correct array formula entry |
| Wrong counts | Overlapping or misordered bins | Ensure bins are sorted and non-overlapping |
📌 Use dynamic named ranges for flexible data analysis setups.
🔗 Related Functions
| Function | Usage |
|---|---|
COUNTIFS() | For multi-criteria count with conditions |
MATCH() | Find the position of a value within a range |
INDEX() | Retrieve data based on position from FREQUENCY output |
TRANSPOSE() | Convert vertical results to horizontal (and vice versa) |
IF() + FREQUENCY | Apply conditional logic after grouping |
💼 Combine these for powerful Excel automation and reporting tools.
💬 FAQs About the FREQUENCY Function in Excel
What does the FREQUENCY function do in Excel?
It returns a distribution count of how many values fall within specified ranges.
Do I need to press Ctrl + Shift + Enter?
Yes, unless you’re using Excel 365 or Excel Online, which support dynamic arrays.
Can I use text data with FREQUENCY?
No — FREQUENCY() only works with numeric values.
How do I create a histogram using FREQUENCY?
First use FREQUENCY(), then insert a Column Chart from the output.
Does FREQUENCY work on Excel Online or Mobile?
Yes — it works, but entering array formulas on mobile may be limited.
How many bins can I use?
There’s no set limit — but make sure your output range matches your bins + 1.
🟢 Final Thoughts: How to Use the FREQUENCY Function in Excel
The FREQUENCY function in Excel 📊 is a smart, powerful way to summarize large datasets and group values into ranges. Whether you’re analyzing exam results, segmenting customer data, or reviewing performance metrics, FREQUENCY() helps you visualize patterns and act with clarity.
Complete List of Windows Keyboard Shortcuts
If you need help for Windows, you can find a whole list of all keyboard shortcuts here.
https://keyboard-shortcuts.org/
Advertisment