Excel Mode With Text: Find the Most Frequent Text Value

Excel’s MODE.SNGL and MODE.MULT functions work with numbers, not words. To find the most frequent text value, count each entry with COUNTIF, then return the entry with the largest count; for values in A2:A20, a helper column makes the method easy to check and use.

Why MODE does not return a text mode

In statistics, a mode is the value that occurs most often. That value can be a category such as a product name or a survey response, but Excel’s MODE.SNGL function is designed to find a numeric mode. MODE.MULT returns multiple numeric modes when there is a tie; neither function is a direct solution for a list of words. If you give MODE.SNGL a range containing only text, it returns an error rather than selecting the most repeated word.

That distinction matters because Excel can store and compare text perfectly well, even though its MODE functions do not calculate a text mode. A frequency count is the missing step: count how often each text value appears, identify the greatest count, and return the matching value. This approach also lets you inspect the counts instead of relying on an opaque result.

Recommended method: count entries in a helper column

Suppose the values are in A2:A20, with a heading in A1. In B2, enter the following formula and fill it down through B20. It leaves blank source cells out of the count and counts how often each nonblank value appears in the full source range.

With the source values in A2:A20 and the count helper in column B, enter this in B2 and fill down to B20:

=IF(A2="","",COUNTIF($A$2:$A$20,A2))

Then return the value corresponding to the largest count. With counts in B2:B20 and the original values in A2:A20, enter this in another cell, such as D2:

=INDEX($A$2:$A$20,MATCH(MAX($B$2:$B$20),$B$2:$B$20,0))

MAX finds the highest helper-column count, MATCH locates the first row containing that count, and INDEX returns the value from the same row in column A. The dollar signs keep the ranges fixed when formulas are copied. The relative A2 reference in the helper formula changes to A3, A4, and so on as you fill down.

Worked example and tie behavior

Imagine A2:A10 contains Cedar, Maple, Birch, Maple, Cedar, Maple, Birch, Maple, and Cedar. Maple appears four times, Cedar three times, and Birch twice. The helper formula assigns each Maple row a count of 4, each Cedar row a count of 3, and each Birch row a count of 2. The result formula returns Maple, the most frequent entry.

If two or more entries share the highest count, the INDEX and MATCH formula returns the one whose first occurrence is earliest in the source range. For example, if Cedar and Maple each appear three times, and Cedar appears first, this formula returns Cedar. This is a tie-breaking rule built into the formula’s search order, not a claim that Cedar is statistically more common.

If you need to report every tied mode, the single-result formula is not enough. You can review the helper counts, filter them to the highest value, or build a PivotTable that groups the text labels and displays their counts. For a short list, the helper column is often the clearest option because it shows exactly why a value won or tied.

Handling blanks, spaces, and inconsistent labels

Blank cells can distort a frequency result if they are counted as a category. The helper formula above returns an empty string for a blank source cell, so MAX considers the numeric counts rather than treating blank entries as a repeated label. If every source cell is blank, however, there is no meaningful text mode to return; add or check for at least one nonblank value before using the result formula.

Text that looks alike may not be identical. For example, “North” and “North ” (with a trailing space) can be counted as separate entries. The same issue arises with leading spaces, inconsistent abbreviations, or labels such as “In progress” and “In-Progress.” Clean the source values first if those variations should represent the same category. TRIM can remove ordinary leading and trailing spaces, but it does not resolve every possible source of inconsistent text.

COUNTIF is not case-sensitive, so “east” and “East” are counted together. It also recognizes asterisks and question marks as wildcard characters in criteria. That can matter if a literal label contains one of those characters: a COUNTIF criterion may then match more than the literal text you intended. For ordinary names and categories this is rarely an issue, but for codes containing wildcard symbols, verify the counts or use a counting approach designed for literal comparisons.

Using the result with changing data

The example uses a fixed range ending at row 20. If you add new records below row 20, update both formulas so their source and count ranges include those rows; otherwise the new values are excluded. One practical option is to convert the data into an Excel Table using Insert > Table. Tables expand when rows are added, which can make a growing list easier to maintain, although the example formulas here use ordinary cell references.

For a one-time calculation, a fixed range is straightforward. For a recurring report, keep the source values and helper counts together, label the columns clearly, and check that the ranges cover all current records. If the source is filtered, note that COUNTIF still counts matching entries in hidden rows. Filtering the sheet changes what is displayed, not the range that COUNTIF evaluates.

Alternative: summarize the text with a PivotTable

A PivotTable is useful when you want a count for every category, not just the mode. Select the source list, choose Insert > PivotTable, and place the text field in both the Rows area and the Values area. Excel normally summarizes a text field in Values by counting it. Sort the count column from largest to smallest to put the most frequent label at the top.

A PivotTable is often more convenient for a long list or a report you need to refresh. It also makes ties visible because the tied labels appear with equal counts. Unlike the helper formulas, a PivotTable is a summary view that you refresh after the underlying source data changes. For a small list or a result needed directly in a worksheet cell, the helper-column method is usually simpler.

Common problems and checks

The formula returns an error: Confirm that the source and helper ranges have matching sizes, that the helper formula has been filled down, and that the source contains at least one nonblank value.
The result is not the label you expected: Inspect the helper counts. Hidden spaces, inconsistent spelling, or wildcard characters can affect which entries COUNTIF groups together.
The mode ignores newly added records: Extend the ranges in both formulas or use a source structure that expands as data is added.
There is more than one most frequent value: The single-result formula returns the first tied value in the list. Check the largest helper counts to identify other tied entries.
Filtered rows seem to affect the result: COUNTIF includes matching rows hidden by a filter. A normal worksheet filter does not make COUNTIF count only visible cells.

Frequently Asked Questions

Can Excel calculate the mode of words directly?
Not with MODE.SNGL or MODE.MULT; those functions are for numbers. Use a frequency-count method such as COUNTIF with INDEX and MATCH, or use a PivotTable to count text categories.

Does the formula return all text values tied for the mode?
No. The INDEX and MATCH formula returns one value: the first tied value in the source range. Check the helper counts or use a PivotTable if you need to list every label tied for the highest frequency.

Does Excel treat uppercase and lowercase text as different?
COUNTIF is case-insensitive, so entries such as “Green” and “green” are counted together. If capitalization should distinguish categories, standardize the labels or use a case-sensitive counting method instead.

Can I find the most frequent number with the same method?
Yes. COUNTIF can count repeated numbers as well as text. The helper-column method is especially useful when you want to see every frequency, while MODE.SNGL is a shorter option for finding a numeric mode.

Why does the answer change when I sort the list?
The highest count should remain the same, but if there is a tie, the formula returns the tied value that appears first. Sorting can change that order, so a different tied value may be returned even though the tied frequencies have not changed.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.