# How to find the most common text value in Excel

Turn each value into its first position with MATCH, take the mode, and look it up: `=INDEX(B2:B10,MODE.SNGL(MATCH(B2:B10,B2:B10,0)))`.

**Excel:** `=INDEX(B2:B10,MODE.SNGL(MATCH(B2:B10,B2:B10,0)))` (Every current version)

**Google Sheets:** `=ARRAYFORMULA(INDEX(B2:B10,MODE(MATCH(B2:B10,B2:B10,0))))`

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Ticket | Category |  | Most common |
| T-1 | Billing |  |  |
| T-2 | Login |  |  |
| T-3 | Shipping |  |  |
| T-4 | Login |  |  |
| T-5 | Billing |  |  |
| T-6 | Refund |  |  |
| T-7 | Login |  |  |
| T-8 | Shipping |  |  |
| T-9 | Refund |  |  |

Formula in E1: `=INDEX(B2:B10,MODE.SNGL(MATCH(B2:B10,B2:B10,0)))`

Result: Login

## How it works

MODE only works with numbers, so MATCH turns every category into the position of its first appearance: each "Login" becomes 2. The most common position is the most common category, and INDEX turns that position back into the text.

## Other ways

- `=XLOOKUP(MAX(COUNTIF(B2:B10,B2:B10)),COUNTIF(B2:B10,B2:B10),B2:B10)`: Excel 365: counts every row's category and returns the one with the highest count. Easier to read.

## Common mistakes

- Blank cells in the range make MATCH return #N/A and the whole formula fails. Point it at the filled rows only.
- With a tie, the formula returns the tied category that appears first in the list.

## Questions

**How do I also show how many times it appears?**

Count it: `=COUNTIF(B2:B10,E1)`.

**How do I rank every category by frequency?**

In Excel 365: `=SORTBY(UNIQUE(B2:B10),COUNTIF(B2:B10,UNIQUE(B2:B10)),-1)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/find-the-most-common-text-value