FREQUENCY Function in Excel

Always Sort Your Bins in Ascending Order

FREQUENCY requires bins to be sorted from smallest to largest for accurate results. Unsorted bins produce incorrect or unpredictable distributions. Before using FREQUENCY, verify your bins increase sequentially: 10, 20, 30, 40, not 20, 10, 40, 30. Use =SORT(BinRange) to automatically sort bins, or manually verify the sequence. This single step prevents 35% of FREQUENCY errors.

Create Dynamic Bins with SEQUENCE

In Excel 365, generate bins automatically using the SEQUENCE function for evenly-spaced intervals. Instead of manually typing bins, use: `=FREQUENCY(Data, SEQUENCE(10, 1, 10, 10))` This creates bins at 10, 20, 30...100 automatically. The syntax is SEQUENCE(rows, columns, start, step). Benefits: eliminates manual bin entry, easy to adjust ranges, consistent spacing guaranteed. Advanced: combine with MIN/MAX for data-driven bin ranges: `=FREQUENCY(Data, SEQUENCE(10, 1, MIN(Data), (MAX(Data)-MIN(Data))/10))`

Convert to Percentages for Better Comparisons

Divide FREQUENCY results by total count to get percentage distributions instead of raw counts: `=FREQUENCY(Data, Bins) / COUNT(Data)` Then format cells as percentages. This makes comparing datasets of different sizes much easier. For example, comparing test results from a class of 20 vs. a class of 30 is clearer with percentages (30% got A's) rather than raw counts (6 students vs. 9 students). Percentage distributions also create clearer, more professional visualizations in charts.

Remember: FREQUENCY Returns n+1 Values for n Bins

This is the most commonly forgotten rule. FREQUENCY always returns one more value than the number of bins because the last cell counts all values ABOVE your highest bin. If you have 5 bins, you need 6 output cells. The extra cell captures outliers and values exceeding your maximum bin. **Common mistake:** Selecting only 5 cells for 5 bins causes #N/A errors in legacy Excel. **Correct approach:** 5 bins = 6 output cells. Always add 1 to your bin count when selecting the output range.

Combine with Charts for Professional Histograms

Use FREQUENCY output as the data source for column charts to create custom histograms: 1. Generate frequency distribution with FREQUENCY 2. Select frequency results and corresponding bin labels 3. Insert → Charts → Clustered Column Chart 4. Format: Remove gaps between columns (Gap Width = 0%) 5. Customize axes, colors, and titles This gives you complete control over bin ranges and appearance, unlike Excel's automatic histogram chart which chooses bins for you. Professional histograms are essential for presentations and reports.

Platform Differences: Legacy vs. Dynamic Arrays

**Excel 2019 and earlier (Legacy method):** 1. Select output range (bins + 1 cells) 2. Type formula 3. Press Ctrl+Shift+Enter 4. Curly braces appear: {=FREQUENCY(...)} **Excel 365/2021 and Google Sheets (Dynamic method):** 1. Type formula in single cell 2. Press Enter 3. Results auto-spill with blue border 4. No curly braces, no pre-selection **Compatibility tip:** If sharing workbooks with users on older Excel versions, use the legacy method to ensure compatibility. Dynamic arrays won't work in Excel 2019 or earlier.