MODE in Excel (Formula, Example) | How to Use MODE Function in Excel?

Excel Functions Tutorials

MODE in Excel (Table of Contents)

MODE in Excel
MODE Formula Excel
How to Use MODE Function in Excel?

MODE Function in Excel

MODE function in Excel is categorized as statistical functions in Excel. In mathematical term, the MODE in Excel returns the MODE for a given data sets. Mode in Excel returns the most often occurring, or repetitive, the value in an array or range of data.

MODE Formula in Excel

Parameters of the MODE in Excel

MODE formula in Excel has the following parameters. i.e. number1 and [number2].

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.
[number2]: it is an array of optional parameters

How to Use MODE Function in Excel?

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

MODE is excel as a worksheet function.

MODE in Excel Example #1

Let’s consider a given set of numbers and we have to calculate the MODE formula in excel of them then apply the MODE in Excel on the given values as shown in the below table.

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

MODE in Excel Example #2

Let consider a given set of values here there are two logical Values in the given data set. Now apply the MODE Formula in Excel here. The MODE Function in Excel ignores the logical value and returns the output.

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

MODE in Excel Example #3

In MODE Formula in Excel example, we have a data set with non-numeric values, empty value and zero in the data, MODE function simple ignore the non-numeric value empty value but consider the zero as value and provide the most repetitive value from the given set of data as shown in the below table.

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

MODE in Excel Example #4

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

MODE Function can be Used as a VBA Function

Suppose we have the data sets located in the excel sheet range from A1 to A10, then we can calculate the MODE formula in excel 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 interger

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

Arguments can either be numbers or names, arrays, or references that contain numbers.
If an array or reference argument contains text, logical values, or empty cells, the values are ignored by this function. However, any cells with the value zero are included.
Arguments that are error values or text that cannot be translated into numbers cause errors.
The MODE function through #NUM! Error if there are no duplicates/repetitive values in the supplied data sets hence, there is no mode within the supplied values.

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

The MODE function through #VALUE! Error when a value that is supplied directly to the MODE is non-numeric and also is not part of an array. Non-numeric functions that are in the array of values are ignored by the MODE excel function.

=MODE(C28:C37,”hdd”)

You can download this MODE Function in Excel template here – MODE Function Excel Template

MODE Excel Function