How to find the most common values in a list in Excel
Excel formula
=TAKE(SORT(HSTACK(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15))),2,-1),3)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).
- Result on the example
- a 3-row list that spills from D2 in
D2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
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.
=TAKE(SORT(HSTACK(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15))),2,-1),3)Result{3 rows × 2 cols} "Billing", 5, "Login", 4, …
B2:B1514 rows × 1 col: "Billing", "Login", "Shipping", "Billing", …A range: 14 cells in one column.
UNIQUE(B2:B15){5 rows × 1 col} "Billing", "Login", "Shipping", "Returns", …UNIQUE: Distinct rows of a range. Spills.- array
B2:B1514 rows × 1 col: "Billing", "Login", "Shipping", "Billing", …
- array
UNIQUE(B2:B15){5 rows × 1 col} "Billing", "Login", "Shipping", "Returns", …UNIQUE: Distinct rows of a range. Spills.- array
B2:B1514 rows × 1 col: "Billing", "Login", "Shipping", "Billing", …
- array
COUNTIF(B2:B15,UNIQUE(B2:B15)){5 rows × 1 col} 5, 4, 3, 1, …COUNTIF: Counts cells that meet one condition.- range
B2:B1514 rows × 1 col: "Billing", "Login", "Shipping", "Billing", … - criteria
UNIQUE(B2:B15){5 rows × 1 col} "Billing", "Login", "Shipping", "Returns", …
- range
HSTACK(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15))){5 rows × 2 cols} "Billing", 5, "Login", 4, …HSTACK: Places ranges side by side.- array1
UNIQUE(B2:B15){5 rows × 1 col} "Billing", "Login", "Shipping", "Returns", … - array2
COUNTIF(B2:B15,UNIQUE(B2:B15)){5 rows × 1 col} 5, 4, 3, 1, …
- array1
-1-1Makes the value negative.
SORT(HSTACK(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15))),2,-1){5 rows × 2 cols} "Billing", 5, "Login", 4, …SORT: Sorts rows by one or more columns. is_ascending is TRUE or FALSE.- range
HSTACK(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15))){5 rows × 2 cols} "Billing", 5, "Login", 4, … - sort_column
2 - is_ascending
-1-1
- range
TAKE(SORT(HSTACK(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15))),2,-1),3){3 rows × 2 cols} "Billing", 5, "Login", 4, …TAKE: First (or last, if negative) rows or columns.- array
SORT(HSTACK(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15))),2,-1){5 rows × 2 cols} "Billing", 5, "Login", 4, … - rows
3
- array
In Google Sheets
Google Sheets needs a different formula: =SORTN(ARRAYFORMULA(HSTACK(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15)))),3,0,2,FALSE). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(SORTBY(UNIQUE(B2:B15),COUNTIF(B2:B15,UNIQUE(B2:B15)),-1),1)Excel only | BillingDifferent result | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find the most common values in a list
D2, return the three most common ticket categories in B2:B15, each with its count, most common first.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).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.