MODE Excel Function

Last Updated :

-

Blog Author :

Tanuj Kumar

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

MODE Function in Excel

The MODE function is categorized as statistical function in Excel. In mathematical terms, the MODE returns the MODE for a given data set. For example, the MODE in Excel returns the most often occurring or repetitive value in an array or range of data.

For example, =MODE(1,2,3,4,5,6,6,7) returns 6. 

Table of contents

MODE Formula in Excel

MODE Formula in Excel

Parameters

The MODE formula has the following parameters: number1 and .

  • number1: It is an array of the number parameters or a set of one or more numeric values for which we want to calculate the mode.
  • : It is an array of optional parameters.

How to Use MODE Function in Excel?

The MODE function in Excel is very simple and easy to use. Let us understand the working of the MODE function by some examples. The MODE formula can be used as a worksheet function and VBA function.

MODE Excel Function Video

Example #1

Let us consider a given set of numbers. First, we have to calculate the MODE formula in Excel, then apply the MODE formula to the given values, as shown in the table below.

=MODE(A3:A22)  output will be 15.

MODE Function in Excel - Example -1

Example #2

Let us consider a given set of values here. The two logical values exist in the provided data set. Now, apply the MODE formula here. The MODE function ignores the logical value and returns the output.

=MODE(C3:C22) and output will be 15.

Example -2

Example #3

In the MODE formula example, we have a data set with non-numeric values, empty values, and zero in the data. Therefore, the MODE function ignores the empty non-numeric value, considers zero as the value, and provides the most repetitive value from the given data set, as shown in the table below.

=MODE(E3:E22) and output will be 15.

Example -3

Example #4

In the MODE formula example, we have a given data set in the horizontal table, and we apply the MODE function to get the most repetitive values from the given data set.

Example -4

MODE Function can be used as a VBA Function

Suppose we have the data sets located in the Excel sheet ranging from A1 to A10. Then, we can calculate the MODE formula with the given data sets by using the below VBA functions:

Sub MODEcal()  // start the MODE function scope

Dim x as Range //declare the range x and y

Dim MODE as integer

set x = Range("A1:A10") //set  data sets to Range x.

MODE = Application.worksheetfunction.mode(x)

MsgBox MODE // print the MODE value in message box.

End sub // End the MODE function

Things to Know about the MODE Function in Excel

  • The arguments can be numbers or names, arrays, or references containing numbers.
  • This function ignores the values if an array or reference argument contains text, logical values, or empty cells. However, any cells with the value zero are included.
  • The arguments that are error values or text we cannot translate into numbers cause errors.
  • The MODE function gives #NUM! Error if no duplicates/repetitive values exist in the supplied data sets. Hence, there is no mode within the provided values.

=MODE(A28:A37) as there are no duplicate values in the supplied data set MODE function through #N/A error.

Example -5
  • The MODE function provides #VALUE! Error, when a value supplied directly to the MODE is non-numeric and not part of an array. The MODE function ignores non-numeric functions in the array of values.

=MODE(C28:C37,"hdd")

Example -6