Cellwise Start free

Excel guide · 3 min read

How to find the mode in Excel

Quick answerUse =MODE.SNGL(B2:B13) to get the number that appears most often. It returns #N/A if no number repeats, and only one value if there is a tie; =MODE.MULT(B2:B13) returns all tied modes. MODE ignores text, so for the most common word use =INDEX(D2:D13,MATCH(MAX(COUNTIF(D2:D13,D2:D13)),COUNTIF(D2:D13,D2:D13),0)).

Sample data

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

ABCDEF
1OrderSizePaymentReasonPop-up store sizes
2R-10419CardToo small8
3R-10428PayPalChanged mind10
4R-104310CardToo small7
5R-10449Apple PayDamaged9
6R-104511PayPalToo big11
7R-10468CardToo small
8R-10479PayPalWrong color
9R-10487CashToo big
10R-104910Apple PayToo small
11R-10509PayPalChanged mind
12R-10518CardToo big
13R-105212Gift cardToo small

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

MODE.SNGL for the most common number

A shoe shop logs its returns and wants to know which size comes back most. That is the mode: =MODE.SNGL(B2:B13). MODE.SNGL, and the older MODE that does the same thing, ignore empty cells, text and TRUE/FALSE in the range, but they do count zeros.

The formula below gets the same answer without MODE, and it is worth knowing because it also works for text. COUNTIF(B2:B13,B2:B13) counts how often each row's value appears. MAX picks the highest count, MATCH finds the first row with that count, and INDEX returns its value. In Excel 365 it works as typed. In Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter.

=INDEX(B2:B13,MATCH(MAX(COUNTIF(B2:B13,B2:B13)),COUNTIF(B2:B13,B2:B13),0))

Result: 9

9: size 9 is the most returned size, the same answer MODE.SNGL gives.

How often does the mode appear?

The mode alone does not say whether it is a clear winner or just one ahead. The count does. Put it next to the mode so nobody reads too much into a weak result.

=MAX(COUNTIF(B2:B13,B2:B13))

Result: 4

4: size 9 was returned 4 times out of 12. Size 8 is close behind with 3.

#N/A when nothing repeats

If every value is different there is no mode, and MODE.SNGL returns #N/A. The pop-up store's five returns in F2:F6 are all different sizes, so =MODE.SNGL(F2:F6) is #N/A. That is correct, not a broken formula. Show a message instead with =IFERROR(MODE.SNGL(F2:F6),"No repeats").

The INDEX/MATCH version has the opposite trap: when nothing repeats, every count is 1, and it quietly returns the first value as if it were the mode. Guard it by checking the top count first.

=IF(MAX(COUNTIF(F2:F6,F2:F6))=1,"No repeats",INDEX(F2:F6,MATCH(MAX(COUNTIF(F2:F6,F2:F6)),COUNTIF(F2:F6,F2:F6),0)))

Result: No repeats

No repeats: each size in F2:F6 appears once, so there is no mode to report.

The most common text value

MODE functions only look at numbers, so =MODE.SNGL(D2:D13) on the return reasons gives #N/A. The INDEX, MATCH and COUNTIF pattern works on text the same way it works on numbers. Blank cells inside the range can cause odd results, so size the range to the data or use a Table column.

For a quick look without a formula, a PivotTable works too: put Reason in Rows and again in Values (it shows as Count of Reason), then sort the count largest to smallest. That also shows the runners-up.

=INDEX(D2:D13,MATCH(MAX(COUNTIF(D2:D13,D2:D13)),COUNTIF(D2:D13,D2:D13),0))

Result: Too small

Too small: it is the most common return reason, given for 5 of the 12 returns.

Ties: MODE.MULT, and finding every mode

Card and PayPal were each used for 4 returns. A single-mode formula picks one and hides the tie. MODE.SNGL returns just one value, and the INDEX/MATCH formula returns whichever comes first in the list, here Card.

For numbers, =MODE.MULT(B2:B13) returns every tied mode as a vertical list. In Excel 365 it spills down by itself. In Excel 2010 to 2019, select several cells down first, type the formula and press Ctrl+Shift+Enter; extra cells show #N/A. MODE.MULT does not work on text either. For text in Excel 365, the formula below returns every value tied for the top count.

=UNIQUE(FILTER(C2:C13,COUNTIF(C2:C13,C2:C13)=MAX(COUNTIF(C2:C13,C2:C13))))

Result: spills 2 rows × 1 column

Card
PayPal

Card and PayPal spill into two cells: both were used 4 times, so both are modes.

Common mistakes

Running MODE.SNGL on text

It ignores text and returns #N/A. Use the INDEX, MATCH and COUNTIF formula instead.

Numbers stored as text

Sizes imported as text are skipped by MODE.SNGL. Convert them to numbers first (Data > Text to Columns > Finish works on one column).

Reporting one mode when there is a tie

Check the count of the mode and look for ties with MODE.MULT or the UNIQUE(FILTER(...)) formula before you present a single winner.

Trusting INDEX/MATCH when nothing repeats

It returns the first value when every count is 1. Wrap it in IF(MAX(COUNTIF(...))=1,"No repeats",...).

FAQ

What is the difference between MODE.SNGL and MODE.MULT?

MODE.SNGL returns one mode, even when several values tie for most frequent. MODE.MULT returns all of them as a vertical array. Both work on numbers only.

Why does MODE return #N/A in Excel?

Either no number in the range appears more than once, or the range has no numbers at all, for example because it holds text or numbers stored as text. Wrap it in IFERROR to show a message instead.

How do I find the most common text in Excel?

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

How do I find the mode with a condition?

In Excel 365 or 2021, filter first: =MODE.SNGL(FILTER(B2:B50,C2:C50="Card")). In older versions use =MODE.SNGL(IF(C2:C50="Card",B2:B50)) with Ctrl+Shift+Enter.

Functions used

Related guides

Updated