If you are looking for how to find the **5 most frequent numbers** in Excel, then you have come to the right place. In this tutorial, I will show how to find the **5 **most (or less or more) frequent numbers/values in Excel.

**Table of Contents**hide

## Download Practice Workbook

Download the Excel file that I have used to create this article. It will be easy for you to follow this article.

## 4 Methods to Find 5 Most Frequent Numbers in Excel

Here, I will use some Excel formulas to do so. Let’s start. Suppose, I have a list of **19** numbers.

Now, I am going to find out the most frequent numbers in the list. Actually, if a number is present more than once in the list, then it will be treated as a **frequent number**. Here, I will show you **4** Excel formulas that can be used to find out the most frequent numbers in a list.

### Method 1: Use of Excel MODE, IF, ISERROR, and MATCH Functions

Here, you can use the **MODE**, **IF**, **ISERROR**, and **MATCH** functions together to find out the **5 **most frequent numbers in Excel. Also, you can find even more or less than the **5** most frequent numbers with this formula in Excel.

**Steps:**

- Firstly, in the cell
**D5**, I input this Excel formula:

`=MODE(IF(ISERROR(MATCH($B$5:$B$23,D$4:D4,0)),$B$5:$B$23))`

- Now, press
**ENTER**to get the result and the result is**49**.

Here, if you use any older version of Excel then** office 365** then you have to input this formula as an **Excel array formula**. So, you need to press **CTRL+SHIFT+ ENTER** simultaneously to get the result.

Furthermore, I will show the frequency of the number in the list (using the **Excel COUNTIF function**).

- Now, in the cell
**E5**, I input this Excel formula:

`=COUNTIF($B$5:$B$23,D5)`

Here, in this formula, I have used the **COUNTIF** function to find the frequency. The **COUNTIF** function will return the total cell numbers within the **$B$5:$B$23** range which will contain the value of the **D5** cell.

- Subsequently, press
**ENTER**to get the result.

Now, **apply the formula, **used in the **D5 **cell for getting the** 5** most frequent numbers, to other cells in the column using Excel’s **Fill Handle **Tool.

As a result, you see the outputs. The** 5 **most frequent numbers are shown in the column.

- Similarly, I will copy the formula used in the
**E5**cell for counting the frequencies using relative cell references. To do this, use the**Fill Handle**icon, hold it, and drag until you reach cell**E9**. - Then, release the mouse button.

Lastly, you see the** 5 **most frequent numbers along with the frequencies.

Here, if you want to find more than the **5** most frequency numbers then you can drag the Mouse pointer more. At the last** two** cells of the column, you’re seeing an Excel** error value (#N/A)**. It is because Excel’s **MODE** function does not find a number with a frequency of more than **1**.

Don’t worry, I will explain step by step how this Excel formula works within a second.

**Formula Breakdown**

To understand this formula, you have to understand how the following Excel functions work:

**MODE, IF, ISERROR, **and** MATCH.**

Let’s explain how this formula actually works.

Remember, this is an array formula. At first, I shall examine the formula in cell **D5**, then the formula in cell** D6**.

The formula in the cell **D5**:

- This is the returned value by the
**MATCH**part:**MATCH($B$5:$B$23,D$4:D4,0)**

Here is the returned array of the **MATCH** function part: **MODE(IF(ISERROR({#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A}),$B$5:$B$23))**

This is because, in the lookup_array **D$4:D4**, the **MATCH** function does not find any match of the list values. So, it returns all the **#N/A;** errors.

- Then, I applied the
**ISERROR**function to these values.**ISERROR**function returns**TRUE**if the value is an**ERROR**.

The returned array of the** ISERROR** function part:

**MODE(IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},$B$5:$B$23))**

- Now, the
**IF**function part. For the first**TRUE**value, it returns the first number from the list. For the second**TRUE**value, it returns the second number from the list. And so on. This is the output:

Next, returned array of the **IF **function part:

**MODE({74;66;49;43;21;82;21;59;82;49;66;49;74;21;43;49;50;3;10})**

- And, now the last function
**MODE**. Here, the**MODE**function applies on the list of numbers and returns the most frequent number**49**. If there was another number which also has the same frequency, then it returns that which is the first found in the list.

Now, the formula in the cell **D6**:

**MODE(IF(ISERROR(MATCH($B$5:$B$23,D$4:D5,0)),$B$5:$B$23))**

Now, observe this formula carefully.

Here, you see that the lookup_array has now been changed to** D$4:D5** as I have extended the formula to this cell from cell **D5**. So, the lookup_array has changed relatively.

- So, this time the
**MATCH**function returns these values. When the**MATCH**function finds**49**in the lookup_array**(D$4:D5)**, it returns the position in the lookup_array. The position of**49**is actually**2**.

Now, the returned array of the **MATCH** function part:

**MODE(IF(ISERROR({#N/A;#N/A;2;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;2;#N/A;2;#N/A;#N/A;#N/A;2;#N/A;#N/A;#N/A}),$B$5:$B$23))**

- When the
**ISERROR**function will get values**2**in the array, it will return**FALSE**values in those places.

Here, the **ISERROR** function returns:

**MODE(IF({TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE},$B$5:$B$23))**

- For those
**FALSE**values, the**IF**function will also return the**FALSE**So, the**MODE**function will be applied only on the numerical values. Not on the**FALSE**values.

Subsequently, the returned array of the **IF **function part:

**MODE({74;66;FALSE;43;21;82;21;59;82;FALSE;66;FALSE;74;21;43;FALSE;50;3;10})**

- And, this is the final output. Here, the
**MODE**function returns value**21**, the second most frequent numbers in the list.

Finally, I think you can now understand how the formula actually works.

### Method 2: Applying IFERROR, MODE, IF, COUNTIF Functions

In this method, I will use the following Excel functions: **IFERROR****, MODE, IF,** and **COUNTIF **to find out the **5 **most frequent numbers in Excel.

**Steps:**

- Firstly, write the following formula in the cell
**D5**.

`=IFERROR(MODE(IF(COUNTIF(D$4:D4,$B$5:$B$23)={0},$B$5:$B$23)),"")`

- Secondly, you must press
**ENTER**.*And, press***CTRL, SHIFT,**and**ENTER**simultaneously for the**older version**of Excel.

Furthermore, I will show the frequency of the number in the list.

- Now, in the cell
**E5**, input this Excel formula:

`=COUNTIF($B$5:$B$23,D5)`

Here, in this formula I have used the **COUNTIF** function to find the frequency. The **COUNTIF** function will return the total cell numbers within the **$B$5:$B$23** range which will contain the value of **D5** cell.

- Subsequently, press
**ENTER**to get the result.

Then apply the formulas to the other **4 **cells in the column (in the above way). Lastly, you see the** 5 **most frequent numbers along with the frequencies.

Here, I will not explain how this formula works. If you reverse engineer this formula using the above procedure (check out the **first** formula), then you will be able to understand how this formula works.

### Method 3: Using MODE, IF, ISNUMBER, MATCH

For this method, I have used the following Excel functions: **MODE, IF,** **ISNUMBER**, and **MATCH**.

- And this is the formula I have used in the cell D5:

`=MODE(IF(1-ISNUMBER(MATCH($B$5:$B$23,D$4:D4,0)),$B$5:$B$23))`

- Here, press
**ENTER to**get the result.*And, press***CTRL, SHIFT,**and**ENTER**simultaneously for the**older version**of Excel.

Furthermore, I will show the frequency of the number in the list.

- Now, in the cell
**E5**, I input this Excel formula:

`=COUNTIF($B$5:$B$23,D5)`

- Subsequently, press
**ENTER**to get the result.

Then I applied these formulas to other cells in the column using the** Fill Handle Tool**. This is the result:

I will also not explain how this formula works. If you reverse engineer this formula using the above procedure (check out the** first **formula), then you will be able to understand how this formula works.

### Method 4: Employing Combined Functions to Find 5 Most Frequent Numbers

You can find the **5 **most frequent numbers in Excel using another combination of functions. Here, I will use **IFERROR**, **INDEX**, **MODE, IF, COUNTIF,** and **MATCH** functions.

- Firstly, click the
**D5**cell to select it. - Secondly, write this formula in this cell:

`=IFERROR(INDEX(B$5:B$23,MODE(IF(COUNTIF(D$4:D4,B$5:B$23)=0,MATCH(B$5:B$23,B$5:B$23,0)+{0,0}))),"")`

- Thirdly, press
**ENTER**to get the result.*And, press***CTRL, SHIFT,**and**ENTER**simultaneously for the**older version**of Excel.

**Formula Breakdown**

**MATCH(B$5:B$23,B$5:B$23,0)—>**gives**{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}**

- Here,
**COUNTIF(D$4:D4,B$5:B$23)—>**turns into an array with all**zero**values. - So,
**COUNTIF(D$4:D4,B$5:B$23)=0—>**returns the array as:**{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}**

- Now, the IF will return a
**two**columns array. - Then, the
**MODE**function gives**3**. **INDEX(B$5:B$23,3)—>**returns**49**.- Finally,
**IFERROR(49,**“”**)—>**gives**49**.

Now, I will find the frequencies.

- Firstly, click the
**E5**cell to select it. - Secondly, write this formula in this cell:

`=COUNTIF($B$5:$B$23,D5)`

**$B$5:$B$23** means the data range and criteria **D5** means: the value of cell **D5**. In one sentence the whole command is: Count in the value if the value is equal to **D5** in the data range **$B$5:$B$23**.

- Thirdly, press
**ENTER**to get the result.

Then I have applied these formulas to other cells in the column using the** Fill Handle Tool**. This is the result:

## Use of MAX Function to Find Highest Numbers in Excel

Moreover, you can find the **highest** number from a given list in Excel using **the MAX function**.

- Firstly, click the
**D5**cell to select it. - Secondly, write this formula in this cell:

`=MAX(B5:B23)`

- Thirdly, press
**ENTER**to get the result.

Finally, you will get the** highest **number. It is so easy to find the **maximum value** from some given numbers in Excel.

## Applying MODE & IF Functions to Find 2nd Most Frequent Number in Excel

You can find the **2nd **most frequent number in Excel by using **MODE**, and **IF** functions only.

- Firstly, click the
**D5**cell to select it. - Secondly, write this formula in this cell:

`=MODE(IF(B5:B23<>MODE(B5:B23),B5:B23,""))`

- Thirdly, press
**ENTER**to get the result.

**Formula Breakdown**

Here, the **MODE** function will return the most frequent number in a given array.

**MODE(B5:B23)—>**turns**49**.

Now, the IF function will return the value which will fulfill the given criteria.

**IF(B5:B23<>49,B5:B23,””)—>**becomes**{74;66; ;43;21;82;21;59;82; ;66;FALSE;74;21;43; ;50;3;10}**

Here, the** 1st** frequent number is blank. As a result, the** MODE** function will return the** 2nd** most frequent function.

**MODE({74;66; ;43;21;82;21;59;82; ;66;FALSE;74;21;43; ;50;3;10})—>**gives**21.**

Now, I will find the frequencies.

- Firstly, click the
**E5**cell to select it. - Secondly, write this formula in this cell:

`=COUNTIF($B$5:$B$23,D5)`

**$B$5:$B$23** means the data range and criteria **D5** means: the value of cell **D5**. In one sentence the whole command is: Count in the value if the value is equal to **D5** in the data range **$B$5:$B$23**.

- Thirdly, press
**ENTER**to get the result.

Lastly, you will see the** 2nd** most frequent number and the frequency also.

## Practice Section

Now, you can practice the explained method by yourself.

## Conclusion

So, these are my formulas to find the most frequent numbers in a list. Here, I have explained how the** first** formula works step by step. This is a general process of reverse-engineering an Excel formula. So, I hope you can understand how the other two formulas are working.

Do you know any better way to find** 5** of the most frequent numbers in Excel? Let me know in the comment box.

Thanks a lot this has been a challenge to me for some time now.

Frank

You’re welcome, Frank! Feeling good to hear that this article helped you.

Best regards

Thank you for this and all your information. I do appreciate it all. Dennis

You’re most welcome, Dennis!

Thanks for the nice words.

Best regards

Kawser Ahmed

In the first sight, I did not understand the usage of this formula, Sorry.

Where it is useful, in what contest? If it is finding the numbers, we can sort them in ascending or descending order then we can see the contents of the row.

I request you please enlighten me.

Hi Kkrao,

I understand your thought.

It is possible for small numbers to count the top ones, but if you have good numbers in a list, you might need an automatic process. And sometimes, you might need the output to feed a system’s input, so I hope you get my point for making a formula for this.

Thanks.

Hi Kawser.. excellent. I was aware of one of your methods.. but learned a lot interrogating the other two. Note for users that you can use MODE() .. the older function or MODE.SNGL() .. the newer function. Either works.. but generally I try and use the newer functions to train my brain to go forward. Thanks for sharing all of your knowledge and skills in the blog posts.. very helpful and appreciated. Thumbs up!

Wayne,

Thanks for your feedback.

Thanks for this. When I use the formula in my sheet, it keeps repeating the result twice. Do you have any idea why this is happening?

The best way is, please send the sheet to me at [email protected]

why this formula only picking top 9 numbers only

Hi, ARUN!

Thank you for your query.

According to our dataset, we have 9 values that are repeated twice or thrice. Other values are unique and have come only once in the dataset. As the formula is about finding the frequent numbers, it is returning those repeated 9 numbers only.

Regards,

Tanjim Reza