Home » Excel Formulas » How to Find 5 Most Frequent Numbers in Excel?

How to Find 5 Most Frequent Numbers in Excel?

Kawser Ahmed Kawser Ahmed Sep 26, 2023
Get FREE Advanced Excel Exercises with Solutions!

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
How to Find 5 Most Frequent Numbers in Excel: 4 Methods
Method 1: Use of Excel MODE, IF, ISERROR, and MATCH Functions
Method 2: Applying IFERROR, MODE, IF, and COUNTIF Functions
Method 3: Using MODE, IF, ISNUMBER, MATCH
Method 4: Employing Combined Functions to Find 5 Most Frequent Numbers
Use of MAX Function to Find Highest Numbers in Excel
How to Find the 2nd Most Frequent Number by Applying MODE & IF Functions in Excel?
Practice Section
Conclusion
Related Articles

How to Find 5 Most Frequent Numbers in Excel: 4 Methods

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

Dataset for how to Find 5 Most Frequent Numbers in Excel

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.

Read More: How to Use Different Types of COUNT Functions in Excel

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))

use MODE, IF, ISERROR, and MATCH functions together to find out the 5 most frequent numbers in Excel

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

Here, if you use any older version of Excel than 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, and 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, the 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 to the list of numbers and returns the most frequent number 49. If there was another number that 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 gets value 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 to 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 number in the list.

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

Read More: How to Count Numbers in a Cell in Excel

Method 2: Applying IFERROR, MODE, IF, and 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.

Use IFERROR, MODE, IF, and COUNTIF to find out the 5 most frequent numbers in 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 the 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.

Read More: How to Count Unique Values Using Excel Formula

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.

Applying Excel Functions to find out the 5 most frequent numbers in 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.

Employing Combined Functions to Find 5 Most Frequent Numbers

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-column 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.

Use of MAX Function to Find Highest Numbers in Excel

Similar Readings

How to Find the 2nd Most Frequent Number by Applying MODE & IF Functions 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.

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

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.

Practice Section For How to Find 5 Most Frequent Numbers in Excel

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.

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.

Related Articles

Tags:

Kawser Ahmed
Kawser Ahmed

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

18 Comments
  1. Reply
    Frank May 21, 2019 at 8:10 PM

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

    Frank

  2. Reply
    Dennis Paul Eagan Jul 25, 2019 at 5:37 PM

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

  3. Reply
    KKRAO Oct 19, 2019 at 9:07 PM

    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.

    • Reply
      Kawser Feb 17, 2021 at 9:24 PM

      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.

  4. Reply
    Wayne Edmondson Oct 29, 2019 at 11:01 AM

    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!

  5. Reply
    Evon Aug 10, 2021 at 10:23 PM

    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?

  6. Reply
    arun Oct 16, 2021 at 2:09 PM

    why this formula only picking top 9 numbers only

    • Reply Avatar photo
      Tanjim Reza Aug 30, 2022 at 3:15 PM

      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

  7. Reply
    RC GOYAL Dec 20, 2022 at 6:00 PM

    Hi Kawser.. excellent. Thanks for sharing all of your knowledge and skills in the blog posts.. very helpful and appreciated. I could not understand about using of +{0,0} in the last of combined formula.

    Thanks dear

    • Reply Avatar photo
      Arin Dec 21, 2022 at 12:09 PM

      Hi RC GOYAL,
      Thanks for your feedback.
      In the formula, we used {0,0} in the combination formula to return a two column array. However, you can avoid it for this dataset. It will return the same result.
      If you face any further problems, please share your Excel file with us at [email protected].
      Regards
      Arin Islam,
      Exceldemy.

  8. Reply
    ken Feb 2, 2023 at 5:06 PM

    Hi Thank you for explaining the above in detail. This will work if the data is in one column. What if the data is spread across ie col A, B, C and D. Is there a way to work out the 5 most frequent number and also the frequency? thank you Ken

    • Reply Avatar photo
      Al Ikram Amit Jul 11, 2023 at 5:01 PM

      Dear KEN,

      Thank you for your question. If your data is spread across columns A, B, C, and D, you can still work out the 5 most frequent numbers and their frequencies using Excel formulas. Here’s a step-by-step approach:

      1. Assuming that your data starts from row 5, you can use the following formula in cell F5 to get the 5 most frequent numbers:

      =IFERROR(MODE(IF(COUNTIF(F$4:F4,$A$5:$D$23)={0},$A$5:$A$23)),"")

      1. To calculate the frequency of each of the top 5 most frequent numbers, you can use the following formula in cell G5:

      =COUNTIF($A$5:$D$23,F5)

      1. Copy the formulas down by dragging the fill handle for cells F5:F9 and G5:G9 to get the top 5 most frequent numbers and their frequencies.

      I hope this helps! If you have any further questions, feel free to ask.

      Best regards

      Al Ikram Amit

      Team ExcelDemy

      • Reply
        Gracie Jul 18, 2023 at 5:52 AM

        Hi,

        I tried your method above with numbers spanning to 5 columns and changed the formula to fit my table: =IFERROR(MODE(IF(COUNTIF(M$4:M4,$E$2:$I$57)={0},$E$2:$I$57)),””) but I received an error message:

        “Microsoft Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you. Try editing the last formula you entered or removing it with the Undo command.”

        What did I do wrong?

        –Gracie

        • Avatar photo
          Al Ikram Amit Jul 18, 2023 at 12:09 PM

          Hi Gracie,

          I see you encountered an issue with the formula provided earlier. The circular reference error occurs because the formula is attempting to reference the same range (M$4:M4) it’s currently located in, which creates a circular dependency.

          To address this, I recommend using a VBA solution to find the top 5 most frequent numbers and their frequencies in the range M4:N9. VBA allows us to perform more complex calculations and avoid circular reference problems.

          To use this VBA code, press Alt+F11 to open the VBA editor in Excel. Then, click Insert>> Module to insert a new module. Copy and paste the code into the module.

          Sub FindTop5FrequentNumbers()
    Dim dataRange As Range
    Dim resultRange As Range
    Dim frequencyRange As Range
    Dim dict As Object
    Dim cell As Range
    Dim i As Integer
    Dim keyArray As Variant    
    ' Set the range that contains your sample data (E2:I57)
    Set dataRange = ThisWorkbook.Worksheets("Sheet1").Range("E2:I57") ' Adjust the sheet name and range as per your data   
    ' Set the range where you want to output the top 5 most frequent numbers (M4:M9)
    Set resultRange = ThisWorkbook.Worksheets("Sheet1").Range("M4:M9") ' Adjust the sheet name and range as per your choice   
    ' Set the range where you want to output the corresponding frequencies (N4:N9)
    Set frequencyRange = ThisWorkbook.Worksheets("Sheet1").Range("N4:N9") ' Adjust the sheet name and range as per your choice  
    ' Create a dictionary to store the frequencies of each number
    Set dict = CreateObject("Scripting.Dictionary")  
    ' Loop through each cell in the data range to count the occurrences of each number
    For Each cell In dataRange
        If IsNumeric(cell.Value) Then
            If dict.Exists(cell.Value) Then
                dict(cell.Value) = dict(cell.Value) + 1
            Else
                dict.Add cell.Value, 1
            End If
        End If
    Next cell  
    ' Convert the dictionary keys (numbers) to an array
    keyArray = dict.keys   
    ' Sort the array based on the frequencies in descending order
    For i = LBound(keyArray) To UBound(keyArray) - 1
        For j = i + 1 To UBound(keyArray)
            If dict(keyArray(j)) > dict(keyArray(i)) Then
                ' Swap elements
                temp = keyArray(i)
                keyArray(i) = keyArray(j)
                keyArray(j) = temp
            End If
        Next j
    Next i
    ' Output the top 5 most frequent numbers and their frequencies to the result and frequency ranges
    For i = 1 To 5
        If i <= UBound(keyArray) + 1 Then
            resultRange.Cells(i, 1).Value = keyArray(i - 1)
            frequencyRange.Cells(i, 1).Value = dict(keyArray(i - 1))
        Else
            resultRange.Cells(i, 1).Value = ""
            frequencyRange.Cells(i, 1).Value = ""
        End If
    Next i
End Sub

          This VBA code will find the top 5 most frequent numbers in the data range E2:I57 and display them in the range M4:M9 and their corresponding frequencies in N4:N9. Remember to adjust the sheet name(We have used the sheet name “Sheet1“) and range references in the code are matched with your data.


          If you have any further questions or need more assistance, feel free to ask.

          Best regards,
          Al Ikram Amit
          Team ExcelDemy

Leave a reply

About ExcelDemy.com

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact | Privacy Policy | TOS

Affiliate Disclosure:

ExcelDemy.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program. I earn a small commission if you buy any products using my affiliate links to Amazon.

DMCA.com Protection Status

Join ExcelDemy Forum

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo