How to Find and Use MODE in Google Sheets

August 7th, 2026

Average and median get most of the attention, but the mode — the most frequent value — is often the better summary for categories, discrete scores, and “what do people pick most?” questions. Google Sheets gives you MODE, MODE.SNGL, and MODE.MULT. This guide explains each one, how multimodal data behaves, and when mode beats average for both numbers and labels.

What mode means

In a list of values, the mode is the value that appears most often.

Example set: 2, 5, 5, 7, 9, 5, 2

• Mean (average): about 5
• Median: 5
• Mode: 5 (appears three times)

When every value appears once, there is no useful single mode — Sheets returns an error for MODE/MODE.SNGL in that situation. When two or more values share the highest frequency, the dataset is multimodal; MODE.SNGL picks one, MODE.MULT can return all of them.

MODE and MODE.SNGL

MODE and MODE.SNGL both return a single most frequent number. In practice they behave the same for typical use; MODE.SNGL is the newer, more explicit name (“single mode”).

=MODE(A2:A20)
=MODE.SNGL(A2:A20)

With A2:A20 containing survey scores mostly equal to 4, both return 4.

Multiple ranges

=MODE(A2:A20, C2:C20)

MODE can take several ranges or values and treats them as one pool.

Non-numeric values

Classic MODE / MODE.SNGL are built for numbers. Text categories need a different pattern (see below) — or count frequencies with QUERY/COUNTIF and take the max.

MODE.MULT — all modes when there is a tie

MODE.MULT returns an array of all modes when more than one value ties for most frequent.

=MODE.MULT(B2:B50)

If 3 and 7 both appear 12 times and nothing appears more often, MODE.MULT spills both values. MODE.SNGL would return only one of them (the first mode Sheets selects under its rules).

Because MODE.MULT is an array result, give it room to spill, or wrap it when you need a single cell:

=TEXTJOIN(", ", TRUE, MODE.MULT(B2:B50))

That produces a readable "3, 7" style summary for dashboards.

Multimodal data: what to report

| Situation | Good practice | | --- | --- | | One clear winner | MODE or MODE.SNGL | | Two or more tied winners | MODE.MULT (or list frequencies) | | Flat distribution (all unique) | Mode is not informative — use median/mean or show “no mode” | | Categories (text) | Frequency table + sort, not classic MODE |

Always look at counts, not only the mode value. A mode that appears 4 times in 1,000 rows is weak; a mode that appears 400 times is the story.

When mode beats average

Discrete scores and Likert scales

Ratings of 1–5 are not continuous. The average might be 3.4 while most people chose 4. Mode answers “what did people actually select most?” — often more actionable for product or HR surveys.

=MODE.SNGL(D2:D500)
=AVERAGE(D2:D500)
=MEDIAN(D2:D500)

Report all three when stakeholders care: mode for popularity, median for center under skew, average only if the scale is treated as numeric distance.

Integer counts (tickets, items per order)

“Most common order size” is a mode question:

=MODE.SNGL(OrderSizes)

Average order size can be pulled up by a few bulk buys; mode stays with the typical customer.

Categories and labels (text)

MODE does not replace a proper frequency analysis for text. Use COUNTIF or a pivot / QUERY:

=QUERY(A2:A100, "select A, count(A) where A is not null group by A order by count(A) desc label count(A) 'n'", 0)

The first row of that result is your modal category. Alternatively, for a single known list of labels in F2:F10:

=INDEX(F2:F10, MATCH(MAX(COUNTIF(A2:A100, F2:F10)), COUNTIF(A2:A100, F2:F10), 0))

(Enter as an array-aware formula; modern Sheets usually handles COUNTIF arrays without special keys.)

Quality control and “default” detection

If a sensor almost always reports 0 when idle, mode finds the idle reading. Average would mix idle zeros with real spikes and hide the pattern.

Practical patterns

Mode by group

For mode within a category, FILTER first:

=MODE.SNGL(FILTER(B2:B200, A2:A200 = "North"))

That is the most common numeric metric for the North region only.

Compare mode to average (skew check)

=MODE.SNGL(C2:C100) - AVERAGE(C2:C100)

A large gap flags skew or a heavy peak away from the mean — a cue to chart the distribution before trusting a single summary.

Guard against “no mode”

=IFERROR(MODE.SNGL(C2:C100), "No mode")

Useful on dashboards so empty or all-unique ranges do not show raw errors.

Top frequency count (how strong is the mode?)

=COUNTIF(C2:C100, MODE.SNGL(C2:C100))

Pair the mode value with its count so readers see strength, not only the label.

Mode vs median vs mean — quick chooser

| Goal | Prefer | | --- | --- | | Most common value / choice | Mode | | Middle value, robust to outliers | Median | | Arithmetic center, every unit counts equally | Mean (AVERAGE) | | Multiple peaks matter | MODE.MULT + chart | | Text categories | Frequency table (QUERY / pivot) |

Common mistakes

• Using MODE on free text without checking. Non-numeric data may error or be ignored depending on function; build a frequency table for labels.
• Ignoring multimodal ties. MODE.SNGL hides co-winners; use MODE.MULT or list counts when ties are plausible.
• Treating a weak mode as “the answer.” Always show frequency or share of total.
• Confusing mode with “most important.” Frequency is not importance — a rare high-value event can matter more than the modal trivial one.
• Forgetting FILTER/criteria. Global mode can be meaningless on mixed segments; compute mode per group.

Going further

Mode is the summary for “what shows up most,” not “what balances the total.” Use MODE or MODE.SNGL for a single numeric peak, MODE.MULT when ties matter, and frequency tables for categories. Combine with AVERAGE and MEDIAN when you need a full picture of center. For a wider statistical toolkit in Sheets, see Understanding Statistical Functions in Google Sheets.