Excel MODE Function

MODE returns the number that appears most often in a range of values. Microsoft replaced it with MODE.SNGL and MODE.MULT starting in Excel 2010, and MODE now exists only for backward compatibility. It still calculates correctly, but it's kept out of the function autocomplete list in newer versions and could be removed from Excel entirely in a future release. If you're building a new formula, skip straight to MODE.SNGL.

Available in: All Excel versions, Excel 97 through Excel 365. Excel 2010 and later replaced MODE with MODE.SNGL and MODE.MULT; MODE itself still works everywhere but exists only for compatibility with older workbooks.

Syntax

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

Parameter	Required	Description
number1	Yes	A number, cell reference, or range to check for the most frequent value.
number2, ...	No	Up to 254 additional numbers or ranges. MODE treats all arguments as one combined data set.

Basic Example

You're a teaching assistant reviewing quiz scores for 23 students in an intro statistics course and want to know which score came up most often.

=MODE(scores)

// scores = the range B2:B24 containing each student's quiz score

If the most common score in the range is 84, MODE returns 84, regardless of how many students scored that way or where in the range they appear. It ignores everything else: how many total students there are, how spread out the scores are, whether 84 is a good score. It just finds the number that repeats most.

How MODE Works

It only returns one value

MODE returns a single number, even if the data set has multiple modes. If both 82 and 91 appear four times each, and no other value repeats more, MODE returns whichever one occurs first in the range. This is the exact limitation that MODE.MULT was built to fix: it returns every mode as an array instead of picking one arbitrarily.

It ignores text, logical values, and blanks

MODE evaluates only numbers. Text entries, TRUE/FALSE values, and empty cells inside the range are skipped entirely rather than counted as zero or as duplicates. A range mixing scores like 90, "Absent", and 85 treats "Absent" as if it weren't there.

Ties go to the first occurrence in the range

When two or more values are tied for most frequent, MODE doesn't average them or flag the tie. It scans left to right, top to bottom, and returns whichever tied value it reaches first. Reorder the data and the result can change even though the underlying values didn't.

Common Use Cases

Find the most common order quantity

An online store wants to know which order quantity customers buy most often, to use as the default quantity shown at checkout.

=MODE(order_quantities)   // returns the quantity that appears most often across all orders

Find the mode within one category

You want the most common shoe size ordered, but only within the "Running" category, not the whole catalog.

=MODE(IF(orders[Category]="Running", orders[Size]))

// orders[Category]="Running" limits the array to Running rows only
// MODE then finds the most frequent size within just that subset

Enter this with Ctrl+Shift+Enter in older Excel versions. In Excel 365, it works as a normal formula.

Confirming a mode is meaningful before reporting it

Before publishing a "most common price point" stat, you want to know how often that value actually repeats, not just that MODE found something.

=COUNTIF(prices, MODE(prices))

// MODE(prices) finds the most frequent price
// COUNTIF counts how many times that price actually appears

If COUNTIF returns 2 out of 200 prices, the "mode" is barely a pattern. A high count means the mode is worth reporting; a low one means the data doesn't really cluster around anything.

Handling Errors

MODE most commonly returns #N/A, not a calculation error, which trips people up because #N/A usually signals a lookup problem.

Common causes of #N/A:

• No value in the range repeats — every number is unique
• The range contains only text or blank cells, leaving nothing numeric to evaluate
• All numeric values were filtered or hidden out of the calculation range

=IFERROR(MODE(scores), "No repeated value")
=IFNA(MODE(scores), "")   // use IFNA since MODE specifically throws #N/A

Notes & Gotchas

Why does MODE return #N/A?

MODE returns #N/A when no value in the supplied range appears more than once. This happens often with continuous data like measurements or prices, where duplicate values are rare. Check whether your data set realistically has repeats before assuming the formula is broken. Sometimes there just isn't a mode.

Does MODE work with dates?

Yes. Dates are stored as serial numbers in Excel, so MODE treats them like any other numeric value and returns the date that occurs most often. Format the result cell as a date, since MODE will return the raw serial number by default.

What's the difference between MODE and MODE.SNGL?

Functionally, nothing. MODE.SNGL takes identical arguments and returns identical results. It's a direct rename introduced in Excel 2010 to organize statistical functions into clearer families. The only reason to still use MODE is compatibility with a workbook built for Excel 2007 or earlier.

Should I use MODE in a new workbook?

No. Use MODE.SNGL if you want a single mode, or MODE.MULT if your data might have more than one. Both are the current, supported versions, and MODE.MULT solves the multi-mode limitation that MODE can't handle at all.

Related Functions

Function	Use this when...
MODE.SNGL	You're building a new formula and want a single mode — the direct, currently supported replacement for MODE.
MODE.MULT	Your data might have more than one mode and you want all of them returned, not just the first.
MEDIAN	You need the middle value of a data set instead of the most frequent one.
AVERAGE	You want the arithmetic mean rather than the most common value.