Find the Most Frequent Text Value in Excel

To find the most frequent text in Excel, you can't use MODE on its own, because MODE only works with numbers. The fix is to turn each text value into a number first, with MATCH, and then let MODE and INDEX do the rest. This guide shows that formula, a COUNTIF alternative that also gives the count, a Microsoft 365 version, and how to handle ties, blanks and conditions.

Why MODE doesn't work on text#

MODE returns the most common number in a range, and it ignores text. On a column of words, it finds no numbers at all and returns #N/A. The guide to MEDIAN and MODE covers MODE on numbers.

All the examples use this support-ticket list in A1:B8:

Login appears three times, Billing twice, and Shipping and Refund once each. The answer we want is Login.

How to find the most frequent text with INDEX, MODE and MATCH#

This classic formula works in every version of Excel:

=INDEX(B2:B8,MODE(MATCH(B2:B8,B2:B8,0)))

It works from the inside out:

The trick is step 1. MATCH looks up every category in the same list and returns where it first appears. Every Login becomes 2 and every Billing becomes 1, so the text is now numbers MODE can count. The guide to the MATCH function explains the 0 at the end, which asks for an exact match.

In Microsoft 365 and Excel 2021, press Enter as normal. In Excel 2019 and older, confirm the formula with Ctrl+Shift+Enter (Mac: ⌘⇧Return) so Excel treats it as an array formula; Excel then shows it in curly braces { }.

How to find the most frequent text with COUNTIF and MAX#

The COUNTIF version is longer, but each part is easier to check, and it gives you the count as well:

=INDEX(B2:B8,MATCH(MAX(COUNTIF(B2:B8,B2:B8)),COUNTIF(B2:B8,B2:B8),0))

To see how many times the winner appears, use just the middle part:

=MAX(COUNTIF(B2:B8,B2:B8))

That returns 3. The COUNTIF function guide and the article on counting duplicates show more ways to count repeated values. For how INDEX and MATCH combine in general, see INDEX MATCH explained, plus the INDEX function guide and MATCH function guide.

A Microsoft 365 version with UNIQUE and SORTBY#

In Excel 2021 or Microsoft 365, you can build a ranked list of categories by count and take the first:

=INDEX(SORTBY(UNIQUE(B2:B8),COUNTIF(B2:B8,UNIQUE(B2:B8)),-1),1)

UNIQUE lists each category once, COUNTIF counts each one, and SORTBY puts them in descending order (the -1). INDEX takes the first item: Login. Change the final 1 to 2 to get the second most frequent value, Billing.

To see the whole frequency table, type =UNIQUE(B2:B8) in D2 and =COUNTIF(B2:B8,D2#) in E2. The # refers to the whole spilled list. The guide to counting by category shows the pivot table way to do the same.

How to find the most frequent text with a pivot table#

If you'd rather not write a formula, a pivot table gives you every count at once, and works in all versions:

1. Click any cell in the list and choose Insert > PivotTable, then OK.
2. Drag Category into Rows.
3. Drag Category again into Values. Because it is text, Excel shows Count of Category.
4. Right-click any count and choose Sort > Sort Largest to Smallest.

The top row is the most frequent value: Login, with 3. A pivot table doesn't update by itself, so right-click it and choose Refresh after the data changes. Use the formulas when the answer must update automatically.

How to find the most frequent text for one condition#

To find the most common category for one agent, add IF inside MODE so that only that agent's rows count:

=INDEX(B2:B8,MODE(IF(A2:A8="Sara",MATCH(B2:B8,B2:B8,0))))

IF returns a position for Sara's rows and FALSE for everyone else's. MODE ignores FALSE, so it only counts Sara's tickets. The result is Billing, which Sara has twice. In Excel 2019 or older, confirm with Ctrl+Shift+Enter.

What happens with ties, blanks and no repeats#

These three edge cases catch people out:

• Ties. If two values appear the same number of times, every formula here returns the one that appears first in the list. With Billing, Login, Login, Billing, the answer is Billing. If ties matter, show the counts with the frequency table so readers can see them.
• Blank cells. An empty cell inside the range can make the MODE and MATCH version return #N/A. Keep the range to filled rows, or skip blanks with IF: =INDEX(B2:B20,MODE(IF(B2:B20<>"",MATCH(B2:B20,B2:B20,0)))).
• No repeats. If every value appears once, MODE has no most common number and returns #N/A. The COUNTIF version returns the first item instead. Wrap the MODE version in IFERROR to show a message: =IFERROR(INDEX(B2:B8,MODE(MATCH(B2:B8,B2:B8,0))),"No repeats").

Extra spaces also split one value into two. "Login" and "Login " count separately, so clean the column with TRIM if the data was typed by hand or imported.

Practise it#

These formulas combine three skills: counting, finding a position and returning a value. Practise INDEX and MATCH in a live spreadsheet: you type each formula and it checks it instantly. Then practise MEDIAN and MODE to see how MODE treats numbers, and practise COUNTIF to build the frequency counts behind the second method.