Quick Navigation

MODE.MULT Function

Excel 2010+

Summary

The MODE.MULT function returns a vertical array containing all the most frequent values (modes) from a dataset. Unlike MODE.SNGL which returns only one mode, MODE.MULT captures multiple modes when they exist, making it perfect for datasets with several equally frequent values.

Syntax

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

Parameters

Parameter	Type	Required	Description
number1	`Number`	Yes	Required first argument - can be single number, array, or range reference
number2	`Number`	No	Optional additional arguments up to 254 total - numbers, arrays, or ranges

Using the MODE.MULT Function

MODE.MULT excels at statistical analysis of datasets with multimodal distributions. Enter as an array formula (Ctrl+Shift+Enter in older Excel versions or dynamic array in Excel 365) to return all modes. Use with TRANSPOSE for horizontal results.

Common MODE.MULT Examples

Multiple Modes Detection

=MODE.MULT(A2:A13)

Entered as array formula in Excel 2010+ or spills automatically in Excel 365. Returns {1;2;3} since each appears 3 times most frequently.

Horizontal Array Result

=TRANSPOSE(MODE.MULT(B1:E10))

Converts vertical mode array to horizontal display across columns.

Single Range Analysis

=MODE.MULT(C1:C100)

Analyzes entire column range to find all most frequent values.

Frequently Asked Questions

If not entered as an array formula (Ctrl+Shift+Enter), it behaves like MODE.SNGL returning just the first mode.

Returns #N/A error since no mode exists without duplicates.

Ignores text, logical values, empty cells but includes zeros.

Common Errors and Solutions

#N/A Error

Cause: No duplicate values exist or non-numeric data only

Solution: Ensure dataset contains repeated numeric values

#VALUE! Error

Cause: Arguments contain unconvertible text or error values

Solution: Clean data or use error-handling functions

Single result instead of array

Cause: Not entered as array formula

Solution: Select multiple cells and press Ctrl+Shift+Enter

Notes

Array formula required in Excel 2010-2019 (Ctrl+Shift+Enter)
Dynamic array support in Excel 365 (spills automatically)
Zero values are included in mode calculation
Maximum 254 arguments total
Use MODE.SNGL for single mode only

Compatibility

Available in: Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in: Excel 2007 and earlier

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2010+