totn Excel Functions

MS Excel: How to use the MODE Function (WS)

This Excel tutorial explains how to use the Excel MODE function with syntax and examples.

Description

The Microsoft Excel MODE function returns the most frequently occurring number found in a set of numbers. This function has been replaced by the MODE.SNGL and MODE.MULT functions starting in Excel 2010.

The MODE function is a built-in function in Excel that is categorized as a Statistical Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the MODE function can be entered as part of a formula in a cell of a worksheet.

Microsoft Excel

If you want to follow along with this tutorial, download the example spreadsheet.

Download Example

Syntax

The syntax for the MODE function in Microsoft Excel is:

MODE( number1, [number2, ... number_n] )

Parameters or Arguments

number1, number2, ... number_n
Each number can be a range, a cell or a numeric value. There can be up to 255 numbers.

Returns

The MODE function returns a numeric value.
If all numeric values in the set are unique, the MODE function returns the #N/A error.
If a text value such as "A" is entered as a parameter, the MODE function will return the #VALUE! error.
If a range or cell reference contains non-numeric values such as text values, logical values or empty cells, these values will be ignored.

Note

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003

Type of Function

  • Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel MODE function examples and explore how to use the MODE function as a worksheet function in Microsoft Excel:

Microsoft Excel

Based on the Excel spreadsheet above, the following MODE examples would return:

=MODE(B2:H2)
Result: 85

=MODE(B2,C2,D2,E2,F2,G2,H2)
Result: 85

=MODE(78,85,90,72,85,63,97)
Result: 85

=MODE(B3:H3)
Result: 75

=MODE(B4:H4)
Result: #N/A     'No repeating number is found

=MODE(78,85,90,72,85,63,"A")
Result: #VALUE!  'Text literal "A" has been entered as a parameter

Combining Range and Value Arguments

The MODE function can combine parameters that are both range arguments such as B2:H2 as well as value arguments such as the number 97 or the number 65.

For example, let's assume that the spreadsheet was missing three grades for Math - the values 97, 65 and 97.

We could modify the formula to include these missing grades as follows:

=MODE(B2:H2,97,65,97)
Result: 97

This formula would test for the most frequently occurring number in the range B2:H2 in addition to the values 97, 65 and 97. In this example, the formula would return 97 since the number 97 appears three times in the set of numbers provided while 85 only appears twice.

Share on: