MODE.MULT in Excel: Multiple Numeric Modes and Complete Array Results

• Introduced: the Microsoft function catalogue marks MODE.MULT as an Excel 2010 addition; an exact first build or preview date is not established here.
• Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016, Excel Web App.
• Checked: October 14, 2026; official MODE.MULT reference. Documentation and independently modeled examples; no native Excel execution or broader platform certification.

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

The reference’s displayed syntax contains an extra opening parenthesis, but its working examples use the ordinary function call shown above. Use a numeric range or separate numeric arguments.

The dedicated page describes array-formula entry and shows #N/A in surplus cells of a preselected legacy output range. It does not certify automatic spilling in every product in its Applies To list. Follow the Microsoft array-formula guidance for the actual client, and keep the needed output area unobstructed.

The shapes below are complete expected result sets. Where more than one mode is present, a universal ordering rule is not inferred from one vendor example.

Enter 2, 5, 2, 5, 9 and 12 in A2:A7:

=MODE.MULT(A2:A7)

The expected vertical result contains 2 and 5, each once. Both appear twice in the input, while 9 and 12 appear once. There are two modes, each with frequency 2.

Those two values are listed numerically here to identify the expected members, not to certify every native output ordering. Returning only one visible value does not establish that the other mode disappeared; inspect entry mode and output dimensions.

For 4, 4, 4, 7, 7 and 9, the only most-frequent value is 4. MODE.MULT therefore has one expected modal member, even though 7 also repeats.

The function does not return every repeated value. It returns the value or values attaining the highest frequency. This difference matters when a data-quality report is looking for all duplicates rather than the statistical mode.

Enter 1, 3, 5, 1, 3, 5 and 9 in D2:D8. Each of 1,3,5 occurs twice and 9 once, so the expected modal members are 1, 3 and 5.

On a separate helper area, list the candidate numbers 1,3,5,9 and calculate their frequencies with COUNTIF. Expected counts are 2, 2, 2 and 1. The largest count is 2, and exactly three candidates attain it.

This helper audit checks membership without assuming an undocumented ordering rule. A mode array is not a frequency table: the returned values do not themselves tell you the count 2.

Microsoft’s example uses a preselected array-output range larger than the number of modal values and shows #N/A in the unused cells. For a legacy three-mode result, reserve enough cells for the three values and interpret any surplus output separately.

Do not count a displayed #N/A tail as another data category or replace all errors without checking the entry context. A no-duplicate dataset also has a #N/A outcome, but that is a different situation from surplus cells after valid modes.

No native legacy-versus-dynamic-array session was run here. The source establishes the documented distinction; the actual client still needs an entry and output-shape check.

Microsoft explicitly suggests wrapping MODE.MULT with TRANSPOSE for horizontal output:

=TRANSPOSE(MODE.MULT(A2:A7))

For the first fixture, the expected shape is one row with two modal values, whose members are 2 and 5. The transpose changes orientation, not the values’ frequencies or their membership in the result.

Use an empty output area and the array-entry method appropriate to the client. An array formula returning several cells is not interchangeable with a scalar formula merely because its first cell looks correct.

Microsoft documents ignoring text, logical values and empty cells within a reference, while numeric zero is included. A controlled numeric list 0,0,3,5 therefore has a single modal member 0.

For 1,2,3,4, no value repeats, and the documented result is #N/A. The function does not return all four values as four equally frequent modes in that no-duplicate case.

Direct errors and text that cannot be interpreted numerically are documented to cause errors. A complete error-containing-reference and text-coercion matrix remains unverified here.

If one additional 5 is appended to the first fixture, its count becomes three while 2 remains at two. The expected modal result then has only 5. The number of output cells can therefore change when the data changes.

MODE.SNGL returns one value and is useful when the distribution has a unique mode. MEDIAN and AVERAGE answer different central-tendency questions. Retain a frequency audit when a multimodal shape matters to the interpretation.

• Microsoft MODE.MULT reference.
• Editorial testing standards.

Expected results are independent statistical/arithmetic models or explicitly documented rules, not captured native output. Examples use English function names and comma separators. Datasets and reporting policies are fictional.