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. 

MODE Formula in Excel

Parameters

The MODE formula has the following parameters: 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?

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 functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more.

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.

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 #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 #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.

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.

  • 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”)

MODE Excel Function Video

This article is a guide to MODE in Excel. We discuss the MODE formula in Excel and how to use the MODE function, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel: –

  • VBA Worksheet FunctionVBA Worksheet FunctionThe worksheet function in VBA is used when we need to refer to a specific worksheet. When we create a module, the code runs in the currently active sheet of the workbook, but we can use the worksheet function to run the code in a particular worksheet.read moreExcel VBA “MsgBox”VBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read morePrint in ExcelPrint In ExcelThe print feature in excel is used to print a sheet or any data. While we can print the entire worksheet at once, we also have the option of printing only a portion of it or a specific table.read moreRemove Duplicates in ExcelRemove Duplicates In ExcelTo remove duplicates from the excel column, the user can adopt any of the three well-known methods: Using data tools group, Using the advanced filter in excel, Conditional formatting in excel.read moreChange Chart Style In ExcelChange Chart Style In ExcelWe can change the structure of the chart by using chart styles. You can reuse a chart style you’ve created by saving it as a chart template.read more