How to Use MODE Function in Excel (4 Examples)

The MODE function, an Excel Statistical function returns the most frequently occurring number from an array or set of numbers. MODE.SNGL function along with MODE.MULT function has replaced the MODE function since Excel 2010. Though we can still find the MODE function in the updated version of Excel. This function is still available for compatibility with Excel 2007 or earlier versions.

Excel mode function

The above image gives a general overview of the MODE function. Throughout the article, we will learn more details about the function. We will also see different usages of this function.


📂 Download Practice Workbook


Introduction to the MODE Function

❑ Objective

Excel MODE function returns the most frequently occurring, or repetitive, value in an array or a range of data.

❑ Syntax

MODE (number1, [number2], …)

syntax

❑ Argument Explanation

Argument Required/Optional Explanation
number1 Required First number or cell references  that refer to numeric values for mode calculation.
number2 Optional Number arguments or cell references 2 to 255 to calculate mode.

❑ Output

Returns the mode of the numeric values. If there is no mode of the dataset; the input values are texts or logicals the function will return #N/A! Error.

❑ Version

This function is available in all the versions of Excel. In Office 365, this function is available for compatibility with Excel 2007 or earlier versions.


4 Examples of Using the MODE Function in Excel

Now let’s see some examples of how we can use this function for different kinds of data.

1. MODE Function for Unimodal Data

When a set of numbers has one number which appears most of the time, it is called unimodal. Suppose we have a dataset where the number of students in different subjects are given. The dataset is unimodal.

dataset

Now, we will use Excel MODE function to find the mode of the numbers.

➤ Type the following formula in an empty cell (E7),

=MODE(C5:C13)

The formula will return the mode of the numbers of the cell range C5:C13 in cell E7.

Excel mode function

➤ Press ENTER

As a result, you will get the mode of the numbers from the cell C5 to C13.

result

Instead of using the cell range, we can directly input the cells in the formula.

cell reference

We can also use the MODE.SGNL function. It will give the same result.

MODE.SNGL


2. Find Modes of Multimodal Data Using MODE Function

When a set of numbers has more than one number which appears most of the time, it is called multimodal. Consider the following dataset with a multimodal number set.

MULTIMODAL

Now, let’s see what will happen if we use the MODE function to find out the mode of this number set.

Excel mode function

➤ Type the following formula in an empty cell (E7),

=MODE(C5:C13)

The formula will return the mode of the numbers of the cell range C5:C13 in cell E7.

After that,

➤ press ENTER

As a result you will see only one of the modes of the number set has been returned. The MODE function only returns the smallest mode from the multimodal dataset.

Excel mode function

To find out all the modes we can use the MODE.MULT function.

➤ Type the following formula in an empty cell (E7),

=MODE.MULT(C5:C13)

The formula will return the modes of the numbers of the cell range C5:C13.

MODE.MULT

Now,

➤ Press ENTER

As a result, you will get all the modes of that multimodal dataset.

MODE.MULT


Similar Readings


3. MODE Function to Find Most Frequently Occurring Word

Excel MODE function only takes numbers as arguments. So, we can’t find the most appearing text by using only this function directly. But If we use the INDEX function and the MATCH function with the MODE function we can find the most appearing text.

Suppose we have the following dataset and we want to find the subject taken by the maximum number of the students.

DATASET

Now,

➤ Type the following formula in an empty cell (E7),

=INDEX(C5:C13,MODE(MATCH(C5:C13,C5:C13,0)))

Here, the MATCH function will search for the items of  C5:C13 in the search range C5:C13 and will return numbers indicating the relative positions of every unique item. The MODE function will then return the number which appears most. At last, the INDEX function will return the value which cell’s position has been returned by the MODE function.

INDEX, MATCH, MODE

➤ Press ENTER.

As a result, you will get the most occurring word in cell E7.

Excel mode function


4. MODE Function in VBA

We can also use the MODE function in a Microsoft Visual Basic Applications (VBA) code. Consider the dataset of the examples 1. Now, we will find out the mode of the dataset by using the MODE function in VBA.

First,

➤ Press ALT+F11

It will open the VBA window.

After that,

➤ Press CTRL+G

It will open the Immediate window of VBA.

Now,

➤ Type the following code in that window,

Range("E7") = Application.WorksheetFunction.Mode(Range("C5:C13"))

The code will return the mode of the numbers from cell range C5:C13 in cell E7.

VBA CODE

At last,

➤ Press ENTER and close the VBA window.

As a result, you will see the mode of the numbers of the selected cells has been returned in cell E7.

EXCEL MODE FUNCTION


💡 Things to Remember

📌Arguments of the MODE function can be numbers, arrays, names and references but can’t be texts or logical values.

📌If the input numbers don’t contain any duplicate numbers the function will show #N/A! Error.

📌 the MODE.SGNL and the MODE.MULT functions which are available from Excel 2010 is the superior alternative of the MODE function. It is better to use those functions if you have an Excel version older than Excel 2007.


Conclusion

I hope now you have a detailed understanding of Excel MODE function after going through this article. If you have any kind of confusion, please feel free to leave a comment.


Related Articles

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo