When a dataset has two equally popular values, MODE returns only one of them — silently dropping the other. Consequently, your analysis misses a key insight: the data is bimodal, and both values matter equally. The MODE.MULT function solves this. It returns all modes simultaneously as a vertical array — every value that appears most frequently, not just the first one Excel encounters.
This distinction matters for survey analysis, quality control, sales frequency reports, and any task where knowing all the most common values — not just one — changes the conclusion. This guide covers the full syntax, the array entry rules for each Excel version, and eight practical examples.
What Is a Statistical Mode?
The mode is the value that appears most often in a dataset. A dataset with one mode is called unimodal. A dataset with two equally frequent peak values is bimodal. Furthermore, a dataset with three or more is multimodal. Standard statistical software reports all modes. Excel's original MODE function reports only one — whichever appears first in the data. MODE.MULT corrects this by returning the complete set.
For example, in the dataset {3, 3, 5, 5, 7}, both 3 and 5 appear twice — the highest frequency. This dataset is bimodal. MODE or MODE.SNGL returns 3 (the first one encountered). MODE.MULT returns {3; 5} — both modes. Additionally, 7 is not a mode because it appears only once, which is less frequent than 3 and 5.
What Is the MODE.MULT Syntax?
| Argument | Required? | What it does |
|---|---|---|
| number1 | Required | The first number, range, or array to analyse. Can be a cell reference like A1:A100, individual numbers, or named ranges. Up to 255 arguments total. |
| number2, ... | Optional | Additional numbers or ranges to include in the analysis. All arguments are pooled into one dataset before modes are calculated. |
MODE.MULT ignores text, logical values (TRUE/FALSE), and empty cells in the range. Numbers stored as text are also ignored. Only genuine numeric values are counted. Zeros are included in the analysis — they count as a valid value.
MODE, MODE.SNGL, and MODE.MULT — Which Should You Use?
Three functions find the mode in Excel. Understanding the difference prevents the most common mistake: using MODE or MODE.SNGL on multimodal data and assuming the single result is complete.
| Function | Returns | When multimodal (two peaks) | Best for |
|---|---|---|---|
| MODE | A single number | Returns only the first mode found — silently discards others | Legacy compatibility only. Avoid in new files — use MODE.SNGL instead. |
| MODE.SNGL | A single number | Returns only the lowest mode — silently discards others | Confirmed unimodal data, or when only one summary value is needed for a formula. |
| MODE.MULT | A vertical array | Returns all modes — complete and accurate | Any dataset that may be multimodal. Always use this when you need a complete picture. |
Examples 1–4: Core Usage Patterns
The fundamental use is simply passing the data range to MODE.MULT. The function returns one result per mode. If only one mode exists, it returns a single value. If no value repeats, it returns #N/A.
When MODE.MULT fills more cells than there are modes, the extra cells show #N/A. In Excel 2019 and earlier this is unavoidable with raw MODE.MULT. Wrapping in IFERROR replaces those cells with a blank or a custom label, producing cleaner output. In Excel 365, the spill stops precisely at the last mode — so IFERROR is not needed for that purpose.
Displaying and Verifying Modes
MODE.MULT always returns a vertical array. For dashboards where modes should appear in a single row instead of a column, TRANSPOSE flips the orientation. This is specifically useful when embedding mode values alongside other summary statistics in a horizontal dashboard header.
MODE.MULT identifies the modes but does not show how frequently each one appears. Pairing it with COUNTIF in an adjacent column adds the frequency count alongside each mode value. As a result, you get a complete frequency summary in two columns.
Examples 5–8: Real-World Scenarios
Survey Analysis and Practical Applications
Net Promoter Score surveys, employee satisfaction ratings, and product reviews all produce numeric scores. A bimodal distribution — where two scores tie for most frequent — indicates a polarised audience. MODE.MULT reveals this split that a single-mode function would hide.
In order management, knowing the most common order quantity helps set default packaging sizes, minimum order quantities, and discount thresholds. MODE.MULT reveals when two quantity levels are equally popular — for example, customers split between ordering 10 and 50 units — which informs a completely different packaging strategy than a single peak at 25.
Dashboard cells often need a single text value instead of a spilled range. TEXTJOIN combined with IFERROR and MODE.MULT produces a comma-separated list of all modes in one cell. This is specifically useful for embedding mode results in report summary lines alongside other statistics.
When every value in the dataset is unique — no value repeats — MODE.MULT returns #NUM!. This error is a meaningful signal, not a bug. It means the data has no mode. Similarly, MODE and MODE.SNGL return #N/A for all-unique data. Consequently, wrapping in IFERROR handles both errors gracefully and keeps the output readable.
Common MODE.MULT Issues and How to Fix Them
Only one value appears instead of multiple modes
In Excel 2019 and earlier, entering MODE.MULT with a regular Enter key returns only the first mode — exactly like MODE.SNGL. You must use Ctrl+Shift+Enter, and you must first select a vertical range of cells large enough to hold all expected modes. If you pressed Enter normally, delete the formula, re-select the output range, retype the formula, and confirm with Ctrl+Shift+Enter.
#NUM! error — no repeating values in the data
MODE.MULT returns #NUM! when every value in the dataset appears exactly once. This is not an error in the formula — it is a correct statistical result. The data simply has no mode. Wrap in IFERROR to display a readable message: =IFERROR(MODE.MULT(A2:A100), "No repeated values").
Modes are showing in wrong order
MODE.MULT returns modes in the order they first appear in the data — not sorted by value or by frequency. Consequently, the first mode listed is not necessarily the smallest or most frequent. All modes in the output share the same frequency (that is the definition of multimodal). To sort the output, wrap in SORT: =SORT(IFERROR(MODE.MULT(A2:A100), "")) in Excel 365.
Frequently Asked Questions
-
What is the difference between MODE.MULT and MODE.SNGL?MODE.SNGL returns a single value — the mode that appears most frequently. If two values tie for most frequent, it returns only the lowest one and discards the other. MODE.MULT returns all modes as a vertical array — every value that shares the peak frequency. If your data may be multimodal and you want a complete picture, always use MODE.MULT. Use MODE.SNGL only when you specifically want a single result, or when you know the data is unimodal.
-
Why does MODE.MULT only return one value when I press Enter?In Excel 2019 and earlier, MODE.MULT must be entered as an array formula using Ctrl+Shift+Enter instead of a regular Enter. Without Ctrl+Shift+Enter, it behaves like MODE.SNGL and returns only one value. Additionally, you must pre-select a vertical range of cells large enough to hold all expected modes before typing the formula. In Excel 365 and 2021, this is not required — the formula spills results automatically when you press Enter normally.
-
What does #NUM! mean in MODE.MULT?#NUM! means no value in the dataset repeats. When every value appears exactly once, there is no mode — and MODE.MULT correctly returns an error to indicate this. It is not a formula error. Wrap in IFERROR to handle it gracefully: =IFERROR(MODE.MULT(A2:A100), "No mode found"). Also check whether your data contains numbers stored as text — MODE.MULT ignores text values and may return #NUM! even when duplicate numbers appear to exist.
More Questions About MODE.MULT
-
How many modes can MODE.MULT return?MODE.MULT can return as many modes as exist in the dataset — up to the total number of distinct values. In practice, most real-world datasets have one to three modes. In Excel 2019 and earlier, you limit the output by the size of the range you pre-select for array entry. In Excel 365, the formula spills as many results as needed with no upper limit. Extra cells pre-selected in Excel 2019 show #N/A, which you can suppress with IFERROR.
-
Can MODE.MULT work with text values?No. MODE.MULT only works with numbers. It ignores text values, logical values (TRUE/FALSE), and empty cells entirely. If you need to find the most frequently occurring text value — for example, the most common product name or country — use COUNTIF to count each distinct value and then INDEX+MATCH or MODE with a lookup table. Alternatively, use a PivotTable to count frequencies and identify the top text value.
-
Does MODE.MULT work in Google Sheets?Yes. MODE.MULT works in Google Sheets with identical syntax. Google Sheets handles array output automatically — no Ctrl+Shift+Enter is required. Enter the formula in a single cell and results spill downward. Additionally, Google Sheets does not show #N/A in extra cells below the modes — the spill stops precisely at the last mode value.