How to Calculate the Mode in Excel

Key Insights

• Use MODE.SNGL for most scenarios where you expect a single most-frequent value; use MODE.MULT when your data might have multiple modes (bimodal or multimodal distributions)
• All Excel mode functions ignore text and empty cells, but return #N/A when no value repeats—always wrap with IFERROR for production spreadsheets
• Combine mode calculations with COUNTIF to validate results and understand the strength of your mode (a mode appearing 50 times is more meaningful than one appearing twice)

Introduction to Mode

Mode is the simplest measure of central tendency to understand: it’s the value that appears most frequently in your dataset. Unlike mean (average) and median (middle value), mode doesn’t require any arithmetic—it’s purely about counting occurrences.

When should you use mode instead of mean or median? Mode excels in specific scenarios:

Categorical data analysis. You can’t calculate the mean of shirt sizes (S, M, L, XL), but you can find the most popular size. Mode is the only central tendency measure that works with non-numeric data.

Identifying popular choices. What’s the most common purchase amount? Which response appears most often in surveys? Mode answers these questions directly.

Detecting data patterns. A dataset with two modes (bimodal) often indicates two distinct groups in your data—valuable information that mean and median would obscure.

Discrete data with clusters. When analyzing discrete values like test scores, ratings, or counts, mode shows you where data naturally clusters.

The practical limitation of mode is that it can be unstable with small datasets or continuous data where values rarely repeat. A dataset of 20 precise measurements might have no mode at all. Understanding when mode provides meaningful insight versus noise is crucial for effective analysis.

MODE.SNGL Function (Single Mode)

MODE.SNGL is your go-to function for finding the most frequently occurring value in a dataset. It was introduced in Excel 2010 to replace the older MODE function with clearer naming.

Syntax:

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

The function accepts up to 255 arguments, which can be individual values, cell references, or ranges. Here’s a practical example with sales data:

| A          | B              |
|------------|----------------|
| Product    | Daily Sales    |
| Widget A   | 45             |
| Widget B   | 32             |
| Widget C   | 45             |
| Widget D   | 28             |
| Widget E   | 45             |
| Widget F   | 32             |
| Widget G   | 51             |
| Widget H   | 45             |
| Widget I   | 32             |
| Widget J   | 28             |

=MODE.SNGL(B2:B11)
Result: 45

In this sales dataset, 45 appears four times, 32 appears three times, and 28 appears twice. MODE.SNGL correctly returns 45 as the most frequent value.

Critical limitation: When multiple values tie for the highest frequency, MODE.SNGL returns only the first mode encountered in the data. It won’t warn you that other modes exist. If your analysis requires identifying all modes, you need MODE.MULT instead.

You can also use MODE.SNGL with non-contiguous ranges or multiple arguments:

=MODE.SNGL(B2:B11, D2:D11, F2:F11)

This calculates the mode across three separate columns—useful when your data is spread across different areas of a worksheet.

MODE.MULT Function (Multiple Modes)

Real-world data often has multiple modes. Survey responses might cluster around two popular options. Customer purchase amounts might peak at both $9.99 and $19.99. MODE.MULT handles these bimodal and multimodal distributions.

Syntax:

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

The key difference from MODE.SNGL: this function returns an array of all modes, not just one value. In modern Excel (365 and 2021), it spills results automatically into multiple cells.

Consider survey data where respondents rate satisfaction from 1 to 5:

| A              | B       |
|----------------|---------|
| Respondent     | Rating  |
| R001           | 4       |
| R002           | 2       |
| R003           | 5       |
| R004           | 2       |
| R005           | 4       |
| R006           | 3       |
| R007           | 4       |
| R008           | 2       |
| R009           | 5       |
| R010           | 4       |
| R011           | 2       |
| R012           | 5       |

=MODE.MULT(B2:B13)

This returns a vertical array with two values: 4 and 2 (both appear 4 times). The results spill into adjacent cells automatically.

For older Excel versions (2010-2019), you need to enter MODE.MULT as a legacy array formula:

1. Select a vertical range of cells (estimate how many modes you might have)
2. Type the formula
3. Press Ctrl+Shift+Enter instead of just Enter

Excel wraps the formula in curly braces {=MODE.MULT(B2:B13)} to indicate it’s an array formula. Extra cells in your selected range will show #N/A—that’s normal and indicates no additional modes exist.

Practical tip: If you’re unsure whether your data has multiple modes, use MODE.MULT by default. When only one mode exists, it returns a single value just like MODE.SNGL. You lose nothing by using the more flexible function.

Legacy MODE Function

The original MODE function still works in all Excel versions for backward compatibility. If you’re maintaining older spreadsheets or need formulas that work across Excel 2007 and earlier, you’ll encounter this function.

Syntax:

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

Functionally, MODE behaves identically to MODE.SNGL:

| A          | B           |
|------------|-------------|
| Month      | Defects     |
| January    | 3           |
| February   | 5           |
| March      | 3           |
| April      | 7           |
| May        | 3           |
| June       | 5           |

=MODE(B2:B7)
Result: 3

=MODE.SNGL(B2:B7)
Result: 3

Both formulas return identical results. Microsoft recommends using MODE.SNGL in new work because the naming clearly indicates it returns a single value, making your spreadsheet’s intent more readable.

Compatibility notes:

• MODE works in Excel 97 through current versions
• MODE.SNGL and MODE.MULT require Excel 2010 or later
• If sharing spreadsheets with users on older Excel versions, stick with MODE
• Google Sheets supports all three functions

When converting legacy spreadsheets, there’s no urgent need to replace MODE with MODE.SNGL—they’re functionally equivalent. However, if you’re adding new mode calculations to an old spreadsheet, consider whether maintaining consistency with existing formulas outweighs the clarity benefit of newer function names.

Handling Edge Cases

Mode calculations fail in predictable ways. Defensive formulas handle these scenarios gracefully instead of displaying ugly error values to users.

No repeating values: When every value in your dataset is unique, no mode exists. All mode functions return #N/A:

=MODE.SNGL({1,2,3,4,5})
Result: #N/A

Empty cells and text: Mode functions silently ignore empty cells and text values. This is usually desirable behavior:

| A      |
|--------|
| 10     |
| hello  |
| 10     |
|        |
| 15     |

=MODE.SNGL(A1:A5)
Result: 10

The function correctly identifies 10 as the mode, ignoring “hello” and the empty cell.

Wrapping with IFERROR: For production spreadsheets, always handle the #N/A case:

=IFERROR(MODE.SNGL(D1:D25), "No mode found")

You can return alternative values based on your needs:

=IFERROR(MODE.SNGL(D1:D25), AVERAGE(D1:D25))

This falls back to the mean when no mode exists—useful for automated reports where blank cells cause downstream problems.

For MODE.MULT with IFERROR, wrap the entire array result:

=IFERROR(MODE.MULT(D1:D25), "No repeating values")

In dynamic array Excel, this returns a single cell with the error message when no mode exists, or the full array of modes when they exist.

Practical Application: Frequency Analysis

Mode tells you what value appears most often, but not how often. Combining mode with COUNTIF creates more informative analysis.

Validating mode results:

=COUNTIF(B2:B50, MODE.SNGL(B2:B50))

This counts how many times the mode appears. A mode that appears 25 times out of 50 values is statistically significant. A mode that appears twice out of 50 is barely meaningful—your data might effectively have no mode.

Building a frequency summary:

| E                  | F                                    |
|--------------------|--------------------------------------|
| Metric             | Value                                |
| Mode               | =MODE.SNGL(B2:B50)                   |
| Mode Frequency     | =COUNTIF(B2:B50, E2)                 |
| Total Values       | =COUNT(B2:B50)                       |
| Mode Percentage    | =E3/E4                               |

Format E5 as a percentage to show what proportion of your data equals the mode. This gives stakeholders context about how dominant the mode actually is.

Identifying all value frequencies:

For complete frequency analysis, combine UNIQUE and COUNTIF:

| G                        | H                          |
|--------------------------|----------------------------|
| =UNIQUE(B2:B50)          | =COUNTIF(B2:B50, G2#)      |

The G2# reference (spill range) counts occurrences of each unique value. Sort this by column H to see your full frequency distribution, with the mode(s) at the top.

Summary

Choose MODE.SNGL for straightforward analysis, MODE.MULT when investigating distributions, and always wrap production formulas with IFERROR to handle datasets where no value repeats.