Excel MODE.MULT: Find Multiple Modes in a Data Set

Yes. In Microsoft 365 Excel for Windows, use MODE.MULT to return every value tied for the highest frequency in a data set. With the numbers in A2:A10, enter =MODE.MULT(A2:A10) in an empty cell; in current Excel, the results spill into the cells below it.

What multiple modes mean

The mode is the value, or values, that occur most often in a set of observations. A data set can have one mode, several modes, or no mode under the usual statistical definition. When two or more different values share the greatest frequency, the data is multimodal. MODE.MULT is Excel’s function for returning that full set of tied values, rather than just one of them.

For example, if two different scores each appear four times and every other score appears fewer than four times, both scores are modes. A value does not become a mode merely because it appears more than once: it must reach the highest occurrence count. If the most frequent value appears five times and another appears twice, only the five-time value is a mode.

Mode is useful for categorical or numeric data where the most common response matters: survey choices, product sizes, repeated measurements, or transaction amounts. Excel stores dates and times as numbers, so they can also have a mode if the underlying serial values repeat. The result describes frequency, not an average or a measure of how close values are to one another.

How MODE.MULT returns results

MODE.MULT evaluates the supplied numbers and returns the value or values with the greatest frequency. In Microsoft 365, enter the formula once in a cell and Excel places its results in a vertical spill range. If there is a single mode, the result occupies one cell; if there are tied modes, the returned values occupy multiple cells. Keep the cells below the formula clear so Excel can display the entire result.

The formula uses a range or numeric arguments. With observations in A2:A10, the basic formula is =MODE.MULT(A2:A10). It is not necessary to sort the source data first, and sorting does not change which values have the highest frequency. The formula also updates when values in the referenced range change, provided the range includes the edited cells.

MODE.MULT returns the modes, not a frequency table. It will not tell you how many times each mode occurs. If you need the counts as well, calculate or display those separately—for example, with a PivotTable or a frequency-counting method appropriate to the layout. This distinction matters when explaining results: two modes can tie at a frequency of two or at a frequency of two thousand.

Worked example with tied values

Suppose A2:A10 contains 4, 4, 7, 7, 7, 9, 9, 9, and 12. The counts are: 4 appears twice, 7 appears three times, 9 appears three times, and 12 appears once. Therefore, 7 and 9 tie for the maximum count. With those values in A2:A10, enter =MODE.MULT(A2:A10) in an empty cell. Excel returns 7 and 9 in the spill range.

This example also shows why the answer is not every repeated value. Although 4 appears more than once, it occurs less often than 7 and 9, so it is not a mode. The mode is determined by the maximum count, not by whether a value has any duplicate at all. If 9 appeared only twice instead, 7 would be the sole mode because it would occur three times.

For a quick check, count the observations for each candidate value and compare their counts. In the example, the highest count is three, and exactly two values reach it. This manual check is especially helpful when a result seems surprising or when source values look identical on screen but may differ internally.

Entering the function in Excel

In Microsoft 365 Excel for Windows, follow these steps:

Put the observations in a single range, such as A2:A10. A heading can sit above the range, but do not include it in the formula range.
Select an empty cell with enough clear space below it for the results.
Type the formula using the actual data range, then press Enter. For the example range, use =MODE.MULT(A2:A10).
Read the returned value or values in the formula cell and the cells immediately below it. Do not type into those spill cells while the formula is there.

In current Excel for Mac and Excel for the web, dynamic-array formulas also normally spill after you press Return or Enter. The Mac key labels and menu layout can differ, but the function syntax is the same. Older desktop versions that support MODE.MULT but do not have dynamic arrays require selecting a vertical output range first, entering the formula, and confirming it as an array formula with Ctrl+Shift+Enter on Windows. In those older versions, use the equivalent Control+Shift+Return array-entry shortcut on Mac; Microsoft 365 does not require that legacy keystroke.

Data the function counts

MODE.MULT is intended for numeric values. When a range reference contains text, logical values, or blank cells, those entries are ignored. Numeric zero is not blank: it is a valid value and can be a mode. If a cell contains a number stored as text, it may not be treated like a numeric cell, so convert or clean imported data when the results do not match expectations.

Dates and times are numeric values in Excel, even though they are commonly displayed in a readable date or time format. Thus, two cells containing the same underlying date can count as repeats. Conversely, values that look the same because of number formatting may be different underneath. For instance, a cell formatted to show no decimal places could display 2 for both 1.6 and 2.4, although those stored numbers are not equal. Check the underlying values or increase displayed precision before concluding that Excel missed a tie.

Errors in the source range can interfere with the calculation. Correct or exclude invalid source entries before interpreting a result. Also check that the selected range covers the entire data set: omitting a row can change the highest frequency and therefore change the mode or create a tie that is not present in the full data.

Common errors and how to fix them

#N/A: This usually means no numeric value occurs more than once in the supplied data. If every observation is unique, there is no repeated mode for MODE.MULT to return. Confirm that duplicates are genuine numeric duplicates, not text-number differences or values hidden by formatting.
#SPILL!: In Microsoft 365, this means Excel cannot place all the results in the required spill area. Clear the cells below the formula, remove merged cells that obstruct the output, or move the formula to an area with room. The formula itself may be valid even when the spill destination is blocked.
Only one result appears: The data may have only one most-frequent value, or other repeated values may occur less often. Compare the frequency counts rather than assuming all duplicated values should be returned.
A result looks unexpected: Check blanks, text-formatted numbers, accidental spaces in imported data, the range boundaries, and hidden decimal places. Formatting changes what a value looks like, not necessarily the value Excel compares.

MODE.MULT versus MODE.SNGL

Use MODE.MULT when the complete set of tied modes matters. MODE.SNGL returns one mode only, so it is appropriate when a report requires a single answer and does not need to show ties. If multiple values tie, MODE.SNGL returns the lowest of the tied values. That single result can conceal the fact that the data has more than one mode.

The older MODE function is retained for compatibility with earlier Excel workbooks and behaves as a single-mode function. For new work in Microsoft 365, MODE.SNGL makes the one-result intent explicit, while MODE.MULT makes the multiple-result intent explicit. Neither function calculates the median or mean; those describe different aspects of a distribution.

Choose the function based on the question being asked. A classroom exercise asking for all most-common scores calls for MODE.MULT. A legacy template with one designated output cell may expect MODE.SNGL. If users need both the modes and their counts, a frequency table or PivotTable communicates more information than either mode function alone.

When a frequency table is a better choice

MODE.MULT is compact, but it does not show the distribution around the mode. A PivotTable can group values and show counts, making it easier to see whether the leading value is far ahead or tied closely with others. For a small fixed list, a manually prepared count table may be easier to audit. For regularly updated data, use a PivotTable that can be refreshed or a formula-based summary suited to the workbook’s version and structure.

Use a frequency table when readers need to see every category, not just the top-frequency value. For example, a survey report may need counts for all response choices, including responses that appear only once. MODE.MULT is better when the actual question is simply which numeric observation or observations occur most often. A chart of counts can add context, but it does not replace checking that the source data is clean and consistently coded.

Frequently Asked Questions

Does MODE.MULT return every value that appears more than once?
No. It returns only values tied at the highest frequency. A value that repeats fewer times than the most frequent value is not included.

Why does MODE.MULT return #N/A?
Usually, no numeric value repeats in the referenced data. It can also help to check whether apparent duplicates are stored differently, such as a number stored as text or numbers with hidden decimal differences.

Can MODE.MULT return text values?
It is a statistical function for numeric observations, and text in a referenced range is ignored. For categories such as names or colors, use a frequency table or PivotTable to identify the most common label.

Can I use MODE.MULT in Excel for the web?
Yes, in current Excel for the web, the function can return a dynamic array of results. Enter it in a cell with open space below, as you would in current Microsoft 365 desktop Excel.

How do I make MODE.MULT show results across columns?
MODE.MULT returns its results vertically. You can place the output in a vertical area or use Excel’s transpose options if a horizontal presentation is needed; the function’s native result orientation remains vertical.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.