Statistical

Excel formula for Most frequent value (mode)

Find the value that appears most often in a range of numbers.

Copy this formula

=MODE.SNGL(A2:A100)

How the formula works

MODE.SNGL returns the single most common number. For text or the most common item including text, use INDEX/MATCH on the highest COUNTIF. MODE.MULT returns all modes when there are ties.

Examples

=MODE.SNGL(B2:B100)→4

4 is the most common rating.

Other ways to do this

Most common text

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

Works for text; enter as an array in older Excel.

FAQ

MODE returns #N/A — why?

No value repeats, or the range is text. Use the COUNTIF approach for text data.

Related formulas

Median value Count how many times a value repeats

Functions used

MODE.SNGL INDEX MATCH

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.

Try ExcelGPT free