MODE.SNGL returns the most frequently occurring number in a set of values. It is the modern replacement for the legacy MODE function and works with numeric data only.
=MODE.SNGL(number1, [number2], …)What does MODE.SNGL do?
MODE.SNGL is a statistical function that tells you which number appears most often in a list. If you have a column of survey responses, order quantities, or test scores, it quickly reveals the single most common value. This is useful when you want to know the typical or most popular result rather than the average.
Unlike AVERAGE, which can be pulled up or down by extreme values, the mode shows the value that actually repeats most. That makes it handy for spotting trends in real data: the most common delivery delay, the most frequent customer rating, or the size that sells best. MODE.SNGL is the current version of the old MODE function and is available in Excel 2010 and later.
Imagine you manage a small retail team and want to know which order quantity customers choose most often. Instead of eyeballing a long list, you point MODE.SNGL at the quantity column and get the answer instantly. The same approach works for attendance counts, support ticket volumes, or any numeric field where repetition matters.
When to use MODE.SNGL
- Finding the most common customer rating in a feedback survey.
- Identifying the most frequent order quantity or product size sold.
- Spotting the most common number of days for delivery or response time.
- Checking the most repeated score in a class or training assessment.
- Finding the typical number of items per transaction in sales data.
- Detecting the most common error code or ticket count in support logs.
MODE.SNGL syntax
=MODE.SNGL(number1, [number2], …)| Argument | Required? | What to enter |
|---|---|---|
number1 | Required | The first number, cell reference, or range to check. Example: B2:B10 or 5. |
number2 | Optional | Additional numbers, references, or ranges. You can add up to 255 arguments. Example: C2:C10. |
What it returns: The most frequently occurring number in the supplied values. If no number repeats, MODE.SNGL returns #N/A.
MODE.SNGL examples
1 Example 1: Most common number in a simple list
This example uses a short list of daily order counts. The goal is to find which count appears most often. The formula =MODE.SNGL(B2:B8) looks at cells B2 through B8 and counts how many times each number occurs. The number 4 appears three times, more than any other, so the formula returns 4.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Day | Orders | |||
| 2 | Mon | 4 | 4 | ||
| 3 | Tue | 6 | |||
| 4 | Wed | 4 | |||
| 5 | Thu | 7 | |||
| 6 | Fri | 4 | |||
| 7 | Sat | 5 | |||
| 8 | Sun | 6 | |||
| 9 |
2 Example 2: Most common customer rating
Here a small survey records ratings from 1 to 5. The goal is to find the rating given most often. The formula =MODE.SNGL(B2:B9) scans the ratings in B2:B9. The value 4 appears three times, which is more than any other rating, so MODE.SNGL returns 4.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Rating | |||
| 2 | C001 | 5 | 4 | ||
| 3 | C002 | 4 | |||
| 4 | C003 | 4 | |||
| 5 | C004 | 3 | |||
| 6 | C005 | 5 | |||
| 7 | C007 | 4 | |||
| 8 | C008 | 2 | |||
| 9 | C009 | 3 | |||
| 10 |
3 Example 3: Most common delivery delay in days
This table shows delivery delays for several orders. The goal is to identify the most frequent delay so the team can focus on that issue. The formula =MODE.SNGL(C2:C9) checks the delay values in column C. The number 2 appears three times, so the function returns 2, meaning a two-day delay is the most common.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Delay (days) | |||
| 2 | 1001 | North | 1 | 2 | ||
| 3 | 1002 | South | 2 | |||
| 4 | 1003 | East | 3 | |||
| 5 | 1004 | West | 2 | |||
| 6 | 1005 | North | 1 | |||
| 7 | 1006 | South | 2 | |||
| 8 | 1007 | East | 4 | |||
| 9 | 1008 | West | 3 | |||
| 10 |
4 Example 4: Most common order quantity for a product
A product is sold in different quantities per order. The goal is to find the quantity customers order most often. The formula =MODE.SNGL(B2:B10) examines the quantities in B2:B10. The value 3 appears four times, more than any other quantity, so MODE.SNGL returns 3.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order ID | Quantity | |||
| 2 | 2001 | 2 | 3 | ||
| 3 | 2002 | 3 | |||
| 4 | 2003 | 5 | |||
| 5 | 2004 | 3 | |||
| 6 | 2005 | 2 | |||
| 7 | 2006 | 3 | |||
| 8 | 2007 | 4 | |||
| 9 | 2008 | 3 | |||
| 10 | 2009 | 5 | |||
| 11 |
Common MODE.SNGL errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #N/A | No number appears more than once in the supplied range. | Check the data for repeated values. If every value is unique, MODE.SNGL cannot return a mode. Consider using a different summary such as AVERAGE or MEDIAN. |
| #VALUE! | The range contains text that cannot be interpreted as a number, or an argument is not numeric. | Ensure all cells in the range are numeric. Remove or convert text entries, or use a range that contains only numbers. |
| #DIV/0! | The range is empty or contains no numeric values. | Point the formula at a range that contains at least one number. Check that you have selected the correct column. |
| #NAME? | The function name is misspelled, or you are using an older Excel version that does not support MODE.SNGL. | Type MODE.SNGL exactly. If you are on Excel 2007 or earlier, use the legacy MODE function instead. |
Tips for using MODE.SNGL
- Use MODE.SNGL on numeric columns only; text values are ignored but can cause errors if mixed with numbers.
- If you need to find the most common text value, use INDEX and MATCH with MODE instead, because MODE.SNGL works only with numbers.
- Combine MODE.SNGL with IFERROR to show a friendly message when no mode exists, for example
=IFERROR(MODE.SNGL(B2:B10),"No mode"). - Remember that MODE.SNGL returns only one mode. If your data has multiple values with the same highest frequency, it returns the first one it encounters.
- Use a named range or table reference to keep formulas readable and easy to update as data grows.
- Test your range for blanks and text before applying MODE.SNGL to avoid unexpected errors.
Frequently asked questions
What is the difference between MODE.SNGL and MODE?
MODE.SNGL is the modern version introduced in Excel 2010. MODE is the legacy function kept for compatibility. Both return the most frequent number, but MODE.SNGL is preferred for new work.
Can MODE.SNGL work with text or dates?
No. MODE.SNGL only evaluates numbers. Text and dates stored as text are ignored. If you need the most common text value, use a combination of INDEX, MATCH, and MODE.
Why does MODE.SNGL return #N/A?
It returns #N/A when no number repeats in the range. In other words, every value is unique, so there is no mode.
What happens if two numbers appear equally often?
MODE.SNGL returns only one mode: the one that appears first in the range. If you need all modes, you would need a more advanced array formula or a helper column.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. MODE.SNGL reference