Function Problems

Why the MODE.MULT Function Returns Only One Result in Excel

Aamir Naveed Akram Sep 28, 2026 869 views

Question details

The user wants to understand why the MODE.MULT function is outputting only a single value instead of multiple values for a given dataset.

Why Does MODE.MULT Return Only One Result in Excel?

Product: Excel
Device & OS: not provided
Scenario: Calculating the most frequently occurring numbers in a dataset using statistical array functions.
Observed behavior: The formula returns only one result because only one number currently holds the absolute highest frequency in the selected data range.

Before you start

Review your dataset manually or use a COUNTIF function to verify if there is an actual tie for the highest frequency among your numbers.

Solution 1Recommended

Adjust Data for Tied Frequencies to Trigger Spilling

By default, MODE.MULT only returns multiple values if two or more numbers tie for the absolute highest frequency. Adjusting your dataset will demonstrate this behavior.

The MODE.MULT function is working exactly as designed. It evaluates the dataset to find the maximum occurrence of any number. If the number 1 appears four times and the number 2 appears three times, the absolute maximum frequency is four. Because 1 is the only number meeting this criterion, it is the only mode returned.

Review the current frequencies

Check your dataset to confirm the counts. If one number appears more times than any other, MODE.MULT will only output that single number.

Modify the dataset to create a tie

Change one of your existing values to match the frequency of the most common number (e.g., add another '2' so both '1' and '2' appear exactly four times).

Observe the dynamic array spill

Once the frequencies are perfectly tied, the MODE.MULT formula will automatically spill both values into the adjacent downward cells.

Adjust Data for Tied Frequencies to Trigger Spilling

Spill Error Prevention: Ensure there are empty cells directly below your MODE.MULT formula. If other data blocks the path, Excel will return a #SPILL! error when multiple modes try to display.

Solution 2

Use an Alternative UNIQUE and FILTER Formula in Microsoft 365

If you want to identify secondary modes or values that meet specific frequency thresholds without them tying for first place, you can use a combination of dynamic array formulas.

Data Analysis Made Easy

Calculate Data Frequencies Seamlessly with WPS Spreadsheet

WPS Office Spreadsheet fully supports dynamic array functions like MODE.MULT, making it incredibly easy to analyze your datasets and extract multiple most frequent values without complex workarounds.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your numerical data.
2. Enter the MODE.MULT function: Click an empty cell and type =MODE.MULT( followed by highlighting your data range, then close the parenthesis.
3. Calculate tied modes: Press Enter. WPS Spreadsheet will automatically calculate and spill all numbers that share the highest frequency.

100% compatible with Microsoft Excel statistical functionsFully supports dynamic array formulas and spilling behaviorFree, lightweight, and efficient for advanced data analysisFamiliar user interface for a seamless transition

microsoft office alternative - wps office

Frequently Asked Questions

What happens if there are no repeating numbers in my dataset?

If absolutely no numbers repeat in the selected dataset, the MODE.MULT function will return an #N/A error because a statistical mode does not exist.

How do I use MODE.MULT in older versions of Excel?

In versions of Excel prior to Microsoft 365 that do not support dynamic arrays, you must enter MODE.MULT as an array formula. Highlight a vertical range of blank cells, type the formula, and press Ctrl+Shift+Enter.

Why is my MODE.MULT formula returning a #VALUE! error?

The #VALUE! error typically occurs if the referenced range contains non-numeric data or text strings instead of numbers. Ensure your selected dataset is formatted as numbers.

Is there a difference between MODE.MULT and MODE.SNGL?

Yes. MODE.SNGL will always return only the single first mode it encounters, even if multiple numbers tie for the highest frequency. MODE.MULT is designed to output all tied values.

Excel & Data Problems