How to Use MODE in Excel
• Excel offers three MODE functions—MODE.SNGL returns the single most common value, MODE.MULT identifies all modes in multimodal datasets, and MODE exists for backward compatibility but should be...
Key Insights
• Excel offers three MODE functions—MODE.SNGL returns the single most common value, MODE.MULT identifies all modes in multimodal datasets, and MODE exists for backward compatibility but should be avoided in new workbooks. • MODE functions return #N/A errors when no value repeats in your dataset, making them unsuitable for analyzing unique values—always validate your data has repeated values before applying MODE. • MODE excels at finding the most frequent discrete values like product SKUs or test scores, but use MEDIAN for continuous data and skewed distributions where the most common value doesn’t represent typical behavior.
Understanding MODE Functions
MODE calculates the most frequently occurring value in a dataset—the statistical mode. Unlike AVERAGE (mean) or MEDIAN, MODE identifies which specific value appears most often, making it invaluable for categorical analysis, quality control, and understanding customer behavior patterns.
Excel provides three MODE variants. MODE.SNGL returns a single mode value even when multiple modes exist. MODE.MULT returns all mode values in multimodal distributions. The legacy MODE function behaves identically to MODE.SNGL but exists only for compatibility with Excel 2007 and earlier—avoid it in new work.
MODE functions ignore text, logical values, and empty cells. They only evaluate numeric values, which means you’ll need workarounds for analyzing text-based categorical data.
Using MODE.SNGL for Single Mode Values
MODE.SNGL returns the most frequently occurring number in your dataset. The syntax is straightforward:
=MODE.SNGL(number1, [number2], ...)
You can reference individual cells, ranges, or arrays. Here’s a practical example analyzing student test scores:
A1: Student B1: Score
A2: John B2: 85
A3: Sarah B3: 90
A4: Mike B4: 85
A5: Lisa B5: 78
A6: Tom B6: 85
A7: Emma B7: 92
A8: Chris B8: 90
B10: =MODE.SNGL(B2:B8)
Result: 85
The score 85 appears three times, more than any other value, so MODE.SNGL returns 85. This immediately tells you that 85 is the most common performance level in this group.
MODE.SNGL works best when you have a clear single mode. When multiple values tie for highest frequency, MODE.SNGL returns the first mode it encounters in the dataset—which may not be the behavior you want. That’s where MODE.MULT becomes essential.
Working with MODE.MULT for Multiple Modes
MODE.MULT identifies all values that appear with equal maximum frequency. This function returns an array, requiring different handling than standard formulas.
In Excel 365 or Excel 2021, MODE.MULT automatically spills results into adjacent cells:
A1: Daily Sales
A2: 150
A3: 200
A4: 150
A5: 175
A6: 200
A7: 150
A8: 200
A9: 180
A11: =MODE.MULT(A2:A9)
This dataset has two modes: 150 and 200 (each appears three times). In modern Excel, entering the formula in A11 automatically populates both values vertically:
A11: 150
A12: 200
In older Excel versions (2019 and earlier), you must use array formula entry. Select multiple cells vertically (A11:A12), enter the formula, then press Ctrl+Shift+Enter:
{=MODE.MULT(A2:A9)}
The curly braces appear automatically—don’t type them manually. They indicate an array formula.
The challenge with MODE.MULT is knowing how many cells to select beforehand. If you’re unsure how many modes exist, select more cells than you expect. Excess cells display #N/A, which you can hide with conditional formatting or error-handling formulas.
Handling Common Scenarios and Errors
MODE functions fail predictably in specific scenarios. Understanding these patterns prevents frustration:
Dataset A (No Repeats): Dataset B (All Same): Dataset C (Mixed):
15 100 50
23 100 50
47 100 "Text"
89 100 50
#N/A
=MODE.SNGL(A:A) =MODE.SNGL(B:B) =MODE.SNGL(C:C)
Result: #N/A Result: 100 Result: 50
Dataset A returns #N/A because no value repeats. MODE requires at least one value to appear multiple times. Use IFERROR to handle this gracefully:
=IFERROR(MODE.SNGL(A:A), "No mode exists")
Dataset B works perfectly—when all values are identical, that value is the mode.
Dataset C demonstrates that MODE ignores text and error values, calculating mode from numeric values only. The text entry and #N/A error don’t prevent the function from working.
Empty cells are also ignored:
A1: 10
A2: 10
A3: (empty)
A4: 20
A5: 10
=MODE.SNGL(A1:A5)
Result: 10
Practical Applications
MODE shines in real-world scenarios where you need to identify the most common discrete value.
Inventory Analysis:
A1: Order ID B1: Quantity
A2: 1001 B2: 5
A3: 1002 B3: 10
A4: 1003 B4: 5
A5: 1004 B5: 15
A6: 1005 B6: 5
A7: 1006 B7: 10
B9: Most Common Order Size:
B10: =MODE.SNGL(B2:B7)
Result: 5
This tells you that customers most frequently order in quantities of 5, informing packaging and shipping decisions.
Conditional Mode Calculations:
Combine MODE with IF for segmented analysis:
A1: Region B1: Sale Amount
A2: North B2: 1000
A3: South B3: 1500
A4: North B4: 1000
A5: South B5: 2000
A6: North B6: 1000
A7: South B7: 1500
C2: North Mode:
C3: =MODE.SNGL(IF(A2:A7="North",B2:B7))
In Excel 365, this works as a standard formula. In older versions, enter with Ctrl+Shift+Enter.
Quality Control:
Manufacturing tolerances often cluster around specific values:
A1: Measurement (mm)
A2: 10.1
A3: 10.0
A4: 10.1
A5: 10.2
A6: 10.1
A7: 10.0
=MODE.SNGL(A2:A7)
Result: 10.1
If your target is 10.0mm but MODE returns 10.1mm, your process has systematic bias requiring calibration.
MODE vs. MEAN and MEDIAN
Understanding when to use each central tendency measure is critical:
Dataset: Home Prices ($1000s)
200, 210, 205, 200, 215, 200, 850
=AVERAGE(A2:A8) = 297.14 (Mean)
=MEDIAN(A2:A8) = 210 (Median)
=MODE.SNGL(A2:A8) = 200 (Mode)
The $850K outlier heavily skews the mean to $297K, far above most homes. The median ($210K) better represents typical value. The mode ($200K) shows the most common price point.
Use MODE when:
- Analyzing discrete, categorical data (sizes, ratings, product codes)
- Identifying the most popular option matters more than average behavior
- Working with nominal data that can’t be meaningfully averaged
Use MEDIAN when:
- Data contains outliers that would skew the mean
- You need the middle value of a distribution
- Working with continuous data like prices, times, or measurements
Use MEAN when:
- You need to account for all values proportionally
- Data is normally distributed without extreme outliers
- Performing further statistical calculations
Tips and Best Practices
Validate Before Calculating: Always confirm your dataset contains repeated values. Use COUNTIF to check frequency:
=COUNTIF(A2:A10, MODE.SNGL(A2:A10))
This returns how many times the mode appears. If it equals 1 or returns an error, MODE isn’t appropriate.
Combine with FILTER for Dynamic Analysis:
=MODE.SNGL(FILTER(B:B, A:A="Category1"))
This calculates the mode for only rows matching your criteria—powerful for dashboard creation.
Handle MODE.MULT Spillover:
When you don’t know how many modes exist, wrap MODE.MULT:
=IFERROR(MODE.MULT(A2:A100), "")
This prevents #N/A errors from appearing in cells beyond the actual number of modes.
Performance Considerations: MODE functions are computationally efficient, but avoid volatile references (INDIRECT, OFFSET) in the range argument. For datasets exceeding 100,000 rows, consider whether MODE is the right tool—you might need database-level aggregation instead.
Text-Based Modes: Since MODE only works with numbers, convert text categories to numbers using MATCH or custom mapping, calculate MODE, then use INDEX to convert back:
=INDEX(CategoryList, MODE.SNGL(MATCH(DataRange, CategoryList, 0)))
MODE functions provide focused insight into your most common values. While less versatile than AVERAGE or MEDIAN, they answer specific questions about frequency that other functions cannot. Master MODE to unlock deeper understanding of categorical patterns, customer preferences, and process consistency in your data.