Function Problems

How to Find the Most Common Text Value and Ignore Blanks in Excel

Adam DavisAdam Davis Nov 10, 2026 869 views

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.

How to Find the Most Common Text Value and Ignore Blanks in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the destination cell

Click on the cell where you want the most common text value to appear.

2
Enter the dynamic array formula

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.

3
Execute the formula

Press Enter. The formula will automatically calculate and display the most frequent text string while ignoring blanks and numbers.

Use LET and FILTER Functions (Microsoft 365)
Handling Ties: Because this formula uses MODE.MULT, if multiple text values tie for the highest frequency, Excel will spill all tied results into adjacent cells.
Advanced Data Analysis

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. 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing the rows you want to analyze.
  2. 2. Select the formula cell: Click on the cell where you wish to calculate the most common text value.
  3. 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.
Full compatibility with Microsoft Excel formulas and .xlsx filesNatively supports modern dynamic arrays (XLOOKUP, FILTER, LET)Lightweight and lightning-fast even with massive datasetsFree to download and user-friendly interface
microsoft office alternative - wps office

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.