How to Find Every Mode with MODE.MULT

When several values tie for most common, MODE.MULT spills them all — no tied answer gets silently dropped.

You will learn to

• List every mode with MODE.MULT
• Count how many modes a list has
• Turn the spilled modes into one line of text

How it works

MODE.SNGL returns just one most-common value, even when two or three values are tied. MODE.MULT returns all of them as a vertical array that spills down from the formula cell, in the order each value first appears in the data.

Wrap MODE.MULT in COUNT to see how many modes there are, or in TEXTJOIN(", ",TRUE,MODE.MULT(range)) to show them in a single cell. Like MODE.SNGL it returns #N/A when no value repeats.

=MODE.MULT(B2:B11)