Note: The other languages of the website are Google-translated. Back to English

How to find the top 5 or n most frequent text or numbers in Excel?

It is a common task to get the most frequent text from a list of cells for most of us. In certain case, you may also need to find and extract the top 3 or n most frequent text from a list as below screenshot shown. To solve this problem, this article will introduce some quick tricks for you.


Find and extract top 3 or n most frequent text or numbers with formulas

You can achieve this task by using the below formulas, please do as this:

Formula 1: Normal formula + Array formula

1. Please apply the below formula to get the value that appears the most frequently:

=IFERROR(INDEX($A$2:$A$18,MODE(MATCH($A$2:$A$18,$A$2:$A$18,0)+{0,0})),"")

Notes:

1. In this formula, A2:A18 is the data list that you want to find the values from.

2. If you are using the Excel versions that are prior to Microsoft 365, you should press Ctrl + Shift + Enter to get the result. For the users of Microsoft 365 and newer versions of Excel, you can just enter the formula in the result cell, and then press Enter key.

2. Then, you should use the following array formula to get the second most frequent value, enter this formula below the first formula cell, and then press Ctrl + Shift + Enter keys together to get the correct result, see screenshot:

=IFERROR(INDEX(A$2:A$18,MODE(IF(COUNTIF(C$2:C2,A$2:A$18)=0,MATCH(A$2:A$18,A$2:A$18,0)+{0,0}))),"")
Note: In this formula, A2:A18 is the data list that you want to find the values from, C2 is the formula cell of the most frequent value you have extracted just.

3. After extracting the second most frequent value, drag this formula cell down to get other n values as you need. See screenshot:

4. Finally, you can use the following formula to get the number of occurrences of the extracting text, see screenshot:

=COUNTIF($A$2:$A$18, C2)


Formula 2: Array formula

In fact, you can also use a single array formula to get the result, please do as this:

1. Enter or copy the below formula into a blank cell where you want to get the result, then, press Ctrl + Shift + Enter keys together to get the first result, see screenshot:

=INDEX($A$2:$A$18,MATCH(MIN(MODE(IF(NOT(COUNTIF(C$1:C1,$A$2:$A$18)),(COUNTIF($A$2:$A$18,"<"& $A$2:$A$18)+1)*{1,1}))),COUNTIF($A$2:$A$18,"<"& $A$2:$A$18)+{1},0))
Note: In the formula, A2:A18 is the data list that you want to find the values from, C1 is the above cell of your formula.

2. And then, drag this formula down to get other values. If you want to get the top 3 most frequent values, you just need to drag down to other two cells, see screenshot:

3. As well, you can also get the number of occurrences of the extracting text with below formula:

=COUNTIF($A$2:$A$18, C2)


Find and extract top 3 or n most frequent text or numbers with Pivot Table

If you are familiar with PivotTable, you can quickly get a table which arranges the data from the most frequently occurring to least frequently occurring. Please do with the following steps:

1. Select the data list that you want to use, then, click Insert > PivotTable, see screenshot:

2. In the popped out Create PivotTable dialog box, select the place where you want to put the PivotTable from the Choose where you want the PivotTable report to be placed section, see screenshot:

3. Then, click OK button, a PivotTable Fields pane is displayed. Drag the column name into the Rows and Values areas separately, and you will get a pivottable as below screenshot shown:

4. Click any cell in the number column, then, right click, and choose Sort > Sort Largest to Smallest, see screenshot:

5. And then, the count of values in the pivotable are sorted from largest to smallest, and you can see the top 3 most frequent texts at glance, see screenshot:


More relative articles:

  • Find Common Values In 3 Columns
  • In general case, you may need to find and select the same values between two columns in Excel, but, have you ever tried to find the common values among three columns which means the values exist in 3 columns at the same time as following screenshot shown. This article, I will introduce some methods to finish this task in Excel.
  • Find The Most Frequent Text With Criteria
  • This article will talk about how to find the most occurred text based on the specific criteria from a range of cells in Excel. At the same time, I will also introduce the formula for extracting the most frequently occurring text in a column.
  • Sort Data By The Most Frequent Value
  • Supposing you have a long list of data in your worksheet, and now you would like to sort this list by the frequency of which each word occurs. That is to say, the most common value (for example, occurs four times in the column) is listed first, and then be followed by the words which occur three times, twice and once as following screenshots shown. How could you solve this task in Excel?
  • Find The Least Or Most Common / Frequent Value
  • When you work on Excel workbook, sometimes, you may need to find the least common or frequent value in a column or a row of the worksheet. Excepting find it one by one manually, there is a formula which combine the Index, Match, Min and Countif functions can quickly return the least frequently occurring value.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations