MODE function
MODE returns the most frequently occurring value in a data set. It is a compatibility function; MODE.SNGL and MODE.MULT replace it in new workbooks, and MODE.MULT lists ties.
Example
Read it as a sentence: find the number that appears most often in A1:A8, which gives 4.
Excel shows 4
A
1
2
2
4
3
4
4
4
5
5
6
5
7
7
8
9
Data
Syntax
MODE(number1, [number2], ...)
Arguments in [square brackets] are optional.
-
number1(required) - A number, range or array.
-
number2(optional) - More values or ranges.
Returns: The most frequent value.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=MODE({2,4,4,4,5,5,7,9}) | 4 | Array constant |
=MODE({1,1,2,2,3}) | 1 | Tie between modes |
=MODE(Data!A1:A8) | 4 | Range of numbers |
=MODE(Data!A1:A4,Data!F1:F5) | 4 | Two ranges |
=MODE(10,"20",TRUE) | #N/A | Direct args: number, text-number, TRUE |
=MODE(10,"abc") | #N/A | Direct arg: non-numeric text |
=MODE(5) | #N/A | Single number |
=MODE({1,"2",TRUE,3}) | #N/A | Array with text and boolean |
The Data sheet these formulas read
A cell that starts with = holds that formula.
A
F
1
2
1
2
4
2
3
4
3
4
4
4
5
5
5
6
5
7
7
8
9
Data
Watch out for
- Superseded by MODE.SNGL / MODE.MULT; use that in new workbooks.
- Returns #N/A when nothing repeats.
- With ties it returns only the one that appears first; MODE.MULT lists them all.
Where it works
- Excel
- MODE is in every version of Excel from 2007 on. Compatibility function: still works in every version, but newer replacements exist.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Compatibility functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .