How to Use the Excel MODE Function to Find the Most Frequently Occurring Number

Beyond the Average

When analyzing a dataset in Excel, most users immediately reach for the AVERAGE function to find the central tendency. While averages are useful, they can be highly misleading if your data contains extreme outliers.

For example, if you sell ten pairs of shoes at $50 each, and one luxury pair at $1,000, the “average” sale price is $136. If you stock your store based on that average, you will stock the wrong shoes, because the vast majority of your customers actually want a $50 shoe.

To find the most common, popular, or frequently occurring value in a dataset, you must use the MODE function.

Step 1: Understanding MODE.SNGL

In older versions of Excel (2007 and earlier), the function was simply MODE. While MODE still works for backward compatibility, Microsoft replaced it with a more precise function called MODE.SNGL (Mode Single) in modern versions of Excel.

MODE.SNGL looks at a range of numbers and returns the single value that appears the most often.

The Syntax:
=MODE.SNGL(number1, [number2], ...)

Usually, you just highlight a range. For example:
=MODE.SNGL(B2:B50)

If column B contains the list of shoe sale prices (ten $50s and one $1000), =MODE.SNGL(B2:B50) will return 50. This instantly tells you that $50 is the most popular price point, completely ignoring the skewed $1,000 outlier.

Step 2: Handling the #N/A Error

One of the most common frustrations with the MODE function is encountering the #N/A error.

Unlike AVERAGE or MEDIAN, MODE requires repetition to work. If you run MODE.SNGL on a dataset where every single number is unique (e.g., 10, 20, 30, 40, 50), there is no most frequent number.

When no duplicate values exist in the selected range, Excel will output #N/A. If you are building a dashboard and want to hide this ugly error, you can wrap the function in IFERROR:

=IFERROR(MODE.SNGL(B2:B50), "No Duplicates")

Step 3: Dealing with Multiple Modes (Ties)

What happens if your dataset has a tie? Imagine a classroom test where five students scored 85, and five students scored 92.

If you use MODE.SNGL, Excel will simply return 85, because it is the first mode it encountered in the dataset. It completely hides the fact that 92 is equally frequent.

To solve this, modern Excel includes the MODE.MULT (Mode Multiple) function.

Because it might need to return multiple answers, MODE.MULT is an array function. If you are using Excel 365 or Excel 2021, which support Dynamic Arrays, you simply type the formula into one cell:

=MODE.MULT(C2:C50)

If there is a tie between 85 and 92, Excel will automatically “spill” the results into the cells below, showing both 85 and 92 in a vertical list.

Step 4: Using MODE on Text Values

A critical limitation of the MODE function is that it only works on numbers.

If you have a column of text—such as customer feedback categorizing a service as “Excellent”, “Good”, or “Poor”—and you try to use =MODE.SNGL(D2:D50), Excel will return an error.

To find the most frequently occurring word or text string in a list, you must combine INDEX, MATCH, and MODE into a clever array formula:

=INDEX(D2:D50, MODE.SNGL(MATCH(D2:D50, D2:D50, 0)))

How this works:

1. The MATCH function looks at the text and translates the words into numerical row positions (e.g., turning “Excellent” into the number 1 everywhere it appears).
2. The MODE.SNGL function easily finds the most frequent number (position) in that new list.
3. The INDEX function translates that winning number back into the original text word (e.g., “Excellent”).

This allows you to extract the most frequent category or name from a massive database without needing to build complex PivotTables.