MODE.MULT Function in Excel

In this article, you will learn about the MODE.MULT function, its usage, syntax and how to use it in your excel spreadsheet.

MODE.MULT Function in Excel

The MODE.MULT function in Excel returns a vertical array of the most frequently occurring or repetitive values in an array or range of data.


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


  • Number1 – The first number argument for which you want to calculate the mode.
  • Number2, – The second argument.
  • You can also use a single array or a reference to an array instead of arguments separated by commas.

Possible Errors and Usage Notes

  • The Arguments in the syntax can either be numbers or names, arrays, or references that contain numbers.
  • When an array or reference argument contains text, logical values, or empty cells,  the MODE.MULT function ignores the value but the cells with the value zero are included.
  • Arguments that are error values or text that cannot be translated into numbers cause errors.
  • If the data set contains no duplicate data points, MODE.MULT returns the #N/A error value.

How to use MODE.MULT Function in Excel?

Using this function in a WS is simple; all you need to do is enter the function as an array formula of the cell in the formula bar.

Take a look at the given example

How to use MODE.MULT Function in Excel?

To calculate the mode of the given numbers, Enter the numbers in columns A, and B and enter the following formula in Column C,

Formula: = MODE.MULT (A1:A10)

This must be entered as an array formula. When entered as an array formula, MODE.MULT returns 123, 456, and 789 as the modes because they each appear twice. The error #N/A  is because the other values are not repetitive.

Here, A1 refers to the cell name or the cell address. Repeat the same to the other cells and you will get the mode of the given numbers.