How to find the mode for text value from a list/column in Excel?
As you know, we can apply the MODE function to quickly find out the most frequent number from a specified range in Excel. However, this MODE function does not work with text values. Please do not worry! This article will share two easy methods to find the mode for text values from a list or a column easily in Excel.
- 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.
This method will introduce an array formula to find the mode for text values in a list in Excel. Please do as follows:
Select a blank cell you will place the most frequent value into, type the formula =INDEX(A2:A20,MODE(MATCH(A2:A20,A2:A20,0))) (A2:A20 is the list where you will find out the most frequent (mode for) text value from) into it, and then press the Ctrl + Shift + Enter keys.
Now the most frequent (mode for) text value has been found and returned into the selected cell. See screenshot:
|Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future!
Read more… Free trial
If you have Kutools for Excel installed, you can apply its Find most common value formula to quickly find out the most frequent (mode for) text values from a list without remembering any formulas in Excel.
1. Select a blank cell where you will place the most frequent (mode for) text value, and click Kutools > Formulas > Find most common value. See screenshot:
2. In the opening Formula Helper dialog box, please specify the list where you will look for the most frequent text value into the Range box, and click the Ok button.
And then the most frequent (mode for) text value has been found and returned into the selected cell.