Get the Most Frequent Number(s) from an Array in Excel (Formula)

There are two functions in Excel that you can use to get the most frequent number(s) from a list of numbers. The first is MODE, and the second is MODE.MULT. The first function helps you to get the single most frequent number, and the second helps you to get multiple.

Let’s learn both formulas one by one.

Formula to Get Single Most Frequent Number (MODE)

You can use the below steps:

1. First, enter the MODE function in a cell.
2. After that, enter the starting parentheses.
3. Next, refer to the range where you have the list of numbers.
4. In the end, enter the closing parentheses and hit enter to get the result.

=MODE(A1:A9)

MODE is an old function but is still available for backward compatibility. But there’s one shortcoming of this function. If you have two numbers in a list that are equally occurred in the list, MODE won’t be able to give you both numbers in the result.

In the above example, you have 30 and 10; both numbers occur four times in the list, but MODE returns 30 in the result, not 10 or both 10 and 30.

Formula to Get the Multiple Frequent Numbers (MODE.MULT)

You can use the below steps:

1. First, enter Excel’s MODE.MULT function in a cell.
2. After that, enter the starting parentheses.
3. Next, refer to the range of cells where you have the list of numbers.
4. In the end, enter the closing parentheses and hit enter to get the result.

MODE.MULT is a dynamic array function that can return results in multiple cells. That’s why it returns 30 and 10 in the result, as both numbers equally occur in the list.

=MODE.MULT(A1:A9)

And if you want to get the result in a horizontal form in a range, you can use the TRANSPOSE function.

=TRANSPOSE(MODE.MULT(A1:A9))