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. However, 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.
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.
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], …)
❑ 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 versions of Excel. In Office 365, this function is available for compatibility with Excel 2007 or earlier versions.
Using the MODE Function in Excel: 4 Examples
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 is given. The dataset is unimodal.
Now, we will use the 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.
➤ Press ENTER
As a result, you will get the mode of the numbers from the cell C5 to C13.
Instead of using the cell range, we can directly input the cells in the formula.
We can also use the MODE.SGNL function. It will give the same result.
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.
Now, let’s see what will happen if we use the MODE function to find out the mode of this number set.
➤ 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.
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.
Now,
➤ Press ENTER
As a result, you will get all the modes of that multimodal dataset.
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 INDEX and MATCH functions with the MODE function, we can find the most appearing text.
Suppose we have the following dataset and want to find the subject taken by the maximum number of students.
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 that appears most. At last, the INDEX function will return the value of which cell’s position has been returned by the MODE function.
➤ Press ENTER.
As a result, you will get the most occurring word in cell E7.
4. MODE Function in VBA
We can also use the MODE function in a Microsoft Visual Basic Applications (VBA) code. Consider the dataset of 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.
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.
💡 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 are the superior alternative to the MODE function. It is better to use those functions if you have an Excel version older than Excel 2007.
📂 Download Practice Workbook
Conclusion
I hope now you have a detailed understanding of the Excel MODE function after going through this article. If you have any kind of confusion, please feel free to leave a comment.
<< Go Back to Excel Functions | Learn Excel
