How to find the most frequent number in a range
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Get the value that appears most often — the statistical mode.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =MODE.SNGL(A2:A6) | MODE.MULT returns ALL tied modes as a spill; legacy =MODE() still works. |
| Google Sheets | =MODE(A2:A6) | MODE.SNGL also available; identical behavior. |
| LibreOffice Calc | =MODE.SNGL(A2:A6) | Identical. |
How it works
MODE.SNGL returns the single most frequent number — 2 appears three times here. With a tie it returns whichever mode occurs first, which is why MODE.MULT (spilling every tied value) exists. These only work on numbers; for the most frequent TEXT value use =INDEX(A2:A6,MATCH(MAX(COUNTIF(A2:A6,A2:A6)),COUNTIF(A2:A6,A2:A6),0)) — or UNIQUE+COUNTIF sorting in modern versions.
Verified, not just documented
We ran =MODE.SNGL(A2:A6) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 2 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 2 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
MODE.SNGL · MODE — see full Excel, Google Sheets & LibreOffice compatibility for each.