How to Use the MODE.MULT and MODE.SNGL Functions in Google Sheets for Multimodal Analysis

In descriptive statistical analysis, identifying the central tendency of a dataset requires evaluating the mean (average), median (middle value), and mode (most frequently occurring value). While the legacy MODE function works for simple datasets, it has a critical mathematical limitation: if a dataset contains two or more values that tie for the highest frequency (a bimodal or multimodal distribution), legacy MODE returns only the first value it encounters and discards the rest.

To perform accurate statistical modeling, Google Sheets provides two modern replacement functions: MODE.SNGL (which returns a single modal value) and MODE.MULT (which outputs a dynamic vertical array containing every tied modal value). This tutorial explains how to formulate and interpret both functions for statistical workflows.

Syntax and Behaviour of MODE.SNGL and MODE.MULT

Both functions accept cell ranges, individual numbers, or comma-separated arrays.

MODE.SNGL Formula Syntax

=MODE.SNGL(value1, [value2, ...])

Calculates the single most frequently occurring value in a dataset. If two or more values share the highest frequency, MODE.SNGL returns the one that appears earliest in the source range.

MODE.MULT Formula Syntax

=MODE.MULT(value1, [value2, ...])

Calculates all frequently occurring values that share the highest frequency. Because modern Google Sheets supports dynamic array spilling, MODE.MULT automatically spills down as a vertical array of numbers across multiple cells.

Function	Distribution Handling	Output Dimensions
MODE.SNGL	Unimodal / Single value tie-break	Single cell numeric output.
MODE.MULT	Bimodal, Trimodal, Multimodal	Dynamic vertical array spilling downwards into adjacent empty rows.
Legacy MODE	Deprecated backwards compatibility	Single cell; identical mathematical output to MODE.SNGL.

Step-by-Step Example: Detecting Multimodal Survey Results

Consider an operational customer survey where respondents rated a software feature on a scale from 1 to 10. The score ledger resides in cells A2:A16:

Scores: 4, 7, 8, 5, 8, 9, 7, 3, 7, 2, 8, 6, 10, 5, 1

In this dataset, the number 7 appears 3 times, and the number 8 also appears 3 times. All other numbers appear fewer times.

Applying MODE.SNGL

Enter the following formula in cell C2:

=MODE.SNGL(A2:A16)

Google Sheets evaluates the range and returns 7 (because 7 appears prior to 8 in the source array). It completely hides the fact that score 8 was equally popular among respondents.

Applying MODE.MULT

Ensure that the cell directly below your formula (cell C5) is empty, and enter this formula in cell C4:

=MODE.MULT(A2:A16)

Google Sheets dynamically spills the complete list of modes:

• Cell C4 displays: 7
• Cell C5 displays: 8

By using MODE.MULT, business analysts immediately identify the bimodal distribution, revealing two distinct clusters of customer satisfaction.

Combining MODE.MULT with Dynamic Array Helpers

To count how many modes exist in your dataset or display them as a comma-separated text string inside a single cell, pair MODE.MULT with text and counting functions:

Counting Total Modes

=COUNT(MODE.MULT(A2:A16))

Returns 2, confirming that the dataset is bimodal.

Concatenating Modes into a Single Text Summary

="Most Frequent Scores: " & TEXTJOIN(", ", TRUE, MODE.MULT(A2:A16))

Outputs: Most Frequent Scores: 7, 8.

Troubleshooting Common MODE Function Errors

If your mode calculation throws formula errors, review these common statistical causes:

• #N/A Error (No Duplicates Found): Occurs if every number in the dataset appears only once (zero repetition). Unlike AVERAGE or MEDIAN, a mode does not mathematically exist if there are no recurring duplicates. Wrap your formula in IFERROR to handle this gracefully:

  =IFERROR(MODE.MULT(A2:A16), "No mode detected")

• #REF! Error (Spill Blocked): Triggered when MODE.MULT attempts to spill down into adjacent rows, but one of the destination cells already contains data or text. Clear the cells directly beneath the formula to resolve the collision.
• Ignoring Text and Boolean Values: Both MODE.SNGL and MODE.MULT ignore empty cells, text strings, and boolean flags (TRUE / FALSE). If your values are stored as numbers formatted as text, convert them first using VALUE().