To find the number that appears most often in a list, use =MODE(A1:A9).
If two or more numbers tie for most frequent, MODE returns only one of them — whichever appears first in the range — so use =MODE.MULT(A1:A9) when ties are possible; it returns every joint-mode value, spilling down the cells below in Excel 365 (on Excel 2019 or earlier, select a vertical range first and enter it with Ctrl+Shift+Enter).
Add TRANSPOSE to lay the results across a row instead: =TRANSPOSE(MODE.MULT(A1:A9)). Two limitations worth knowing.
Both functions return #N/A when no value repeats at all, so wrap them in IFERROR on data that might be all-unique. And both work only with numbers — for the most frequent text value, use =INDEX(A1:A9,MODE(MATCH(A1:A9,A1:A9,0))), which converts each entry to its first-match position, finds the most common position, and returns the value sitting there.
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:
- First, enter the MODE function in a cell.
- After that, enter the starting parentheses.
- Next, refer to the range where you have the list of numbers.
- 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 equally occur on the list, MODE won’t be able to give you both numbers in the result.
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:
- First, enter Excel’s MODE.MULT function in a cell.
- After that, enter the starting parentheses.
- Next, refer to the range of cells where you have the list of numbers.
- In Excel, 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))
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.