Summary

The Excel MODE function returns the most frequently occurring number in a numeric data set. For example, =MODE(1,2,4,4,5,5,5,6) returns 5.

Purpose 

Get most frequently occurring number

Return value 

A number representing the mode.

Syntax

=MODE(number1,[number2],...)
  • number1 - A number or cell reference that refers to numeric values.
  • number2 - [optional] A number or cell reference that refers to numeric values.

How to use 

The MODE function returns the most frequently occurring number in a set of numeric data. If supplied data does not contain any duplicate numbers, the MODE function returns a #N/A error.

The MODE function takes multiple arguments in the form number1, number2, number3, etc. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. MODE ignores empty cells, text values, and the logical values TRUE and FALSE. The MODE function will accept up to 255 separate arguments. 

Examples

MODE returns the most frequently occurring number in supplied data. For example,

=MODE(1,2,4,4,5,5,5,6) // returns 5
=MODE(7,8,9,7,9) // returns 7

If there are no duplicate numbers, the MODE function returns the #N/A error:

=MODE(7,9,6,5,3,1,0) // returns #N/A

In the example shown, the formula in L5, copied down, is:

=MODE(B5:J5)

Note: the MODE function is now classified as a "compatibility function". Microsoft recommends that MODE.SNGL or MODE.MULT be used instead.

Notes

  • If supplied data does not contain duplicate numbers, MODE returns #N/A
  • MODE ignores empty cells, the logical values TRUE and FALSE, and text.
  • Arguments can be numbers, names, arrays, or references.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.