# How to Calculate Mode in Excel (Step by Step)

To calculate the mode in Excel, use the MODE.SNGL function for the single most frequent value or MODE.MULT for every value that ties for the highest frequency. Both functions take a range of numbers and return the value that repeats most often. This guide shows how to calculate mode in Excel step by step, with a worked example and the errors you are most likely to hit.

## Quick Answer

- **Single mode:** `=MODE.SNGL(B2:B21)` returns the one most frequent value in the range.
- **All modes:** `=MODE.MULT(B2:B21)` returns every value tied for the highest count. In older Excel versions it returns an array, so you select several cells first and confirm with Ctrl+Shift+Enter.
- **Legacy function:** `=MODE(B2:B21)` still works but Microsoft recommends the newer functions because MODE may not be available in future versions of Excel [1].
- **Text and blanks are ignored.** Only numeric values count toward the frequency.
- **No repeats means an error.** If every value appears once, MODE.SNGL returns `#N/A`.

## Before You Start

Mode is the value that appears most often in a dataset. It is the third of the three common averages, alongside the mean and the median. The mean is the arithmetic average, the median is the middle value, and the mode is the most frequent value. For a quick comparison of all three, the [Mean, Median & Mode Calculator](/tools/mean-median-mode-calculator) lets you check your numbers before you build the sheet.

Two things to confirm before you write a formula.

First, your data should be numeric. The MODE functions work on numbers, names, arrays, or references that contain numbers [1]. If your column holds text labels, MODE.SNGL will not count them.

Second, decide whether you expect one mode or several. A dataset can have one mode, multiple modes, or no mode at all. MODE.SNGL returns only one value even when several tie. MODE.MULT returns all of them.

If you are new to writing formulas, the basics in [How to Make a Formula in Excel](/blog/data-analysis/how-to-make-formula-in-excel) cover cell references and the equals sign convention.

## Step by Step

1. **Enter your data in a single column.** Put the values you want to analyze in one contiguous range, for example B2:B21. Keep the column free of merged cells and stray text.

2. **Click an empty cell for the result.** Choose a cell outside the data range so the result does not overwrite a value.

3. **Type the MODE.SNGL formula.** Enter `=MODE.SNGL(B2:B21)` and press Enter. Excel returns the single most frequent number.

4. **Type the MODE.MULT formula in a second cell.** Enter `=MODE.MULT(B2:B21)` and press Enter. If only one value is the mode, it returns that same number.

5. **Read the result.** Compare the returned value against your data to confirm the count. If you want to verify the frequency, use `=COUNTIF(B2:B21,4)` with the returned mode as the criteria.

6. **Handle multiple modes.** If two or more values tie, MODE.SNGL returns only the first one it finds. Use MODE.MULT and select enough cells vertically to hold every tied value before you enter the formula.

The syntax for both functions is the same. `Number1` is required and holds the first number or range. `Number2` and beyond are optional and accept up to 255 number arguments, or you can pass a single array or range reference instead [1].

## Worked Example

The dataset below holds twenty survey ratings on a 1 to 5 scale, one row per respondent. Column B holds the rating, column C labels whether the rating equals 4, and column D holds the two mode formulas.

| Row | A: Respondent | B: Rating | C: Rating Label | D: Mode Result |
|---|---|---|---|---|
| 1 | Respondent | Rating | Rating Label | Mode Result |
| 2 | R1 | 4 | `=IF(B2=4,"Most frequent","Other")` -> displays Most frequent | `=MODE.SNGL(B2:B21)` -> displays 4 |
| 3 | R2 | 5 | `=IF(B3=4,"Most frequent","Other")` -> displays Other | `=MODE.MULT(B2:B21)` -> displays 4 |
| 4 | R3 | 3 | `=IF(B4=4,"Most frequent","Other")` -> displays Other | |
| 5 | R4 | 4 | `=IF(B5=4,"Most frequent","Other")` -> displays Most frequent | |
| 6 | R5 | 2 | `=IF(B6=4,"Most frequent","Other")` -> displays Other | |
| 7 | R6 | 4 | `=IF(B7=4,"Most frequent","Other")` -> displays Most frequent | |
| 8 | R7 | 5 | `=IF(B8=4,"Most frequent","Other")` -> displays Other | |
| 9 | R8 | 4 | `=IF(B9=4,"Most frequent","Other")` -> displays Most frequent | |
| 10 | R9 | 1 | `=IF(B10=4,"Most frequent","Other")` -> displays Other | |
| 11 | R10 | 4 | `=IF(B11=4,"Most frequent","Other")` -> displays Most frequent | |
| 12 | R11 | 3 | `=IF(B12=4,"Most frequent","Other")` -> displays Other | |
| 13 | R12 | 4 | `=IF(B13=4,"Most frequent","Other")` -> displays Most frequent | |
| 14 | R13 | 5 | `=IF(B14=4,"Most frequent","Other")` -> displays Other | |
| 15 | R14 | 4 | `=IF(B15=4,"Most frequent","Other")` -> displays Most frequent | |
| 16 | R15 | 2 | `=IF(B16=4,"Most frequent","Other")` -> displays Other | |
| 17 | R16 | 4 | `=IF(B17=4,"Most frequent","Other")` -> displays Most frequent | |
| 18 | R17 | 4 | `=IF(B18=4,"Most frequent","Other")` -> displays Most frequent | |
| 19 | R18 | 3 | `=IF(B19=4,"Most frequent","Other")` -> displays Other | |
| 20 | R19 | 4 | `=IF(B20=4,"Most frequent","Other")` -> displays Most frequent | |
| 21 | R20 | 4 | `=IF(B21=4,"Most frequent","Other")` -> displays Most frequent | |

The rating 4 appears eleven times, more than any other value. `=MODE.SNGL(B2:B21)` returns 4, the single most frequent rating in the range. `=MODE.MULT(B2:B21)` also returns 4, because 4 is the only mode here. If a second rating had tied with eleven appearances, MODE.SNGL would still return just one value while MODE.MULT would return both.

## Other Ways to Do It

**The legacy MODE function.** `=MODE(B2:B21)` returns the same single mode. Microsoft has replaced it with MODE.SNGL and MODE.MULT, and while it remains available for backward compatibility, it may not be available in future versions of Excel [1]. Use the newer functions in new workbooks.

**PivotTable counts.** Insert a PivotTable with your rating column in the Rows area and again in the Values area set to Count. Sort the counts from largest to smallest. The top row is your mode. This approach shows the full frequency distribution, which is useful when several values sit close together.

**COUNTIF for verification.** Build a small table of unique values and use `=COUNTIF(B2:B21,value)` next to each one. The value with the highest count is the mode. This is the most transparent method when you need to explain your result to someone else.

**The Analysis ToolPak.** Excel's Analysis ToolPak includes a Descriptive Statistics tool that reports the mode along with the mean, median, and standard deviation. It is a fast way to get several summary statistics at once. Related measures are covered in [How to Calculate the Mean in Excel](/blog/data-analysis/how-to-calculate-mean-in-excel), [How to Calculate Median in Excel](/blog/data-analysis/how-to-calculate-median-in-excel), and [How to Calculate Standard Deviation in Excel](/blog/data-analysis/how-to-calculate-standard-deviation-in-excel).

## Troubleshooting

**`#N/A` error.** MODE.SNGL returns `#N/A` when no value repeats in the range. Check whether every value is unique. If so, the dataset has no mode, and that is a valid result, not a broken formula.

**`#VALUE!` error.** This appears when an argument typed directly into the formula is text that cannot be interpreted as a number. Text inside a referenced range is ignored, so check any arguments you typed by hand.

**`#NAME?` error.** Excel does not recognize the function name. This happens in older versions that predate MODE.SNGL and MODE.MULT. Use the legacy `=MODE(range)` instead.

**MODE.MULT returns only one value.** In older Excel versions, MODE.MULT is an array formula. Select a vertical block of cells, type the formula, and confirm with Ctrl+Shift+Enter. In current versions it spills automatically.

**The result looks wrong.** MODE.SNGL returns the first tied value it encounters, which may not be the one you expected. Switch to MODE.MULT to see every tied value.

## Common Mistakes

- **Using MODE.SNGL when several values tie.** It returns only one of them, which hides the rest. Fix: use MODE.MULT and select enough cells to hold every tied value.
- **Including text or blank cells in the range.** Text is ignored and blanks are skipped, so a range that looks populated may hold fewer numbers than you think. Fix: confirm the range holds only the numeric values you intend to analyze.
- **Expecting a mode when every value is unique.** MODE.SNGL returns `#N/A`. Fix: accept that the dataset has no mode, or group continuous values into bins first.
- **Confusing mode with mean or median.** Mode is the most frequent value, not the average or the middle. Fix: compute all three when you need a full picture of the distribution.
- **Treating a mode on continuous data as meaningful.** With decimals, every value may be unique. Fix: round or bin the data before looking for a mode.
- **Relying on the legacy MODE function in new workbooks.** It may not be available in future versions of Excel [1]. Fix: use MODE.SNGL or MODE.MULT.

## Limitations

Mode is most useful for categorical and discrete data, such as ratings, counts, or survey responses on a fixed scale. On continuous data with many decimal places, every value is often unique, so the mode either does not exist or carries little meaning. Binning the data first, for example rounding to the nearest whole number, makes the mode interpretable again.

Mode also ignores the rest of the distribution. A dataset can have a clear mode while the mean and median sit far away, which usually signals skew or outliers. Report the mode alongside the mean and median so readers see the full shape of the data. If you need to compare spread or test a hypothesis, move on to [How to Calculate Variance in Excel](/blog/data-analysis/how-to-calculate-variance-in-excel) or [How to Calculate P Value in Excel](/blog/data-analysis/how-to-calculate-p-value-in-excel).

## Frequently Asked Questions

### What is the difference between MODE.SNGL and MODE.MULT?

MODE.SNGL returns one value, the single most frequently occurring number in the range. MODE.MULT returns every value tied for the highest frequency. If your data has one clear mode, both return the same number. If two or more values tie, MODE.SNGL picks one and MODE.MULT returns all of them.

### Why does my MODE formula return #N/A?

The `#N/A` error means no value repeats in the range you selected. Every number appears exactly once, so there is no mode. This is a correct result for unique data. If you expected repeats, check that the range covers the right cells and that no values were entered as text.

### Can I calculate the mode of text values in Excel?

Not with the MODE functions, which accept numbers only. For text, use `=INDEX(range,MODE(MATCH(range,range,0)))` entered as an array formula, or build a PivotTable with the text field in the Rows area and Count in the Values area. The PivotTable approach is simpler and easier to audit.

### How do I find multiple modes at once?

Use MODE.MULT. Select a vertical block of empty cells large enough to hold every tied value, enter `=MODE.MULT(B2:B21)`, and confirm. In older Excel versions press Ctrl+Shift+Enter. In current versions the result spills down automatically from the single cell where you typed the formula.

### Does the mode work on grouped or binned data?

Yes, and it often works better that way. Group continuous values into bins first, such as rounding to the nearest whole number or creating ranges like 0 to 10 and 11 to 20. Then run MODE.SNGL on the rounded values or on numeric bin codes, because MODE.SNGL ignores text labels. The result tells you which group is most common, which is usually the question you actually wanted answered.

## References

1. [MODE function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/mode-function)

## Further Reading

- [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)
- [Microsoft Support: Excel help and learning](https://support.microsoft.com/en-us/excel)
- [McCullough B, Heiser DA (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics & Data Analysis](https://doi.org/10.1016/j.csda.2008.03.004)
- [Abeysooriya M, Soria M, Kasu MS et al. (2021). Gene name errors: Lessons not learned. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1008984)

## Related Articles

- [How to Calculate the Mean in Excel (Step by Step)](/blog/data-analysis/how-to-calculate-mean-in-excel)
- [How to Calculate Variance in Excel (Step by Step)](/blog/data-analysis/how-to-calculate-variance-in-excel)
- [How to Calculate P Value in Excel (Step by Step)](/blog/data-analysis/how-to-calculate-p-value-in-excel)
- [How to Make a Formula in Excel (Step by Step)](/blog/data-analysis/how-to-make-formula-in-excel)
- [How to Calculate Age in Excel (Step by Step)](/blog/data-analysis/how-to-calculate-age-in-excel)