Cellwise Start free

Statistics function

Excel MODE.SNGL function

MODE.SNGL returns the number that appears most often in a list, such as the shoe size you sell most. It works on numbers only and returns #N/A when no number repeats. If two numbers tie, it returns the one that appears first; MODE.MULT returns all of them.

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

Arguments

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

MODE.SNGL examples

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

AB
1OrderShoe size
210419
310428
4104310
510449
6104511
710468
810479
910487
10104910
1110509

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

  1. =MODE.SNGL(B2:B11)

    Result: 9

    Size 9 is the most common, so it is the size to stock deepest.

  2. =COUNTIF(B2:B11,MODE.SNGL(B2:B11))

    Result: 4

    Size 9 appears 4 times out of 10 orders.

  3. =IFERROR(MODE.SNGL(A2:A11),"No repeats")

    Result: No repeats

    Order numbers never repeat, so MODE.SNGL would return #N/A. IFERROR shows a message instead.

  4. =MODE.SNGL(B2:B11)-MEDIAN(B2:B11)

    Result: 0

    Here the mode and the median are both 9, so the difference is 0. On skewed data they can be far apart.

How to use MODE.SNGL

  1. Click the cell for the result.
  2. Type =MODE.SNGL( and select the numbers.
  3. Press Enter.
  4. Wrap it in IFERROR if the data might have no repeats.

Common MODE.SNGL mistakes

Using it on text

MODE.SNGL ignores text, so on a column of product names it returns #N/A. For the most common text use INDEX, MATCH and COUNTIF together.

Missing a tie

If 8 and 9 both appeared four times, MODE.SNGL would return only the one listed first. Use MODE.MULT to see every tied value.

Decimals that only look equal

Values that round to the same display can still differ, so they never count as repeats. Round them with ROUND first.

MODE.SNGL FAQ

What is the difference between MODE and MODE.SNGL?

None in the result. MODE is the older name; MODE.SNGL was added in Excel 2010 alongside MODE.MULT.

Why does MODE.SNGL return #N/A?

No number appears more than once, or the range has no numbers at all, for example numbers stored as text.

How do I find the most common text value in Excel?

Use =INDEX(A2:A11,MATCH(MAX(COUNTIF(A2:A11,A2:A11)),COUNTIF(A2:A11,A2:A11),0)). In Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter.

Guides that use MODE.SNGL

Related functions

Updated