Summary

The Excel MODE.MULT function returns an array of the most frequently occurring numbers in a numeric data set. For example, =MODE.MULT(1,2,3,3,4,5,5) returns {3;5}, because there are two modes, 3 and 5.

Purpose 

Get most frequently occurring numbers

Return value 

Numbers representing mode(s)

Syntax

=MODE.MULT(number1,[number2],...)
  • number1 - A number or cell reference that refers to numeric values.
  • number2 - [optional] A number or cell reference that refers to numeric values.

How to use 

The Excel MODE.MULT function returns a vertical array of the most frequently occurring number(s) in a numeric data set. The mode is the most frequently occurring number in a set of data. When there is just one mode in a set of data, MODE.MULT will return a single result. If there is more than one mode in supplied data, MODE.MULT will return more than one result. If there are no modes, MODE.MULT will return #N/A.

The MODE.MULT function takes multiple arguments in the form number1, number2, number3, etc. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. MODE ignores empty cells, text values, and the logical values TRUE and FALSE. The MODE function will accept up to 254 separate arguments. 

Examples

In the example shown, the formula entered in D5:D9 is:

=MODE.MULT(B5:B16)

In Excel 365, which supports dynamic arrays, multiple results will spill onto the worksheet automatically. In earlier versions of Excel, you will need to enter the formula as a multi-cell array formula. See below for more information.

MODE returns the most frequently occurring number(s) in supplied data. For example,

=MODE.MULT(1,2,4,4,5,5,5,6) // returns 5
=MODE.MULT(7,8,9,7,9) // returns 7

If there are no duplicate numbers, the MODE.MULT function returns the #N/A error:

=MODE(7,9,6,5,3,1,0) // returns #N/A

If there is more than one mode in a set of data, MODE.MULT will return more than one result:

=MODE.MULT(1,3,3,5,5,7,7,8) // returns {3,5,7}

Note: MODE.MULT will not return any duplicated number(s), only mode(s).

Array formula syntax

The MODE.MULT function returns an array of results and must be entered as a multi-cell array formula if you are not using Excel 365. Here are the steps:

  1. Select a vertical range of cells
  2. Enter MODE.MULT function
  3. Confirm with control + shift + enter

In each selected cell, MODE.MULT will return a mode value, if one exists.

Horizontal array

The MODE.MULT function returns results in a vertical array. To return a horizontal array, add the TRANSPOSE function:

=TRANSPOSE(MODE.MULT(range))

This formula also needs to be entered as an array formula, unless you are using Excel 365, or another version of Excel that supports dynamic arrays.

Excel 365

In the Dynamic Array version of Excel, MODE.MULT spills the right number of multiple modes automatically with no #N/A errors

Notes

  • If supplied numbers do not contain duplicates, MODE.MULT will return #N/A
  • The MODE.MULT function ignores empty cells, and cells that contain boolean values or text.
  • Arguments can be numbers, names, arrays, or references.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.