# MODE Excel Function

Last Updated :

21 Aug, 2024

Blog Author :

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

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

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

#### 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")

### Recommended Articles

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 Function
- Excel VBA "MsgBox"
- Print in Excel
- Remove Duplicates in Excel
- Change Chart Style In Excel