What FREQUENCY does and when to use it

The FREQUENCY function counts how many times values fall into ranges you define. You give it a list of numbers and a list of boundaries, and it tells you how many numbers land in each range. This is useful when you have raw data — test scores, sales amounts, customer ages — and you want to see the shape of that data without manually counting.

For example, if you have 50 test scores and want to know how many fall between 60–70, 71–80, 81–90, and 91–100, FREQUENCY does that counting for you in one step. The function returns an array, which means it produces multiple results at once, so the setup is slightly different from other Excel functions you may have used.

Key Takeaways

  • FREQUENCY takes two inputs: your data (the numbers to count) and your bins (the upper boundaries of each range).
  • The function returns an array, so you must enter it as an array formula using Ctrl+Shift+Enter on Windows or Cmd+Shift+Enter on Mac.
  • Each bin counts how many values are less than or equal to that boundary and greater than the previous boundary.
  • FREQUENCY works best when your bins are in order from smallest to largest, and it ignores text and blank cells.

Setting up your data and bins

Before you write the formula, arrange your data in one column and your bin boundaries in another. Your data can be anywhere — say, column A with test scores from A2 to A51. Your bins should be in a separate column, typically in order from smallest to largest. If you want ranges of 60–70, 71–80, 81–90, and 91–100, your bins would be 70, 80, 90, and 100 in cells like D2:D5.

The bins represent the upper limit of each range. FREQUENCY counts values that are less than or equal to each bin and greater than the previous bin. So a bin of 70 means "up to and including 70", and the next bin of 80 means "from 71 to 80". You do not need to type the lower boundaries — FREQUENCY figures those out automatically.

Make sure your data and bins do not overlap in the same cells, or Excel will get confused about what to count. Also, if you have headers (like "Score" in A1 and "Range" in D1), start your data range below the header.

Writing and entering the FREQUENCY formula

The formula structure is =FREQUENCY(data, bins). If your scores are in A2:A51 and your bins are in D2:D5, you would type =FREQUENCY(A2:A51, D2:D5). Do not press Enter yet — this is where array formulas differ from regular ones.

On Windows, hold Ctrl and Shift, then press Enter. On Mac, hold Cmd and Shift, then press Enter. Excel will add curly braces around the formula (like {=FREQUENCY(A2:A51, D2:D5)}) to show it is an array formula. The results will appear in a column next to your bins, one result per bin.

If you press Enter without Ctrl+Shift+Enter, Excel will only show the first result, and you will see a number in one cell instead of a list. If that happens, click the cell, press F2 to edit, then use Ctrl+Shift+Enter to fix it.

Understanding what the results mean

Each result tells you how many values fall into that range. If your bins are 70, 80, 90, 100 and your results are 8, 15, 18, 9, that means 8 scores are 70 or below, 15 scores are between 71 and 80, 18 are between 81 and 90, and 9 are between 91 and 100. The results always correspond to the bins in the same order.

FREQUENCY also counts values that fall outside your highest bin. If a score is 105 and your highest bin is 100, it still gets counted — in a row below your last bin result. So if you have four bins, you will see five results: one for each bin, plus one for anything above the highest bin. This extra row is useful for catching outliers.

Common mistakes and how to fix them

The most common mistake is forgetting to press Ctrl+Shift+Enter. If you see only one number instead of a column of results, that is the problem. Click the cell with the formula, press F2, then press Ctrl+Shift+Enter to convert it to an array formula.

Another mistake is putting your bins in the wrong order. FREQUENCY assumes bins go from smallest to largest. If you put 100, 90, 80, 70 instead of 70, 80, 90, 100, the results will not match what you expect. Rearrange your bins in ascending order and re-enter the formula.

If you have text in your data column (like "absent" or "N/A"), FREQUENCY ignores it and counts only numbers. This is usually what you want, but if you need to track non-numeric entries separately, you will need a different approach — like using COUNTIF to count text values on the side.

Using FREQUENCY with a helper column for clarity

If you want to label your ranges so the results are easier to read, create a helper column next to your bins. In one column put your bins (70, 80, 90, 100), and in the next column put labels like "60–70", "71–80", "81–90", "91–100". Then put your FREQUENCY results next to the labels. This makes it obvious what each count represents without having to remember which bin is which.

You can also use this setup to build a chart. Select your labels and your FREQUENCY results, then insert a bar or column chart. Excel will automatically use the labels as the chart axis, giving you a visual picture of how your data is distributed.

Frequently Asked Questions

What is the difference between FREQUENCY and COUNTIF?

COUNTIF counts how many cells match one specific condition, like "greater than 70". FREQUENCY counts how many values fall into multiple ranges at once. If you need to count values in just one range, COUNTIF is simpler. If you need to count across many ranges, FREQUENCY is faster.

Can I use FREQUENCY with decimal numbers?

Yes. FREQUENCY works with any numbers, including decimals. If your data is prices like 12.50, 15.75, 18.25, your bins can also be decimals like 15.00, 20.00, 25.00. The same rules apply — values less than or equal to each bin are counted in that range.

What happens if I have duplicate values in my data?

FREQUENCY counts each value separately, even if it appears multiple times. If the score 85 appears five times in your data, all five instances are counted toward the bin that includes 85. This is the correct behavior for frequency analysis.

Do my bins have to be whole numbers?

No. Your bins can be any numbers — whole, decimal, or even negative. Use whatever boundaries make sense for your data. If you are measuring temperature and want ranges like -10, 0, 10, 20, those work just as well as 70, 80, 90, 100.

Can I use FREQUENCY in a newer version of Excel without Ctrl+Shift+Enter?

In Excel 365 and Excel 2021, array formulas sometimes work without Ctrl+Shift+Enter, but FREQUENCY still requires it. To be safe, always use Ctrl+Shift+Enter when entering FREQUENCY. It works the same way across all versions and ensures your formula behaves as expected.