fx
FunctionPedia
Quick Answer

MODE.MULT Function

Returns every value tied for the highest frequency as an array.

✓ Excel✓ Google SheetsExcel Excel 2010+

Syntax

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

Parameters

ParameterDescriptionRequired
number1The first number, cell, or range to evaluate.Required
number2Additional numbers, cells, or ranges (optional).Optional

Basic Example

Two values share the top frequency

=MODE.MULT(1,2,2,3,3,4)
Result2 and 3 (vertical array {2;3})

Both 2 and 3 occur twice, more than 1 and 4, so both are returned.

Advanced Examples

Example 1: Three tied modes

Multi-modal distribution

When more than two values tie, all are returned.

=MODE.MULT(7,7,8,8,9,9)
Result: 7, 8, and 9 (vertical array {7;8;9})
Each of 7, 8, and 9 occurs twice, so all three are returned.

Example 2: Single mode still returns an array

Only one most-frequent value

Even with one mode the result is a one-element array.

=MODE.MULT(5,5,5,8,9)
Result: 5 (array {5})
5 occurs three times; it is returned as a single-element array.

How MODE.MULT Works

MODE.MULT finds the highest frequency among the numeric values, then returns every value that reaches that frequency. It returns a vertical (column) array, so in modern Excel it spills into multiple cells; in older versions it must be entered as a multi-cell array formula with Ctrl+Shift+Enter. If no value repeats, it returns #N/A. Text, logical values, and empty cells are ignored.

1
Select a vertical range of cells
Highlight enough empty cells below the formula cell to hold all modes.
2
Enter the formula
Type =MODE.MULT(range) in the top cell of the selection.
3
Confirm as array (older Excel)
In Excel 2019 and earlier press Ctrl+Shift+Enter; in Microsoft 365 just press Enter to spill.

Important Notes & Limitations

  • Returns a vertical array, so it needs multiple cells to display all modes (or dynamic-array spill in Microsoft 365).

  • Returns #N/A when every value is unique.

  • Ignores text labels, so repeated category names are not counted.

Common Errors & Fixes

#N/A errorNo value is repeated in the data.

Fix: Use MODE.SNGL or verify the dataset contains duplicates.

Only one value shownFormula entered in a single cell without array handling in pre-365 Excel.

Fix: Select a vertical range of cells and confirm with Ctrl+Shift+Enter.

Download Practice File

Practice MODE.MULT with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the MODE.MULT function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

Why do I only see one result?
In older Excel the formula must be entered as an array across several cells. In Microsoft 365 it spills automatically.
How is MODE.MULT different from MODE.SNGL?
MODE.SNGL returns one mode (lowest on ties); MODE.MULT returns all tied modes as an array.
What if there is no repeated value?
MODE.MULT returns #N/A because no mode exists.