Need to find the most frequently occurring number in a dataset? MODE returns the most common value, making it perfect for identifying typical values, popular choices, or recurring patterns. It’s the third pillar of central tendency alongside AVERAGE and MEDIAN.
What Makes MODE Useful
MODE reveals the most common value:
- Find patterns – Identify the most frequent score, price, or quantity
- Popular choices – See which option appears most often
- Quality control – Spot the typical measurement or defect count
- Survey analysis – Find the most common response in numeric surveys
- Inventory patterns – Identify most frequently ordered quantities
The Syntax
=MODE.SNGL(number1, [number2], ...)Number1, number2, etc. are the values you want to analyze. MODE.SNGL returns the single most common value.
Note: Use MODE.SNGL in modern Excel. The old MODE function still works but MODE.SNGL is preferred.
Example 1: Most Common Test Score
Find the score that appears most frequently:
| Student | Score |
|---|---|
| Alice | 85 |
| Bob | 92 |
| Carol | 85 |
| David | 88 |
| Emma | 85 |
| Frank | 92 |
Formula: =MODE.SNGL(B2:B7)
Result: 85
Three students scored 85, making it the mode. It appears more than any other score.
Example 2: Most Common Order Quantity
Find the typical order size:
| Order | Quantity |
|---|---|
| 001 | 10 |
| 002 | 25 |
| 003 | 10 |
| 004 | 50 |
| 005 | 10 |
| 006 | 25 |
Formula: =MODE.SNGL(B2:B7)
Result: 10
Quantity 10 appears three times, more than any other value.
Example 3: Shoe Size Inventory
Identify the most popular shoe size:
| Customer | Size |
|---|---|
| 1 | 9 |
| 2 | 10 |
| 3 | 9 |
| 4 | 11 |
| 5 | 9 |
| 6 | 10 |
| 7 | 9 |
Formula: =MODE.SNGL(B2:B8)
Result: 9
Size 9 appears four times, indicating highest demand.
Example 4: Defect Count Analysis
Find the most common defect count per batch:
| Batch | Defects |
|---|---|
| A | 2 |
| B | 1 |
| C | 2 |
| D | 3 |
| E | 2 |
| F | 1 |
Formula: =MODE.SNGL(B2:B7)
Result: 2
Two defects per batch is the most common occurrence.
Example 5: No Mode Exists
What happens when no value repeats?
| Day | Sales |
|---|---|
| Mon | 100 |
| Tue | 150 |
| Wed | 200 |
| Thu | 250 |
| Fri | 300 |
Formula: =MODE.SNGL(B2:B6)
Result: #N/A
No value repeats, so there’s no mode. MODE returns an error.
Example 6: Multiple Ranges
Find mode across different data sets:
| Q1 Sales | Q2 Sales |
|---|---|
| 5 | 7 |
| 8 | 5 |
| 5 | 9 |
| 12 | 5 |
Formula: =MODE.SNGL(A2:A5, B2:B5)
Result: 5
Combines both quarters and finds 5 appears most frequently (4 times total).
MODE vs AVERAGE vs MEDIAN
| Scenario | Best Function | Why |
|---|---|---|
| Test scores (no outliers) | AVERAGE | Balanced distribution |
| Income data | MEDIAN | Resistant to outliers |
| Shoe sizes to stock | MODE | Most common = highest demand |
| Survey ratings | MODE | Most popular choice matters |
| Home prices | MEDIAN | Outliers distort average |
MODE Variations
| Function | Purpose |
|---|---|
| MODE.SNGL | Returns single most common value |
| MODE.MULT | Returns array of all modes (if multiple exist) |
| MODE | Old version, works like MODE.SNGL |
What MODE Includes
Considered:
- All numbers in the range
- Negative numbers
- Zero
- Duplicates (that’s the point!)
Ignored:
- Text
- Empty cells
- Logical values (TRUE/FALSE)
- Error values
Common Use Cases
Retail Inventory Stock more of the most commonly purchased size or quantity.
Manufacturing Identify typical production output or defect rates.
Survey Analysis Find the most popular numeric response.
Quality Control Determine normal measurement values.
Practical Applications
Compare to average:
=MODE.SNGL(B2:B10) vs =AVERAGE(B2:B10)Shows most common vs average value.
Check if value is the mode:
=IF(B2=MODE.SNGL($B$2:$B$10), "Most Common", "")Count mode frequency:
=COUNTIF(B2:B10, MODE.SNGL(B2:B10))Shows how many times the mode appears.
Common Mistakes to Avoid
Expecting Mode for Unique Values
- If all values are unique, MODE returns #N/A
- Check for duplicates before using MODE
- Use IFERROR to handle this gracefully
Confusing with MEDIAN
- MODE finds most common value
- MEDIAN finds middle value
- They measure different things
Text Values
- MODE ignores text completely
- “5” as text won’t be counted
- Check cell formatting if numbers don’t register
Error Handling
Handle no mode gracefully:
=IFERROR(MODE.SNGL(B2:B10), "No repeating values")Check if mode exists:
=IF(ISNUMBER(MODE.SNGL(B2:B10)), MODE.SNGL(B2:B10), "No mode")Minimum occurrences check:
=IF(COUNTIF(B2:B10, MODE.SNGL(B2:B10))>=3, MODE.SNGL(B2:B10), "Not frequent enough")MODE.MULT for Multiple Modes
Sometimes two or more values tie for most common:
| Value |
|---|
| 5 |
| 5 |
| 8 |
| 8 |
| 12 |
Formula: =MODE.MULT(A2:A6)
Result: Returns both 5 and 8 (array formula)
Both appear twice. MODE.MULT shows all modes.
Combining with Other Functions
Most common value above threshold:
=MODE.SNGL(IF(B2:B10>50, B2:B10))(Array formula in older Excel)
Mode of absolute values:
=MODE.SNGL(ABS(B2:B10))Mode by category (requires helper column):
=MODE.SNGL(IF(A2:A10="Category1", B2:B10))When to Use MODE
Use MODE when:
- Most common value matters more than average
- Identifying popular choices or typical patterns
- Stocking inventory based on demand
- Understanding frequency distribution
- All or most values repeat
Don’t use MODE when:
- Values are all unique (returns error)
- You need average or middle value
- Working with continuous measurements
- Pattern doesn’t matter, just overall level
Start Using It
Open a spreadsheet with repeated numeric values. Select a range where some numbers appear multiple times. Type =MODE.SNGL, include your range, and press Enter. See which value appears most frequently.
MODE becomes essential when frequency matters more than average.
Questions about MODE? Want to explore MODE.MULT for multiple modes? Let’s connect.