Written and maintained by CASRAI Editorial Board
Last updated
To calculate the mode in Excel, type =MODE.SNGL(A2:A7) with the range that holds your numbers. It returns the value that occurs most often. That one function hides a problem: if two or more values tie for most frequent, MODE.SNGL gives you a single answer and says nothing about the others. This guide gives the steps as Microsoft documents them, then works through ties with MODE.MULT, shows which cells Excel ignores, and explains why a column with no repeated values returns #N/A.
What it is used for in research
The mode is the most frequently occurring value in a data set. Microsoft presents it as one of three common measures of central tendency, alongside the average and the median. In research reporting it is the measure to reach for when you want the most common response or code, such as the most frequent score on a rating item or the most common category among numerically coded groups. Because a mode only exists when values repeat, it suits discrete or coded data better than measurements with many decimal places. For the wider choice between summarising and testing, see descriptive versus inferential statistics. If your data has two peaks, the mode alone misleads; the guide to the multimodal distribution explains how to report that.
Install or access (official source only)
The mode functions are built into Excel; there is nothing to install. Microsoft documents them on its support site: MODE.SNGL function, MODE.MULT function and MODE function. Microsoft lists MODE.SNGL as applying to Excel for Microsoft 365 (Windows and Mac), Excel 2024, Excel 2021 (Windows and Mac), Excel 2019 and Excel 2016.
Step-by-step: calculate the mode in Excel
Which function you need depends on whether you expect one mode or want to check for several.
| Function | What it returns | Notes from Microsoft |
|---|---|---|
MODE.SNGL(number1,[number2],...) |
One value: the most frequently occurring | Accepts 2 to 254 arguments, or a single array or reference |
MODE.MULT(number1,[number2],...) |
A vertical array of every most-frequent value | Must be entered as an array formula; for a horizontal result use TRANSPOSE(MODE.MULT(...)) |
MODE(number1,[number2],...) |
One value, as MODE.SNGL | Replaced by MODE.MULT and MODE.SNGL; still available for backward compatibility but may be removed in a future version |
How to find the mode in Excel with MODE.SNGL
- Enter your numbers in a column, for example
A2:A7. - Click an empty cell.
- Type
=MODE.SNGL(A2:A7)and press Enter.
Using Microsoft’s own example, with A2:A7 holding 5.6, 4, 4, 3, 2 and 4, the formula returns 4, because 4 appears three times and every other value appears once. The older =MODE(A2:A7) returns the same result on the same data.
How to calculate the mode in Excel when there are several modes
- Enter your numbers, for example in
A2:A13. - Select a vertical block of output cells that is larger than the number of modes you expect. Microsoft’s example selects eight cells.
- Enter
=MODE.MULT(A2:A13)as an array formula across the selected cells.
Microsoft’s data in A2:A13 is 1, 2, 3, 4, 3, 2, 1, 2, 3, 5, 6, 1. The values 1, 2 and 3 each occur three times, so MODE.MULT returns 1, 2 and 3 in the first three output cells. The remaining five cells show #N/A. If the same formula is not entered as an array formula, Microsoft states the result is a single value, 1 in this example, the same as MODE.SNGL. To spread the results across a row instead of down a column, wrap the call: =TRANSPOSE(MODE.MULT(A2:A13)).
Output and how to read it
Read the result against the data you gave it. For A2:A7 the answer 4 is unambiguous. For A2:A13, MODE.SNGL would report one number while three values are tied at three occurrences each, so a report that says “the mode is 1” would leave out two equally common values. Run MODE.MULT whenever you are not sure the maximum frequency belongs to one value only, and read the #N/A cells at the bottom of the block as unused output cells, not as errors in your data: Microsoft notes that selecting more cells than there are modes shows #N/A in the extras.
What Excel counts and what it ignores
- Numbers, names, arrays and references that contain numbers are accepted as arguments.
- Text, logical values and empty cells inside an array or reference argument are ignored.
- Cells containing zero are included, so a column of blank cells and a column of zeros do not behave alike.
- Error values, or text that cannot be translated into numbers, cause errors.
One consequence for survey data: because text in a range is ignored, a column of text labels such as “agree” and “disagree” cannot be passed to these functions. Code the categories as numbers first, and then read the returned number as a label code, not as a quantity. To tabulate categories by count instead, see crosstabs in Excel.
Troubleshooting
- The result is #N/A. Microsoft states that if the data set contains no duplicate data points,
MODE.SNGL,MODE.MULTandMODEreturn #N/A. Nothing is broken: with no repeated value there is no mode. Continuous measurements recorded to many decimal places often have no repeats. MODE.MULTshows only one value. The formula was not entered as an array formula. Microsoft states that without array entry the single result matchesMODE.SNGL.- #N/A appears below the modes. You selected more output cells than there are modes. Microsoft’s own example shows five of them.
- The answer ignores some of your data. Text, logical values and empty cells are skipped. Check for numbers stored as text.
- A formula returns an error rather than a value. Error values in the range, or text that cannot be converted to a number, cause errors in these functions.
Free alternatives
LibreOffice Calc has the same three functions. Its help pages describe behaviour that differs from Excel in two places worth knowing if you share files between the programs.
| Situation | Excel (Microsoft Support) | LibreOffice Calc (LibreOffice Help) |
|---|---|---|
| Several values tie for most frequent, single-value function | Use MODE.MULT to list all of them |
MODE and MODE.SNGL return the smallest of the tied values |
| No value repeats | #N/A | MODE.SNGL returns #VALUE! |
| All modes | MODE.MULT entered as an array formula |
MODE.MULT entered as an array formula; otherwise only the first mode is returned |
For other no-cost tools, see free statistical software for research and the JASP guide. If you export from SPSS, reading SPSS output explains the tables you will meet.
FAQ
How do I find the mode in Excel?
Type =MODE.SNGL(range) in an empty cell, replacing range with the cells holding your numbers, and press Enter. It returns the most frequently occurring value. If your data may have ties, use MODE.MULT instead.
How do I do mode in Excel when more than one value is the mode?
Use MODE.MULT, selecting a vertical block of cells and entering the formula as an array formula. It returns every value tied for most frequent. For a row of results, wrap it in TRANSPOSE.
Why does the mode in Excel return #N/A?
Because no value in the range occurs more than once. Microsoft documents #N/A for this case in all three mode functions.
Which function should I use, MODE or MODE.SNGL?
Use MODE.SNGL. Microsoft lists MODE as replaced by MODE.MULT and MODE.SNGL, kept for backward compatibility, and says it may be removed in future versions.
Does Excel count zeros and blanks when finding the mode?
Zeros are counted. Empty cells, text and logical values inside a range or array are ignored.
Sources (official documentation)
- Microsoft Support, MODE.SNGL function, checked 2026-10-29.
- Microsoft Support, MODE.MULT function, checked 2026-10-29.
- Microsoft Support, MODE function, checked 2026-10-29.
- LibreOffice Help, Statistical Functions Part Four, checked 2026-10-29.
