MODE Function in Excel – A Statistical Function

Mode is a handy parameter in statistical analysis. It is the term that occurs most frequently in a set of values. MODE Function helps us achieve this objective for the small as well as for the expanded range of values.

The MODE Function is comapatible with Excel 2007 and the earlier versions. However, you can still use it in later versions.

So let us begin with the topic.

When to use MODE Formula in Excel?

The MODE Function in excel takes into account, several values and then returns us the most frequently occurring value out of the list. In other words, we can say that the MODE Function returns the Mode of the given set of values.

The MODE function works only for the numerical set of values. It does not work with text strings. Operations like finding the most frequently worn outfit color are not possible since colors i.e Black, White, or Blue are text strings.

The MODE Function can be categorized under the statistical functions of excel.

Syntax and Arguments


Where the following points will explain to you the function arguments we need to pass to the MODE function in excel:-

  • number1It inputs the list of values from which we want to find the most recurring value. This can be a cell reference, range of cells, named range, or a directly supplied numerical value.
  • [number2] – This is an optional argument and is not mandatory to specify. It asks for the same parameters mentioned in the number1 argument.

There can be a total of 255 such arguments that can be passed into the function out of which only the first one is mandatory to specify.

Important Points to Remember about MODE Formula

The following points about the MODE Function must be kept in mind before the actual implementation of the MODE Formula.

  • If the list of values does not contain any duplicate numerical value, then the MODE formula returns a #N/A error.
  • The function ignores the Text strings and Logical Values ( TRUE, FALSE ) and nonnumerical values.
  • The function returns a #N/A error when there is no single numerical value passed as a function argument.
  • The function has become old and is not compatible with Excel versions after Excel 2007. However, you can still use it in later versions successfully.
  • The function ignores blank cells.
  • If two values are occurring an equal number of times, then the value appearing first on the list is the Mode value. =MODE(10,12,12,10) returns 10.
infographics mode function in excel

Examples to Learn MODE Function in Excel

In this part of the blog, we would perform some of the practical examples required to learn about the MODE Function.

Let us suppose we have the following range of data:-

example of mode function in excel raw data

We want to find the mode of data in each range or data contained in each row (horizontal-wise).

Use the following formula in cell G2 and copy it down the range G2:G7.

result of using the MODE Function in excel

As a result, the MODE function returns mode for the four ranges while the last two ranges B6:F6 and B7:F7 return #N/A error.

Explanation: We have passed the corresponding ranges as the number argument of the MODE Function. The Explanations for the result of each range are as follows:-

  • Range 1 (B2:F2) – When this range passes as the function argument for MODE Function in cell G2, the function returns the most frequently occurring value. 1 occurs three times ( B2, D2, and E2 ).
  • Range 2 (B3:F3) – This range contains the text string “Excel” in cell F3, and the function simply ignores it. The function returns 2 as the mode of numerical values in the range.
  • Range 3 (B4:F4) – The range has a logical value TRUE in cell F4 and the MODE function ignores it. The MODE Function returns the mode value as 3 of the numerical values in the range.
  • Range 4 (B5:F5) – There is a blank cell D5 that the function ignores
  • Range 5 (B6:F6) – The range does not contain any numerical value so the function returns #N/A error
  • Range 6 (B7:F7) – There is no value that occurs frequently, more than once. Due to zero duplicacy in the range, there is no mode value so the function gives the #N/A error.

With this, we have come to the end of the blog.

Thank you for reading.

Leave a Comment