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.
You can achieve this task by using the below formulas, please do as this:
1. Please apply the below formula to get the value that appears the most frequently:
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:
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:
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:
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:
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.