How to Use the MODE.MULT Function in Microsoft Excel for Multimodal Datasets

When performing statistical analysis on numeric datasets in Microsoft Excel, finding the central tendency typically involves calculating the mean, median, and mode. The mode represents the most frequently occurring value in a sample. However, real-world datasets often feature multimodal distributions—scenarios where two or more distinct values tie for the highest frequency. If you rely on traditional functions like MODE or MODE.SNGL, Excel returns only the very first mode it encounters, completely concealing subsequent modal values.

To accurately capture all tied modes, modern versions of Microsoft Excel supply the MODE.MULT function. Designed as a dynamic array formula in Excel 365 and Excel 2021, MODE.MULT calculates all modal values simultaneously and spills them vertically into adjacent cells. If you regularly build statistical dashboards or conduct numerical research in spreadsheets, you may also appreciate our tutorials on calculating median values in Excel and using the FREQUENCY function for data distributions.

Syntax and Behaviour of the MODE.MULT Function

The syntax for the MODE.MULT function in Microsoft Excel is structured as follows:

=MODE.MULT(number1, [number2], ...)

You can pass individual numbers, ranges (e.g., A2:A25), or multiple non-contiguous cell references containing up to 255 arguments.

Key operational characteristics include:

• Dynamic Spill: In modern Excel, MODE.MULT automatically spills an array vertically downward containing each tied mode.
• Error Handling: If the dataset contains no duplicate values (meaning every number appears exactly once), MODE.MULT returns a #N/A error, correctly signaling that no mathematical mode exists.
• Text and Empty Cells: Excel automatically ignores text strings, logical values (TRUE/FALSE), and blank cells contained within the referenced range.

Step-by-Step Example: Calculating Multimodal Exam Scores

Consider a practical scenario where a teacher or analyst records test scores across a group of students in cells A2:A13:

Student ID	Exam Score
ST-101	78
ST-102	85
ST-103	92
ST-104	78
ST-105	85
ST-106	64
ST-107	92
ST-108	78
ST-109	85
ST-110	92
ST-111	70
ST-112	81

In this dataset, the scores 78, 85, and 92 each appear exactly 3 times. If you enter =MODE.SNGL(B2:B13), Excel returns only 78.

To capture all three tied modes dynamically:

1. Click on an empty destination cell (for example, D2). Ensure that cells D3 and D4 beneath it are empty to prevent #SPILL! errors.
2. Enter the formula:

   =MODE.MULT(B2:B13)

3. Press Enter.
4. Excel instantly returns 78 in cell D2, 85 in cell D3, and 92 in cell D4 with a blue dynamic array spill boundary.

Transposing Multimodal Values Horizontally

By default, MODE.MULT outputs values vertically in a column. If your dashboard layout requires horizontal formatting across rows, wrap MODE.MULT inside the TRANSPOSE function:

=TRANSPOSE(MODE.MULT(B2:B13))

This formula spills the modal numbers horizontally across columns D2, E2, and F2.

Suppressing #N/A Errors on Datasets with No Mode

To keep presentation dashboards clean when a dataset might not contain duplicate values, nest MODE.MULT inside IFERROR:

=IFERROR(MODE.MULT(B2:B13), "No Mode Found")

If all numbers are unique, Excel cleanly displays the custom message rather than a raw formula error code.

Handling Legacy Excel Versions (Pre-Dynamic Array)

If you or your recipients open the workbook in legacy versions of Excel (such as Excel 2016 or 2013) that lack dynamic array spilling:

1. Highlight a vertical range of cells (e.g., D2:D5) beforehand.
2. Type =MODE.MULT(B2:B13) into the formula bar.
3. Press Ctrl + Shift + Enter simultaneously to commit as a legacy CSE array formula.
4. Cells beyond the actual count of modes will display #N/A, which can be concealed using conditional formatting.