Cellwise Start free

Statistics function

Excel MODE.MULT function

MODE.MULT returns every number that is tied for most frequent, as a vertical list. When a survey question gets as many 7s as 8s, MODE.SNGL shows only the first; MODE.MULT shows both. In Excel 365 the list spills down by itself.

=MODE.MULT(number1, [number2], ...)

Arguments

number1
The first number, cell or range.
number2
Optional. More numbers or ranges. Text, TRUE/FALSE and empty cells are ignored.

MODE.MULT examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

AB
1RespondentRating (1-10)
2R-017
3R-028
4R-036
5R-048
6R-059
7R-067
8R-075
9R-088
10R-097
11R-1010

Download this sheet as an Excel practice file (.xlsx)

  1. =MODE.MULT(B2:B11)

    Result: spills 2 rows × 1 column

    7
    8

    Ratings 7 and 8 each appear three times, so both spill down the column.

  2. =TEXTJOIN(", ",TRUE,MODE.MULT(B2:B11))

    Result: 7, 8

    Joins the modes into one cell: 7, 8.

  3. =ROWS(MODE.MULT(B2:B11))

    Result: 2

    Counts the tied modes: 2. A result of 1 means there is a single clear mode.

  4. =TRANSPOSE(MODE.MULT(B2:B11))

    Result: spills 1 row × 2 columns

    78

    MODE.MULT always returns a column. TRANSPOSE turns it into a row.

How to use MODE.MULT

  1. Click a cell with empty cells below it.
  2. Type =MODE.MULT( and select the numbers.
  3. Press Enter. In Excel 365 the modes spill down.
  4. In Excel 2010 to 2019, select several cells down first and press Ctrl+Shift+Enter; spare cells show #N/A.

Common MODE.MULT mistakes

No room to spill

If a cell below the formula is not empty, the result is #SPILL!. Clear the cells under it.

Expecting text modes

MODE.MULT works on numbers only. For text, compare COUNTIF counts with their MAX and FILTER the matches.

No repeats

When every number appears once, MODE.MULT returns #N/A, the same as MODE.SNGL. Wrap it in IFERROR for a message.

MODE.MULT FAQ

How do I find multiple modes in Excel?

Use =MODE.MULT(range). It returns every number tied for the highest count as a list.

Can MODE.MULT return a row?

Not directly; it always returns a column. Use =TRANSPOSE(MODE.MULT(range)) for a row.

How do I show all modes in one cell?

Use =TEXTJOIN(", ",TRUE,MODE.MULT(range)). TEXTJOIN needs Excel 2019 or later.

Guides that use MODE.MULT

Related functions

Updated