The MODE function in Excel makes it easy to find the value that occurs most often in a set of selected data. Learn how to use the MODE function, in addition to its new function replacements, MODE.SNGL and MODE.MULT.
The instructions in this article apply to Office 2019, 2016, 2013, 2010; Excel for Microsoft 365, Excel Online, and Excel for Mac.
Different MODE Functions
Starting with Excel 2010, Microsoft introduced two alternatives to the all-purpose MODE function. Although MODE is still available in Excel for backward compatibility, consider using these new functions because MODE may not be available in future versions of Excel.
These new MODE functions are:
- MODE.SNGL: Used for data with a single mode; very similar to the MODE function covered in this article.
- MODE.MULT: Used if the data is likely to have multiple modes — two or more values that occur with the same frequency.
To use the regular MODE function in Excel 2010 and later versions, it must be entered manually, as there is no dialog box associated with it in these versions of the program.
MODE Function Syntax and Arguments
Here are examples of the different ways that the MODE function is used:
The syntax for the MODE function is:
Number1 (required): The values used to calculate the mode; this argument can contain the following:
- Actual numbers separated by commas. See the example in row 3 in the image above.
- A single range containing cell references to the location of the data in the worksheet. See the examples in rows 2, 5,6, and 7.
- A combination of cell references and data. See the example in row 4.
- A named range.
- Individual cell references separated by commas.
Number2,Number3,...,Number255 (optional): Additional values or cell references up to a maximum of 255 used to calculate the mode.
Factors to know before using the MODE function:
- If the selected data range contains no duplicate data, the MODE function shows a result of #N/A. See row 7 in the image above.
- If multiple values in the selected data occur with the same frequency (in other words, the data contains multiple modes) the function returns the first such mode it encounters as the mode for the entire dataset. See the example in row 5 in the image above.
- The function ignores text strings, boolean values, and empty cells.
Find the MODE in Excel
Two options exist for entering the MODE function's arguments within Excel. You can either type in the data or cell references, or use point and click to select the cell references in the worksheet.
The advantage of point and click, which involves using the mouse to highlight the cells of data, is that it reduces the possibilities of errors caused by typing mistakes.
To follow along with this tutorial, copy the data in cells A2 to D2 as shown in the image below:
To find the mode for this range, use the MODE function like this:
To enter the MODE function into cell E2 using point and click:
Select cell E2 make it the active cell.
Place the cursor in the Formula Bar and type the function:
Highlight cells A2 to D2 in the worksheet to enter this range as the function arguments.
Type a closing round bracket or parenthesis to enclose the function's argument.
Press Enter to complete the function.
The answer 3 appears in cell E2 since this number appears the most (it appears in two of the four cells) in the list of data.
Select cell E2 to display the complete function in the Formula Bar.