# How to find the most common values in a list in Excel

Count each unique value, sort by the count and keep the top rows: `=TAKE(SORT(HSTACK(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15))),2,-1),3)`.

**Excel:** `=TAKE(SORT(HSTACK(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15))),2,-1),3)` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=SORTN(ARRAYFORMULA(HSTACK(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15)))),3,0,2,FALSE)`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Ticket | Category |  | Category | Tickets |
| T-401 | Billing |  |  |  |
| T-402 | Login |  |  |  |
| T-403 | Shipping |  |  |  |
| T-404 | Billing |  |  |  |
| T-405 | Login |  |  |  |
| T-406 | Returns |  |  |  |
| T-407 | Billing |  |  |  |
| T-408 | Shipping |  |  |  |
| T-409 | Login |  |  |  |
| T-410 | Billing |  |  |  |
| T-411 | Other |  |  |  |
| T-412 | Shipping |  |  |  |
| T-413 | Login |  |  |  |
| T-414 | Billing |  |  |  |

Formula in D2: `=TAKE(SORT(HSTACK(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15))),2,-1),3)`

Result (spills 3 × 2): Billing, 5; Login, 4; Shipping, 3

## How it works

UNIQUE lists each category once and COUNTIF counts how often each one appears. HSTACK pairs them into a small table, SORT orders it by the count column (2), largest first, and TAKE keeps the first three rows. In Google Sheets, SORTN sorts and keeps the top rows in one step.

## Other ways

- `=INDEX(SORTBY(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15)),-1),1)`: Just the single most common value, without its count. (Only Billing.)

## Common mistakes

- MODE only works on numbers, so it can't find the most common text value. Count the unique values instead, as here.
- Ties at the cut-off are decided by the order the values first appear. Check the counts if third place matters.

## Questions

**How do I find the most common number?**

Use `=MODE(C2:C15)` for numbers. For text, use the formula on this page.

**How do I list the least common values?**

Sort ascending instead: `=TAKE(SORT(HSTACK(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15))),2,1),3)`.

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-values