How to Find the Most Common Text Value and Ignore Blanks in Excel
Question details
The user needs a formula to identify the most frequently occurring text string within a specific row range, explicitly ignoring any blank cells or numerical data.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Analyzing a dataset where the goal is to extract the most common categorical (text) value from a row containing mixed data types and empty spaces.
- Observed behavior
- Standard statistical functions like MODE only work with numbers, requiring alternative array formulas to properly evaluate and return text strings.
Verify that your version of Excel supports Dynamic Array functions like LET, FILTER, and XMATCH, as these solutions require Microsoft 365 or Excel 2021 and newer.
Use LET and FILTER Functions (Microsoft 365)
This is the most precise method to strictly isolate text values and filter out non-text values and blanks before calculating the most frequent occurrence.
This dynamic array formula uses the FILTER function combined with ISTEXT to isolate only the text cells. XMATCH maps the text strings to numerical positions, allowing MODE.MULT to find the most frequent item, which is then retrieved by INDEX.
Click on the cell where you want the most common text value to appear.
Type the formula: =LET(a,FILTER(K5:AF5,ISTEXT(K5:AF5)),INDEX(a,MODE.MULT(XMATCH(a,a)))) into the formula bar. Be sure to replace 'K5:AF5' with your actual target row range.
Press Enter. The formula will automatically calculate and display the most frequent text string while ignoring blanks and numbers.

Use TOROW to Exclude Blank Cells
Use this approach if your primary goal is to ignore blanks, even if the range contains a mix of text and numbers that you wish to evaluate together.
Use COUNTIFS and XLOOKUP
A highly readable array method that counts the occurrences of each cell value and looks up the highest count.
Easily Calculate Text Modes in WPS Spreadsheet
WPS Office fully supports advanced dynamic array functions like FILTER, LET, and XLOOKUP. You can seamlessly execute complex statistical array formulas without needing a premium Microsoft 365 subscription.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing the rows you want to analyze.
- 2. Select the formula cell: Click on the cell where you wish to calculate the most common text value.
- 3. Enter the dynamic formula: Paste your preferred formula (like the LET and FILTER combination) into the formula bar and press Enter to instantly spill the result.

Frequently Asked Questions
Why doesn't the standard MODE function work for text values?
The standard MODE and MODE.MULT functions in Excel are designed exclusively to evaluate numerical data. When applied to text cells, they return a #N/A error. To find the mode of text, you must use a combination of functions like INDEX and MATCH, or modern formulas like LET and FILTER to map text frequencies mathematically.
Will these dynamic formulas work in older versions of Excel?
Formulas utilizing LET, FILTER, TOROW, and XLOOKUP are only available in Microsoft 365 and Excel 2021 or newer. For older versions like Excel 2016, you would need to use complex traditional array formulas (entered with Ctrl+Shift+Enter) using INDEX, MATCH, and MAX to achieve similar results.
What happens if there is a tie for the most common text value?
It depends on the formula you use. The XLOOKUP and traditional INDEX/MATCH methods will typically return the first text value in the range that reaches the maximum count. If you use the MODE.MULT function wrapped inside LET and FILTER, Excel will return an array of all tied values, spilling them into adjacent cells.




