Mode Not Working in Excel? Causes, Checks and Fixes

If MODE is not working in Excel, the most common reason is that the selected range contains no repeated numeric values: MODE.SNGL returns #N/A in that case. Check that the range includes the intended numbers, that numbers are not stored as text, and that a tie is not hiding a second mode; use MODE.MULT when you need every tied mode.

What MODE calculates

The mode is the value that occurs most often in a data set. In current Excel for Microsoft 365, MODE.SNGL returns one mode, while MODE.MULT can return multiple values when they share the highest frequency. The older MODE function remains available for compatibility and behaves like MODE.SNGL.

For example, if A2:A10 contains 4, 2, 4, 7, 2, 4, 9, 2, 4, then 4 occurs five times and 2 occurs three times. With those values in A2:A10, enter =MODE.SNGL(A2:A10) in another cell; the result is 4. The mode is not necessarily the same as the average or the middle value, and a data set can have more than one mode—or no mode under Excel’s rule that a value must repeat.

Start with these quick checks

Before changing a formula, confirm that Excel is calculating the range you think it is. A reference that stops one row early, points to a different sheet, or includes a header instead of the last data point can change the result. Click the formula cell and inspect its reference in the formula bar, or select the range inside the formula to see which cells are highlighted.

Look for repeats. If every numeric value occurs once, MODE.SNGL correctly returns #N/A.
Check the reference. Make sure it includes the full data set and excludes labels or unrelated cells.
Check the values’ types. A value that looks like a number may actually be text, which a referenced range’s mode calculation can ignore.
Check for a tie. Two or more values may share the highest count. A single-mode function will not list all of them.
Check for source errors. An error value in the referenced data can cause the formula to return an error rather than a mode.

These checks often reveal that the function itself is working as designed. In particular, #N/A does not automatically mean the formula is misspelled or the workbook is damaged; it can simply mean there is no repeated numeric value.

Choose MODE.SNGL or MODE.MULT

Use MODE.SNGL when you want one result, such as the most common response in a survey or the most frequent item count. It returns one of the most frequently occurring values. If values tie for the top frequency, it does not display the full set of tied modes, so do not use it when reporting every top-frequency value matters.

Use MODE.MULT when the data may have multiple modes. For example, if B2:B7 contains 4, 4, 2, 2, 7, 9, both 2 and 4 occur twice, more than any other value. With the observations in B2:B7, enter =MODE.MULT(B2:B7). In Microsoft 365, Excel returns the tied modes in a vertical spill range below the formula cell, provided those cells are empty. If the spill area is blocked, clear the obstructing cells; Excel may show a spill error instead of displaying the results.

In older Excel versions without dynamic arrays, MODE.MULT may need to be entered as a legacy array formula: select the intended output range, type the formula, then press Ctrl+Shift+Enter on Windows. Current Excel for Microsoft 365 on Windows and Mac uses dynamic arrays, so enter the formula normally. The formula syntax is the same in Excel for the web.

Check numbers, text, blanks, and dates

When a range is supplied by reference, MODE functions work with numeric values. They ignore blank cells and text in the range, which is why a column that visually appears to contain numbers can produce an unexpected result. This often happens after importing a CSV, copying values from a website, or entering an apostrophe before a number. A cell aligned differently from neighboring numbers can be a clue, but formatting is not conclusive: inspect the value or test a small sample rather than relying on appearance.

If numeric-looking entries are text, convert them to numbers before calculating the mode. Depending on the source, you can select the affected cells and use Excel’s warning menu to convert text to numbers, or use Data > Text to Columns and complete the conversion without changing the delimiter settings. Check the result afterward, especially if the column contains identifiers such as postal codes that should remain text. Do not convert identifiers just to make a mode formula accept them.

Dates are stored as serial numbers in Excel, so repeated dates can have a mode. If a date-mode result appears as a number, the calculation may be correct but the result cell may be formatted as General. Apply a date number format to show it as a calendar date. Times also have numeric serial values, so values that look identical when displayed may differ in their underlying time precision.

Display rounding can create another surprise. Two values may both appear as 3.5 while the stored values are slightly different, so Excel counts them separately. If the analysis should group values to a chosen precision, round or otherwise standardize the source data deliberately before finding the mode. Be clear about that rule, because rounding changes the data being analyzed.

Understand ties and what counts as a repeat

A value must occur more often than every other numeric value to be the sole mode. If two values tie for the highest count, the data is multimodal. If every value occurs only once, there is no mode for Excel to return. For instance, 1, 2, 3 has no repeated value, while 1, 1, 2, 2, 3 has two tied modes. The latter is not a case where one of the values is objectively more frequent.

Excel compares the underlying numeric values, not their displayed labels. Thus, entries displayed as whole numbers may be different if their stored decimal portions differ. Similarly, two dates displayed as the same day can contain different times. Decide whether values should be grouped—for example, by rounding measurements or removing time components—before applying MODE. That decision depends on what the observations mean, not on a special MODE setting.

Diagnose errors and unexpected results

If the result is #N/A, first count the actual numeric observations and look for repeats among those numbers. A column with many entries can still have no mode if its entries are unique, blank, or text. If the formula returns another error, inspect the source range for errors and verify that the function arguments refer to valid cells. A copied formula can also shift its range; use absolute references with dollar signs when you need a range to stay fixed as you fill formulas down.

A helper count can show how often each value occurs. With observations in A2:A10, put this formula in B2 and fill it down alongside the data: =COUNTIF($A$2:$A$10,A2). For the example values 4, 2, 4, 7, 2, 4, 9, 2, 4, rows containing 4 show a count of 5 and rows containing 2 show a count of 3. If a value that looks numeric has a zero count or does not group with its apparent matches, inspect whether it is stored as text or has extra decimal precision.

If you prefer a readable message instead of #N/A when no mode exists, with the observations in A2:A10 use: =IFERROR(MODE.SNGL(A2:A10),"No repeated numeric values"). This handles errors broadly, however, so it can also hide a different problem in the source range. For troubleshooting, it is often better to inspect the original error first, then add an error-handling wrapper once you know which errors are expected.

Verify the result and consider alternatives

For a small data set, verify the returned value by counting it and comparing its frequency with the other candidates. A PivotTable can also summarize counts: put the field in Rows and again in Values, then review the count column. Sort the counts from largest to smallest to see ties and confirm whether the mode result represents the most common item. A PivotTable is useful for inspection, but it does not replace a mode calculation automatically.

Mode answers a different question from mean and median. The mean uses all numeric values and can be pulled by unusually large or small observations. The median is the middle value after sorting and is often useful for skewed data. The mode identifies the most common value, making it especially relevant for categories represented by numeric codes or for repeated choices. For text categories, MODE functions are not suitable because they do not calculate a most-common text label from a referenced range; use a count-based summary such as a PivotTable instead.

There is no need to purchase an add-in or change a general Excel setting to make MODE work. The key is choosing the function that matches the question, cleaning the inputs appropriately, and interpreting a tie or missing mode honestly. In current Microsoft 365, these functions are built into Excel and use the same formulas on Windows and Mac.

Frequently Asked Questions

Why does MODE.SNGL return #N/A?
It returns #N/A when the referenced numeric data has no repeated value. Check whether the values are unique, whether apparent numbers are stored as text, and whether the formula includes the intended cells.

What is the difference between MODE and MODE.SNGL?
MODE is the older compatibility function and is equivalent to MODE.SNGL. Both return one mode; use MODE.MULT if you need Excel to return multiple tied modes.

Why is MODE ignoring numbers in my column?
Those entries may be text that only looks like numbers, or the formula may not include their cells. Convert genuine numeric measurements to numeric values, then check the formula range. Keep codes and identifiers as text when their meaning depends on preserving their format.

Can Excel return more than one mode?
Yes. Use MODE.MULT. In Microsoft 365, its results spill vertically into nearby cells; make sure the cells below the formula are clear. The function returns the values tied for the greatest frequency.

Why does the mode show as a serial number instead of a date?
Excel stores dates as numbers. If the result is a date serial, apply an appropriate date format to the result cell. Also check whether the source dates include times that make their underlying values different.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

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

Spotted an error in this guide? Let us know.