The Excel MODE.MULT function returns a vertical array of all the most frequently occurring values in a data set — useful when the data has more than one mode.
Syntax
| Argument | Description | |
|---|---|---|
number1 | Required | The first number, cell reference, or range to evaluate for modes. |
number2, ... | Optional | Up to 254 additional numbers or ranges. |
How to use it
Where MODE.SNGL returns only the first mode, MODE.MULT returns every value tied for most frequent. It is an array function: in Excel 365 it spills down automatically; in older versions you select several cells and press Ctrl+Shift+Enter.
For the data {1, 2, 2, 3, 3, 4} both 2 and 3 occur twice, so MODE.MULT returns the vertical array {2; 3}. If every value is unique there is no mode and the function returns an #N/A error.
| A | |
|---|---|
| 1 | Score |
| 2 | 2 |
| 3 | 2 |
| 4 | 3 |
| 5 | 3 |
| 6 | =MODE.MULT(A2:A5) → spills 2 then 3 |
Only one mode? If there is a single most-frequent value, MODE.MULT returns just that one value — behaving like MODE.SNGL. The array form matters only when several values tie.
Try it: interactive demo
Pick a MODE.MULT example to see the formula and its result.
Practice workbook
Frequently asked questions
How is MODE.MULT different from MODE.SNGL?
MODE.SNGL returns a single value — the first mode it finds. MODE.MULT returns an array of all values tied for most frequent, so it can report two or more modes at once.How do I enter MODE.MULT so it shows every mode?
What if there is no repeated value?
#N/A error. Wrap it in IFERROR if you want a friendlier message.Does MODE.MULT only return numbers?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class