MODE.SNGL
Returns the most frequently occurring value in a dataset.
=MODE.SNGL(number1, [number2], ...)This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.
What the function does
Overview
Returns the most frequently occurring value in a dataset.
MODE.SNGL is a statistical function used to identify the most frequently occurring value in a dataset, representing the most 'popular' or common data point. It is a modern replacement for the legacy MODE function. A key technical behavior is that when a dataset contains multiple modes (two or more values with the same maximum frequency), MODE.SNGL returns only the first one it encounters in the range. The function handles up to 255 individual arguments or a single large array reference. It specifically ignores empty cells, text, and logical values (TRUE/FALSE) found within array or range references. However, if these non-numeric values are passed as direct constants in the formula, an error may occur. For datasets where you need to identify every occurring mode in a tie, use the array-based MODE.MULT function instead. It is best suited for discrete numeric data, such as survey scores, sizes, or category codes, rather than continuous data where exact duplicates are rare.
Quick reference
Syntax
=MODE.SNGL(number1, [number2], ...)Inputs
Arguments
Example: A2:A100
Formula patterns
Examples
Finding the most frequent survey response
=MODE.SNGL(A2:A20)Identifying the common value across multiple ranges
=MODE.SNGL(B2:B10, D2:D10)Finding the mode of specific non-contiguous cells
=MODE.SNGL(C2, C4, C6, C8)Avoid these issues
Common Errors
#N/A
Cause: The dataset contains no duplicate values, meaning every number appears exactly once.
Fix: Ensure the data range includes repeating values, or wrap the formula in IFERROR to manage unique datasets.
#VALUE!
Cause: A value supplied directly as an argument is text or a non-numeric character that Excel cannot convert.
Fix: Ensure all direct arguments are numbers; alternatively, use range references (A2:A10) so the function can automatically ignore non-numeric cells.
Platform support
Compatibility
Source: Microsoft Support
Common questions
Frequently Asked Questions
Returns the most frequently occurring value in a dataset.
number1: Range of data
#N/A: Ensure the data range includes repeating values, or wrap the formula in IFERROR to manage unique datasets. #VALUE!: Ensure all direct arguments are numbers; alternatively, use range references (A2:A10) so the function can automatically ignore non-numeric cells.