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.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
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?