Excel MODE Function: Find the Most Frequent Value
Returns the number that appears most often in a range.
MODE returns the value that occurs most frequently. Where average and median describe the centre of a spread, mode describes its most popular point, which is a different and sometimes more useful thing — the most common order quantity, the pay grade most people sit on, the score most students got.
It has two real limitations. It only looks at numbers, so the most frequent product name or region is not something MODE can find. And when two values tie for most frequent, MODE.SNGL returns whichever appears first in the range, silently, with no indication that there was a tie at all.
MODE.MULT is the answer to the second problem: it returns every tied value as a spilled array rather than picking one. MODE itself is the legacy name and still works; MODE.SNGL is the modern equivalent.
Syntax
=MODE.SNGL(number1, [number2], …) =MODE.MULT(number1, …)Arguments
number1Required- The first range or value. Text and blanks are ignored rather than counted.
number2, …Optional- Further ranges or values, up to 255.
The example data
Headers in row 1, data in A2:C8. Note how far F8's salary sits above the rest.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Salary | Team |
| 2 | Alice Moreau | 32000 | Support |
| 3 | Bruno Santos | 28000 | Support |
| 4 | Chen Wei | 45000 | Engineering |
| 5 | Dana Okafor | 28000 | Support |
| 6 | Erik Halls | 38000 | Engineering |
| 7 | Farah Idris | 41000 | Engineering |
| 8 | Greg Nolan | 154000 | Executive |
Worked examples
=MODE.SNGL(B2:B8)Result: 28000
The only salary appearing twice, so it is the most frequent value.
=MODE(B2:B8)Result: 28000
The legacy name, identical in behaviour. Still supported for older workbooks.
=MODE.MULT(B2:B8)Result: 28000
The array version. With a single most-frequent value it spills one cell; with a tie it spills one per tied value.
=INDEX(C2:C8, MODE.SNGL(MATCH(C2:C8, C2:C8, 0)))Result: Support
The most frequent *text*. MATCH turns each name into a position number, MODE finds the commonest position, INDEX turns it back.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#N/A
Why it happens: No value repeats — every number in the range is unique, so there is no mode.
How to fix it: Guard it: =IFERROR(MODE.SNGL(range), "No repeats"). This is expected on continuous data like prices.
Ignores text values
Why it happens: MODE only considers numbers. Text entries are skipped entirely.
How to fix it: Use the INDEX/MODE/MATCH pattern above, or COUNTIF against a UNIQUE list for the most common label.
A tie is hidden
Why it happens: MODE.SNGL returns whichever tied value appears first, without saying so.
How to fix it: Use MODE.MULT, which returns all of them, and COUNT its result if you only want to know whether there was a tie.
Tips worth knowing
- MODE.MULT spills, so give it room and reference the result with # elsewhere.
- For the most common text, =INDEX(range, MODE.SNGL(MATCH(range, range, 0))) is the classic pattern.
- With dynamic arrays, sorting a UNIQUE list by COUNTIF gives a full frequency table rather than just the top value.
- Mode is most informative on discrete data — grades, sizes, quantities — and often useless on continuous data.
Frequently asked questions
What is the difference between MODE, MODE.SNGL and MODE.MULT?
MODE is the legacy name and MODE.SNGL is its modern replacement; they behave identically and return one value. MODE.MULT returns every value tied for most frequent as a spilled array, which is the only way to see that a tie existed.
Why does MODE return #N/A?
Because nothing repeats. If every value in the range appears exactly once there is no most-frequent value, and #N/A is the correct answer rather than an error in your formula.
How do I find the most common text value?
MODE cannot do it directly. Use =INDEX(A2:A100, MODE.SNGL(MATCH(A2:A100, A2:A100, 0))), which converts each text value to the position of its first occurrence, finds the most frequent position, and reads the text back.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.