Skip to main content

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

Author Xiaoyang Last modified

It is a common task to get the most frequent text from a list of cells for most of us. In certain cases, you may also need to find and extract the top 3 or N most frequent text from a list, as shown in the screenshot below. 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.

A screenshot of the array formula result for extracting the most frequent value

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.

A screenshot of the array formula used to extract the second most frequent value in Excel

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

A screenshot showing how to drag the formula down to extract other frequent values in Excel

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)

A screenshot of the formula used to count the occurrences of a text in Excel

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.

A screenshot of the array formula for extracting the top frequent values

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:

A screenshot of the array formula result showing the top frequent values in Excel

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

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

A screenshot of the PivotTable option in Excel to extract frequent values

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:

A screenshot of the PivotTable option in Excel ribbon

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:

A screenshot of the 'Create PivotTable' dialog box in Excel

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:

A screenshot of the PivotTable fields pane showing values for creating a PivotTable

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

A screenshot showing how to sort PivotTable results from largest to smallest

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:

A screenshot of the sorted PivotTable results showing the top frequent values in Excel

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.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

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!