How to How to Use FREQUENCY Function in Excel in Excel - Practical Guide

Learn to use the FREQUENCY function to count how many values fall within specified ranges or bins. This powerful array formula is essential for creating histograms, analyzing data distribution, and summarizing large datasets into meaningful frequency tables without manual counting.

Why This Matters

FREQUENCY enables rapid data analysis and visualization by automatically grouping values into ranges, saving hours of manual categorization for business analysts and data professionals.

Prerequisites

•Basic understanding of Excel formulas and cell references
•Familiarity with array formulas or Ctrl+Shift+Enter entry method
•Knowledge of how data ranges and bins work

Step-by-Step Instructions

Organize your data and create bins

Place your data values in one column (e.g., A2:A100) and your bin ranges in another column (e.g., C2:C10). Bins are the upper limits of each range you want to count.

Select the output range

Click on the first cell where results will appear (e.g., D2). Select a range equal to the number of bins (if you have 5 bins, select D2:D6).

Enter the FREQUENCY formula

Type: =FREQUENCY(A2:A100,C2:C10) where the first range is your data and the second is your bins. Do not press Enter yet.

Confirm as array formula

Press Ctrl+Shift+Enter (not just Enter) to enter it as an array formula. Excel will display curly braces {} around the formula, indicating it's an array formula.

Review the frequency results

The results column now shows how many values fall below or equal to each bin value. Each cell represents the count for that specific range.

Alternative Methods

Using COUNTIFS for flexibility

Use COUNTIFS with multiple criteria to count values within ranges without array formulas. This approach is more intuitive for beginners but requires more individual formulas.

Using pivot tables for visual summaries

Create pivot tables (Insert > Pivot Table) to automatically group and count data ranges. Ideal for large datasets and when you need multiple perspectives on the same data.

Tips & Tricks

✓Always include one extra bin at the end to catch any values above your highest bin threshold
✓Arrange bins in ascending order for accurate counting and logical interpretation
✓Copy your frequency results and paste as values (Paste Special > Values) before sharing to prevent formula recalculation

Pro Tips

★Combine FREQUENCY with CHART (Insert > Chart) to instantly visualize data distribution as a histogram
★Use named ranges for bins and data to make your formulas more readable and maintainable
★For dynamic ranges, use OFFSET or INDIRECT functions with FREQUENCY to automatically expand with new data

Troubleshooting

FREQUENCY returns #N/A error

Ensure you've entered the formula using Ctrl+Shift+Enter and that your output range size matches the number of bins exactly.

Results show 0 for all bins

Verify that your data range contains actual numeric values and that bin values are comparable (same format/type) to your data.

Formula shows without curly braces

Delete the formula, reselect the output range, type it again, and press Ctrl+Shift+Enter instead of just Enter.

Related Excel Formulas

COUNTIF COUNTIFS SUMIF HISTOGRAM PERCENTILE

Frequently Asked Questions

Can FREQUENCY work with text data?

No, FREQUENCY only works with numeric values. If you need to count text categories, use COUNTIF or pivot tables instead.

What's the difference between FREQUENCY and COUNTIF?

FREQUENCY counts values within ranges using bins in a single formula, while COUNTIF counts individual criteria one at a time. FREQUENCY is more efficient for multiple ranges.

Do I always need Ctrl+Shift+Enter?

Yes, FREQUENCY is an array formula and requires Ctrl+Shift+Enter to function properly. In Excel 365, this may sometimes be automatic, but it's best practice to use it consistently.