Most Frequent Value (MODE)

All versionsMODE

What value shows up the most? MODE finds the most frequent number; a tiny INDEX/MATCH combo does the same for text — the most common category, response, or label.

Quick formula: for numbers in B2:B20:

=MODE(B2:B20)

Returns the value that appears most often. For text, use the COUNTIF + INDEX/MATCH approach below.

Functions used (tap for the full reference guide):

MODE INDEX

The example

Survey ratings — the most common score.

	A	B
1	Response	Rating
2	R1	4
3	R2	5
4	R3	4
5	Most frequent	4

The formula

The most frequent number:

=MODE(B2:B20) // 4 appears most → 4

How it works

MODE scans for the value with the highest frequency:

MODE(range) returns the single most frequently occurring number in the range.
If several values tie, it returns the first one encountered — not all of them.
For text, MODE won’t work. Use =INDEX(range, MATCH(MAX(COUNTIF(range,range)), COUNTIF(range,range), 0)) — an array formula that finds the most common label.
In Excel 365, MODE has a modern sibling MODE.SNGL (single) and MODE.MULT (all tied modes as a spill).

Most common text, the easy way: if you have the dynamic-array functions, =INDEX(SORTBY(UNIQUE(rng), COUNTIF(rng,UNIQUE(rng)), -1), 1) returns the most frequent label without an array entry. Otherwise the COUNTIF/INDEX/MATCH array formula works in every version.

Try it: interactive demo

Live demo

Enter values (numbers or words), one per line.

Most frequent:

Variations

All tied modes (365)

Spills every most-frequent value:

=MODE.MULT(B2:B20)

Most frequent text (array)

Ctrl+Shift+Enter in older Excel:

=INDEX(A2:A20, MATCH(MAX(COUNTIF(A2:A20,A2:A20)), COUNTIF(A2:A20,A2:A20), 0))

How many times it occurs

Count the mode:

=COUNTIF(B2:B20, MODE(B2:B20))

Pitfalls & errors

MODE errors with no repeats. If every value is unique, MODE returns #N/A. Wrap with IFERROR if that’s possible.

Ties pick the first. MODE/MODE.SNGL return only one value even when several tie. Use MODE.MULT to see them all.

Numbers only. MODE ignores text and blanks; for the most common label you need the COUNTIF/INDEX approach.

Practice workbook

📊

Download the free Most Frequent Value (MODE) practice workbook

A mode sheet with numeric MODE, the text array formula, count-of-mode, and MODE.MULT variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I find the most frequent value in Excel?

For numbers, use =MODE(range). For text, use the array formula =INDEX(range, MATCH(MAX(COUNTIF(range,range)), COUNTIF(range,range), 0)).

What does MODE return when there's a tie?

It returns the first most-frequent value encountered. Use MODE.MULT (Excel 365/2010+) to return all tied modes as a spilled array.

Why does MODE give a #N/A error?

There are no repeated values — every entry is unique, so there's no mode. Wrap it in IFERROR if that case is possible.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: MODE · INDEX