How to find value with highest frequency in a range in Excel?
How can you find the most common (frequently appear) value in a range in Excel? This article shows you two methods to achieve it in details.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Supposing you need to find the certain fruit with highest frequency in the Fruit column as bellow screenshot shown, please do as follows.
1. Select a blank cell for showing the value with highest frequency, then enter formula =INDEX(B:B, 10000*MOD(MAX(COUNTIF(B2:B16,B2:B16)+(ROW(B2:B16)/10000)),1), 1) into the Formula Bar, and then press the Ctrl + Shift + Enter keys simultaneously.
Now you are getting the value with highest frequency in the Fruit column, and it will get the last most frequent value if there are more than one result.
1. In the formula, B:B and B2:B16 are the column and range which you will search for the value with highest frequency.
2. This array formula is available both text list and number list.
3. If you just want to find the value with highest frequency in a number list, you can also try this normal formula: =MODE(C2:C16), it will get error value if there is no repeated values
If the above array formula is hard for you to handle, you can try the Find most common value function. This function will help you easily find the value with highest frequency in a certain range without remembering formulas.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.|
1. Select a blank cell for showing the value with highest frequency, then click Kutools > Formulas > Find most common value. See screenshot:
2. In the Formula Helper dialog box, click the button to select the range (only one column) with the most common value you need to find, and then click the OK button.
Then the value with highest frequency in specified list will be populated in selected cell immediately.
1. With this feature, you will get the first most frequent value if there are more than one result.
2. If there are no repeated data in the list, you will get an error value after applying this feature.
- How to convert birthdate to age quickly in Excel?
- How to count occurrences of specific character in a cell in Excel?
- How to count the number of words in a cell or a range cells in Excel?