List most frequently occuring numbers

To list the most frequently occurring numbers in a column (i.e. most common, second most common, third most common, etc), you can an array formula based on four Excel functions: IF, MODE, MATCH, and ISNUMBER. In the example shown, the formula in D5 is:
where "data" is the named range B5:B16. The formula is then copied to rows below D5 to output the desired list of most frequent numbers.
Note: this is an array formula and must be entered with control + shift + enter.
The core of this formula is the MODE function, which returns the most frequently occurring number in a range or array. The rest of the formula just constructs a filtered array for MODE to use in each row. The expanding range $D$4:D4 works to exclude numbers already output in $D$4:D4.
Working from the inside out:
- MATCH function is used first check all numbers in the named range "data" against existing numbers in the expanding range $D$4:D4
- ISNUMBER converts matched values to TRUE and non-matched values to FALSE
- 1-NUMBER reverses the array, and the math operation outputs ones and zeros
- IF uses the array output of #3 above to filter the original list of values, excluding numbers already in $D$4:D4
- The MODE function returns the most frequent number in the array output in step #4
In cell D5, no filtering occurs and the output of each step above looks like this:
{#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A} {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} {1;1;1;1;1;1;1;1;1;1;1;1} {93;92;93;94;95;96;97;98;99;93;97;95} 93
In cell D6, with 93 already in D5, the output looks like this:
{2;#N/A;2;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;2;#N/A;#N/A} {TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE} {0;1;0;1;1;1;1;1;1;0;1;1} {FALSE;92;FALSE;94;95;96;97;98;99;FALSE;97;95} 95
Handling errors
The MODE function will return the #N/A error when there is no mode. As you copy the formula down into subsequent rows, you will likely run into the #N/A error. To trap this error and return an empty string ("") instead, you can use IFERROR like this:
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.