How to Find the Mode in Excel: Formulas and Examples

To find the most frequently occurring number in Excel, enter =MODE.SNGL(A2:A11) in an empty cell, replacing A2:A11 with your data range. In Microsoft 365, use =MODE.MULT(A2:A11) to return every tied mode. The examples below use Excel for Microsoft 365 on Windows and explain what to do when your data contains text, ties, or no repeated values.

What You’ll Need

An Excel workbook with the values you want to analyze.
A clear range of numeric data, such as measurements or test scores. The MODE functions do not calculate the mode of text categories.
An empty cell near your data for the result. If you use MODE.MULT, leave enough empty cells below it for the results to spill.

Step-by-Step

Arrange and inspect your data. Put the numbers you want to analyze in one column or row, with no heading included in the range you plan to use. For example, enter 8, 12, 12, 15, 18, 12, 15, 20, 15, and 22 in cells A2:A11. In this set, 12 appears three times and 15 appears three times; all the other values appear once. The data therefore has two modes. You can use a range that is not adjacent to the result cell, but keep the range limited to the observations you mean to analyze.
Choose an empty result cell. Click a blank cell, such as C2. Do not put the formula in the middle of the source data, where it could overwrite a value or make the range confusing. If you want to label your result, type Mode in B2 and put the formula in C2.
Enter the formula for one mode. With the example values in A2:A11 and the result cell C2 selected, enter =MODE.SNGL(A2:A11) and press Enter. Excel returns 12. The function returns one most-frequent number; if several numbers tie for the highest frequency, MODE.SNGL returns the lowest tied number. In the example, both 12 and 15 occur three times, so the single result is 12.
Return all tied modes if you need them. To show every value tied for the highest frequency, select an empty cell with clear cells underneath it. With the example data in A2:A11, enter =MODE.MULT(A2:A11) and press Enter. In Microsoft 365, the results spill into the cells below; the example returns 12 and 15. Do not type into the spill area, or Excel may show a #SPILL! error. The number of results depends on how many modes the data has.
Check how often the candidate values occur. A count helps confirm the answer and makes ties easier to explain. For example, with the numbers in A2:A11 and the candidate modes 12 and 15 entered in D2 and D3, put this in E2: =COUNTIF($A$2:$A$11,D2). It returns 3. Copy E2 down to E3; the relative reference changes to D3, and that formula also returns 3. The dollar signs keep the source range fixed while you copy the formula.
Use MODE.SNGL for a single answer or MODE.MULT for all answers. Decide which result suits your task before sharing or building further calculations. A report that allows just one number can use MODE.SNGL, but state that it selects one tied value. If every equally common result matters, use MODE.MULT and label the output as multiple modes. MODE is the older function name and is retained for compatibility; MODE.SNGL makes it clearer that the formula returns one result.
Handle text categories with a PivotTable. MODE.SNGL and MODE.MULT work on numbers, not labels such as Red, Blue, or Green. To find the most common category, select a clean column that includes its heading, then choose Insert > PivotTable. Confirm the Table/Range and choose New Worksheet, then select OK. In the PivotTable Fields pane, drag the category field to Rows and drag the same field to Values. Excel should display Count of the category. The category with the largest count is the mode. If needed, right-click a count in the PivotTable and choose Sort > Sort Largest to Smallest. Check that blank categories are not being mistaken for meaningful results.
Review the result before using it. Confirm that the formula covers the intended rows, that numeric values are actually numbers, and that your chosen function fits the question. A mode describes the most frequent value; it does not describe the average. If you have filtered a list, remember that MODE.SNGL and MODE.MULT still analyze the referenced cells, including rows hidden by a filter. For visible rows only, use a method designed to account for the filter or copy the visible values to a separate range first.

Common Mistakes

Including the heading in the range: Text in a referenced range is ignored by the MODE functions, but including headings makes a formula harder to read and can cause confusion later. Start the range at the first observation, not the column label.

Expecting MODE.SNGL to list every tie: It returns one value, even when there is more than one mode. Use MODE.MULT when the full set of tied numeric modes is needed. If a report uses the single result, make clear that another value may be equally frequent.

Trying to find a text mode with MODE.SNGL: The function is for numbers. A list of product names, colors, or survey choices needs a count-based approach, such as a PivotTable, rather than a numeric MODE formula.

Assuming blanks and zeros mean the same thing: Empty cells are ignored, but a cell containing 0 is a numeric observation and is counted. If zero means missing data in your workbook, remove or exclude those entries before calculating the mode.

Using too broad a range: A reference such as A:A may include old values or other data farther down the column. Select the actual data rows so unrelated entries do not affect the result.

Tips

Turn a well-structured data range into an Excel table with Insert > Table. A formula that refers to a table column can expand as you add new records, reducing the chance that new values are left out.
Use MODE.MULT when ties are possible, especially for survey counts or repeated measurements. In Microsoft 365, its results spill automatically; reserve the cells below the formula for that output.
Use COUNTIF to audit a result when the answer matters. Comparing the counts of candidate values helps show whether there is a tie or whether the selected range needs correcting.
Keep units consistent. A list mixing values recorded in different units, such as centimeters and inches, may produce a technically valid but misleading mode.
Excel for Mac has the same MODE.SNGL and MODE.MULT functions, and the relevant commands are generally in the same ribbon locations. Excel for the web also supports these functions. If your regional settings use semicolons as formula separators, Excel may require semicolons instead of commas when you type formulas.

Troubleshooting

Excel returns #N/A: The selected range may not contain any value that occurs more than once. A mode is not defined when every numeric value appears exactly once, so #N/A is expected in that case. Check the range for repeated values or use a different statistic if the data has no mode. Also make sure the range contains numbers rather than numbers stored as text.

Excel returns an unexpected number: Check whether the range includes extra rows, zeros, or values that should not be part of the analysis. If there is a tie, MODE.SNGL returns one of the modes, specifically the lowest tied value; use MODE.MULT to see all tied results. You can also use COUNTIF to verify the frequency of the returned value.

MODE.MULT shows #SPILL!: One or more cells in the cells where Excel needs to display the results are occupied, merged, or otherwise blocking the output. Clear the cells below the formula, unmerge any cells in the spill area, and try again. The formula itself belongs in only the first result cell in Microsoft 365; do not copy it down to make the results appear.

The formula appears as text instead of calculating: The cell may be formatted as Text, or the formula may have an apostrophe or space before the equals sign. Change the cell format to General, edit the cell, and press Enter. Confirm that the first character is an equals sign. If Excel reports a formula syntax error, check that the parentheses are balanced and use the list separator expected by your regional settings.

MODE.MULT does not spill in an older Excel version: Dynamic arrays are available in Microsoft 365 and newer Excel releases, but older versions may require entering MODE.MULT as a legacy array formula. Follow the instructions for your Excel version; on Windows, that can mean selecting the intended output cells first and confirming with Ctrl+Shift+Enter. The simpler MODE.SNGL formula returns just one value and does not require a spill range.

Frequently Asked Questions

What does mode mean in Excel?
The mode is the value that occurs most often in a data set. A set can have one mode, multiple modes tied for the highest frequency, or no mode if every value occurs equally often without a repeat.

What is the difference between MODE.SNGL and MODE.MULT?
MODE.SNGL returns one most-frequent number. MODE.MULT returns all numeric values tied for the greatest frequency. In Microsoft 365, MODE.MULT displays those results in a spill range.

Why does MODE.SNGL return the lower value in a tie?
When two or more numbers have the same highest frequency, MODE.SNGL returns the lowest of the tied values. Use MODE.MULT if you need to see every tied value rather than one representative result.

Can I find the mode of words or categories?
Not with MODE.SNGL or MODE.MULT, which calculate modes for numbers. Use a PivotTable to count each category and identify the one with the largest count.

What does #N/A mean in a mode formula?
It commonly means no numeric value repeats in the referenced range, so Excel cannot identify a most-frequent value. Check that your range is right and that numeric entries are stored as numbers.

Do blank cells or zeros affect the mode?
Blank cells in a referenced range are ignored, while numeric zeros are counted as values. Decide whether zero is a real observation before including it in the calculation.

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.