MODE Function

What is the MODE Function?

The MODE function in Excel returns the value that makes the maximum number of appearances in a given data set.

We all have fond childhood memories of calculating mean, median, and mode., Math was one of my favorite subjects, and I had great fun determining the different values returned for each statistical concept using their formulas.

When we were asked to calculate the mode for a number in school, we sometimes created a table to mark their occurrences in our dataset. However, that was when we were kids.

Today, we won't be dealing with a few random numbers but hundreds and thousands of rows or even multiple spreadsheets where we might be expected to return the number that appears most often in the dataset.

This is where the MODE function comes in. In this article, we will see what the function is along with its syntax, how to use the function, as well as a couple of examples.

Understanding the MODE function

The function is categorized as a Statistical/Compatibility function that returns the value with the maximum number of occurrences in a given data set.

If you access the function from the library, you will find it under the compatibility section since the function has been replaced by upgraded versions offering more flexibility to users.

Still, Excel supports the use of the MODE function in the newer versions for backward compatibility so that if anyone still uses older versions of Excel, the formula would still be supported without giving an error. 

The function was introduced in the Excel 2003 version. However, it was replaced by the MODE.SNGL and MODE.MULT, which performs unique operations on a set of numbers, respectively.

The newer versions of the function were introduced in Excel 2010, and the former function was kept as a compatibility function.

Suppose that you have three numbers 4, 4, and 8. If you use the function to determine the number with maximum occurrences, it will return the result as 4.

The number 4 appears twice, while the number 8 appears only once. Hence, the mode for the dataset is equal to 4.

MODE Function Formula

The syntax for the function is:

=MODE(number1,[number2]...)

where,

• number1 - (required) a number or reference to a cell containing a numerical value or range of cells containing numbers
• number2 - (optional) a number or reference to a cell containing a numerical value or range of cells containing numbers

You can input up to 255 optional arguments in the function. This could be a reference to a single cell, a range of cells, or even an entire spreadsheet range as one argument.

How to use the MODE Function in Excel?

We will not go deep into this section, but it will give you an overview of how to use the function. You can either select the function directly from the library or use it as a worksheet formula.

To select the function from the library, click on Formulas > More Functions > Compatibility and select the MODE function from the drop-down.

This will open up the dialog box where we will input the numbers directly or the references to the range of cells containing the numerical values. Let’s say we input the values as illustrated below:

As you might have already observed in the highlighted sections, we get the result as 4, which means that the number 4 was repeated the maximum number of times in our dataset of 4, 4, 8, 2, and 4.

Suppose you have the same data in Excel as illustrated below:

Here, we will use the function as a worksheet formula as =MODE(C2:C6) in cell C8, which gives us the same result 4.

There is nothing wrong with either method. It's just a matter of preference that might differ between different individuals. However, the pros of the worksheet formula method far outweigh its cons as it allows the user to use multiple functions together.

MODE Function Example

The function doesn’t have a lot of practical applications apart from returning a value with a maximum number of appearances.

Would the value be the ‘numbers,’ or maybe you can manipulate the formula to get ‘text strings’ with maximum occurrences?

Let’s see!

Example 1

All of us know how degen option traders make money. Take several similar trades to make a few dollars and, in the process, lose hundreds more because revenge is sweet.

Suppose that a day trade takes several trades in an option contract as illustrated below:

We need to find at what price the maximum number of times the contract was purchased before it was sold back in the market.

Here, we will use the formula =MODE(E3:E12) in cell G5, which gives us the result of $31.34.

A pure stroke of luck, but as per the data, the maximum number of times the call options were purchased for $31.34.

What if we have multiple prices with the same number of occurrences in our dataset? For example, suppose we have two prices, $31.34 and $31.20, with three occurrences each. What would be the expected result?

In this case, whatever number appears first in our referenced range will be the value for the mode in our dataset.

The formula will be =MODE(E3:E12) in cell H5, which gives the result of $31.34. As the value is present in the first cell, i.e., E3 in our referenced range, we get the same effect when we have a matching number of appearances in the dataset.

If the price in cell E3 is equal to $31.20, then the mode as well will be equal to $31.20, as illustrated below:

This way, you can use the function to find the value that occurs the maximum number of times in the data.

Example 2

You would probably question - can we use the function with text strings?

The answer is yes and no. If you directly reference text strings inside the function, you will get the #VALUE! Error. However, there is an alternative that can be used to identify the text strings that ‘match’ the maximum number of times.

Suppose that you have the stock trading data as illustrated below:

Here, we will try to determine what stock was traded the maximum number of times. 

As we had foreshadowed in the third line of this example, we will use the MATCH along with MODE and INDEX functions.

The formula in cell H4 will be =INDEX(D3:D12, MODE(MATCH(D3:D12, D3:D12,0))), which gives us the result as Tesla Inc as it is repeated four times.

Thus, using the formula, you can easily find the text string that appears most in your dataset.

MODE vs. MODE.SNGL Function

As we previously said, the MODE function was replaced by two newer versions in 2010 that offered users more flexibility and directness in regards to the result that they expect from Excel. One of those functions is the MODE.SNGL

The function is categorized as a Statistical function that returns the value with the maximum number of occurrences in a given data set.

If you were to say what function is the closest replica of the MODE function, then it is MODE.SNGL

Even the syntax for the function is the same, which is:

=MODE.SNGL(number1,[number2]...)

where,

• number1 - (required) a number or reference to a cell containing a numerical value or range of cells containing numbers
• number2 - (required) a number or reference to a cell containing a numerical value or range of cells containing numbers

To understand if any difference exists between both functions, let’s see a simple example. Suppose that you have the data as illustrated below:

In cell C9, we will use the formula =MODE(C2:C7), while in cell C10, the formula will be =MODE.SNGL(C2:C7), which gives us the result of 12.

As you can see, both functions work similarly to get the result. Well, what if you have more than two numbers with similar appearances?

As we had already seen, both the formulas return the number that appears first on the list, which is 14 in our range C2:C7, despite two numbers, 12 and 14, making the same number of appearances in the dataset.

There is something that we still haven't talked about - What if there are no duplicate or repeating numbers in the dataset?

In that case, both the MODE and MODE.SNGL function will return the #N/A error to signify that a mode value does not exist in the given range.

MODE vs. MODE.MULT Function

The next on the list is the MODE.MULT function. We previously saw two different functions that returned the same result, so the question is - Is the MODE?MULT function the same?

The function is categorized as a Statistical function that returns a vertical array of the values with the maximum number of occurrences in our dataset.

We have talked multiple times about how the previous two functions returned the first value with an equal number of appearances.

But MODE.MULT is built differently. It will return an array of all the values that make the most appearances in the dataset. However, the tricky part is using the function.

Suppose that you have the data as illustrated below:

To use the formula, first and foremost, we will select the range in which we expect the array of values to return. There is no specific number; you can guess and select the range directly. For example, here, we will choose the range C9:C11

Next, we will use the formula =MODE.MULT(C2:C7) in cell C9, and then press the Ctrl + Shift + Enter to get the array-based result which is equal to

The values we get in the selected cells are 12,13 and #N/A. So the interpretation can be made is that the range C2:C7 only had two values with a maximum equal number of occurrences in the dataset.

Since there was no third number similar to those, Excel returns the #N/A error as an indication to the user that the value appearing the maximum number of times in the array is exhausted.

Researched and Authored by Akash Bagul | Linkedin

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• FV Function
• HOUR Function
• HYPERLINK Function
• IFERROR Function
• LEN Function