## What Is MODE Function In Excel?

The

MODEfunction in Excel determines the most frequently occurring value in a given data set. And if the supplied array contains two or more modes, the function returns the least mode value.

The **MODE **Excel function is an inbuilt **Statistical** function that users can use to find the mode of a given data range while performing financial evaluations. However, the functions **MODE.MULT** and **MODE.SNGL **replaced the **MODE() **in Excel 2010, offering improved accuracy. And the **MODE()** is now available as a **Compatibility** function in Excel for backward compatibility.

For example, the below table shows a list of smartphone models and their popularity in 2022 as percentage values in columns A and B, respectively.

Suppose we require to determine the most repetitive percentage value in column B and display the result in the target cell B9. Then, considering the **MODE Excel function definition**, we can apply the function in cell B9.

In the above **MODE function example**, the **MODE()** takes the data range B2:B6 as the input and determines the percentage value that occurs maximum times. And as the value **51% **occurs twice in the list, while the remaining values occur only once, the function returns **51%** as the mode value for the data range B2:B6.

Please note that the data format in cell B9 is **Percentage**, as highlighted in the above image.

##### Table of contents

###### Key Takeaways

- The
**MODE**Excel function returns the most frequently appearing data point in the given data set as the mode value. Users can apply the**MODE()**to determine the mode of the data set required in financial calculations. - The
**MODE()**takes one mandatory number argument as input and can accept 2 to 254 optional number arguments. - The
**MODE()**gives excellent results when used with other Excel functions, such as**INDEX**and**MATCH**. - The latest versions of the
**MODE()**,**MODE.SNGL()**and**MODE.MULT()**are available in Excel in the**Statistical**functions category in the**Formulas**tab. They provide improved accuracy than the**Compatibility**function**MODE**.

### MODE() Excel Formula

The syntax of **MODE **Excel formula is:

where,

**number1**: The first number we require to calculate the mode value for.**number2,**…**:**The subsequent 2 to 254 number arguments for which we require to calculate the mode value.

While the first argument in the above **MODE **Excel formula is mandatory, the remaining number arguments are optional. And we can supply them as arguments (Excel cell references or array references) separated by commas or an array reference.

Please ensure we adhere to the following points when applying the **MODE()**.

- The
**MODE Excel formula arguments**can be numeric values, named ranges in Excel , arrays, or cell references to numeric values. - If a provided argument is a text, logical value, or empty cell, the
**MODE()**ignores it. However, it counts cells containing zero. - Suppose the
**MODE Excel formula arguments**are error values or texts that do not translate into numeric values. Then they can create errors in the mode value calculation. - If the supplied data set contains no repetitive data points, the
**MODE Excel function return value**will be the**#N/A**error.

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

### How To Use MODE Excel Function?

The steps to use the **MODE **Excel function are as follows:

- First, ensure the source data is accurate and free from error values or texts that do not translate into numbers.
- Then, select the target cell and enter the
**MODE()**. - Finally, press
**Enter**to view the**MODE return value**.

Below is an example to clearly understand the above steps.

Consider the following table showing six test results over five days.

And suppose we require to determine the mode value for each test and display the results in column G.

Then, we can apply the **MODE **Excel function in the target cells G2:G7 and achieve the required mode values.

**Select the target cell G2, enter the following MODE(), and press Enter.****=MODE(B2,C2,D2,E2,F2)**

Alternatively, we can select the target cell G2 and click**Formulas**→**More Functions**→**Compatibility**→**MODE**to apply the**MODE()**through the**Function Arguments**window.

Now, we can start entering the required numbers or cell references to the specific numbers as the arguments in the**Function Arguments**window. First, we will see the provision to enter two number arguments. But our data set contains five data points. So, to enter the remaining three values, click the second argument field. The window will show the field to enter the third argument. Likewise, we must click the successive number argument fields to supply the rest of the data points.

And once we supply the required arguments and click**OK**, the**MODE()**gets executed. We can view the output,**50**, in the target cell G2.

We can also enter the**MODE()**arguments as an array range, as shown below.**Select the target cell G3, enter the following MODE(), and press Enter.****=MODE(B3:F3)****Please Note:**Suppose we must determine the mode for data values across multiple array ranges. Then, we can enter the array ranges separated by commas or the entire cell range that includes all the specific array ranges.

Now, we shall see what happens when we use the**MODE()**for data sets containing empty cells, texts, logical values, or no reoccurring data points.**Drag the fill handle downwards to copy the formula in cell range G4:G7.**

In the above**MODE Excel function example**, row 4 contains empty cells. So, the function ignores them to return the number that appears most frequently,**20**, as the output.

On the other hand, the above results show the**#N/A**error in cell range G5:G7. The reasons are:

• The data set in row 5 does not contain any reoccurring data points; hence, it has no mode value.

• The data set in row 6 contains texts that do not translate into numbers.

• The data set in row 7 contains only logical values, which the**MODE()**ignores, leading to the data set not containing any numbers for which the**MODE()**can find the mode value.

### Examples

Below are a few more examples to effectively explain the **MODE Excel function definition**.

#### Example #1

In this example, we will learn how the **MODE **Excel function behaves when a data set has more than one mode.

Assume the table below contains a list of tasks and the estimated days to complete each task.

We have to calculate the mode of the day values, provided in column B, to display it in the target cell E1. Then, here is how we can apply the **MODE **Excel function in cell E1 to get the required mode value.

**Step 1: **Select the target cell E1, enter the following **MODE()**, and press **Enter**.

**=MODE(B2:B7)**

The values **3** and **4** in the above data set in column B repeat twice. And thus, the data set has two mode values, **3** and **4**. However, the **MODE() **returns the lowest mode; hence, the **MODE()** return value is **3** in this case.

#### Example #2

We can apply the **MODE **Excel function with other inbuilt Excel functions to achieve fruitful results.

For example, we can use the **MODE()** with **INDEX** and **MATCH** functions to determine the most repeatedly occurring word in a given list of words.

Consider the following table. It contains the list of the top 15 richest towns in the US and their corresponding states mentioned in columns A and B, respectively.

And suppose the requirement is to find the US state with the highest number of richest towns, based on the above list, and show the result in the target cell B19. Then here is how we can proceed to get the required state name.

**Step 1: **Select the target cell B19, enter the below formula containing the **MODE **Excel function, along with the **INDEX** and **MATCH** functions, and press **Enter**.

**=INDEX(B2:B16,MODE(MATCH(B2:B16,B2:B16,0)))**

In the above formula, the **MATCH** Excel function returns the relative position of each state in the array of state names, matching the specific state name in a specified order. So, it returns **{1;2;1;4;1;6;7;8;9;7;6;2;13;9;15}**.

Now, this array range becomes the input for the **MODE()**. And thus, it returns the value **1**, as **one **occurs thrice in the array range.

Finally, the **INDEX Excel function **returns the value at the intersection of a specific row and column in the provided range. I

n this example, the range is B2:B16, and the row number is **1**. And so, it returns the output **California**.

#### Example #3

Let us now see how the latest versions of the **MODE **Excel function, **MODE.SNGL **and **MODE.MULT** work.

The syntax for the functions **MODE.SNGL** and **MODE.MULT **is the same as **MODE()**. But we will have to execute the **MODE.MULT() **as an array Excel formula using the keys **Ctrl** + **Shift** + **Enter**. The reason is that the **MODE.MULT() **output will be an array for a data set with multiple modes.

Suppose the first table in the below image shows the marks secured by a student in eight subjects.

We shall apply the above functions to see the mode each function returns in the target cells B11:B14.

**Step 1: **Select the target cell B11, enter the **MODE **Excel function, and press **Enter**.

**=MODE(B2:B9)**

**Step 2: **Select the target cell B12. Enter the **MODE.SNGL()**, and press **Enter**.

**=MODE.SNGL(B2:B9)**

**Step 3: **Select the cell range B13:B14. Enter the **MODE.MULT()**, and press the keys **Ctrl** + **Shift** + **Enter** to execute it as an array formula.

**{=MODE.MULT(B2:B9)}**

In this example, values **45** and **49** occur twice in the score list; thus, they are the modes of the given data set. So, as per the definition, the **MODE() **returns the lowest mode, **45**, as the output. And the **MODE.SNGL()**, the replacement of the **MODE()**, also gives the same result, **45**.

However, the **MODE.MULT()** helps display all the modes of the given data set. So, when we select the two target cells, B13:B14, and apply the function as an array formula, it returns the two modes, **45 **and **49**.

And if we are not sure about how many modes the given list has, we can select any number of target cells and execute the **MODE.MULT()**, as explained above.

The function will return all the modes in the respective target cells. And if the modes are less than the selected number of target cells, the remaining cells in the target array range will show the **#N/A** error.

**Please Note: **We can also access the functions **MODE.SNGL **and **MODE.MULT** by using the option **Formulas** → **More Functions** → **Statistical**.

### Important Things To Note

- Ensure the arguments we provide to the
**MODE**Excel function are numbers or named ranges, arrays, or cell references to the numeric values. - The
**MODE()**ignores the text, logical values, and empty cells while determining the mode of a data set. But it considers cells containing zero. - The
**MODE()**output will be**#N/A**if the data set does not contain any reoccurring data points. - If the
**MODE()**arguments include error values or texts that do not translate into numbers, we might get errors while executing the function.

### Frequently Asked Questions (FAQs)

**1. Is there a MODE function in Excel?**

There is a **MODE** function in Excel. It is in the **Formulas** tab. We can click **Formulas** → **More Functions **→ **Compatibility** → **MODE** to access it.

And to use the latest version of the **MODE()**, **MODE.SNGL() **and **MODE.MULT()**, click **Formulas** → **More Functions** → **Statistical**.

**2. Why is the MODE function in Excel not working?**

The **MODE** function in Excel may not work because of the below reasons:**•** The **MODE() **arguments are not numeric values.**•** The **MODE() **arguments contain error values or texts that do not translate into numeric values.**•** The source data set does not contain reoccurring data points.**•** All data points in the given data set are text, logical values, or empty cells.

**3. How to use the MODE() in Excel VBA?**

We can use the **MODE()** in Excel VBA using the below method:**Application.WorksheetFunction.Mode()**

Let us see how to use it in Excel VBA with an example.

The table below shows a grocery items list and the corresponding count of units delivered in columns A and B.

Suppose we have to determine the mode of units delivered and display the result in cell E2. Then the steps are:**•** **Step 1: **With the active sheet containing the above table, press the keys **Alt** + **F11 **to open the VBA Editor.**•** **Step 2: **Choose the required **VBAProject** and select **Insert** → **Module** to open the **Module1** window.**•** **Step 3: **Enter the VBA code containing the **MODE** function in the **Module1** window to determine the mode of units of grocery items delivered.**Sub mode_fn()**

Range(“E2”) = Application.WorksheetFunction.Mode(Range(“B2:B6”))**End Sub****•** **Step 4: **Now click on the **Run Sub/UserForm** icon in the top menu to execute the code.

And finally, if we open the active worksheet after the code gets executed, we will see the required mode value, **2000**, in the target cell E2.

In the above example, the **MODE()** in Excel VBA checks the supplied array range for the most frequently occurring values. And as the value **2000** appears twice in the given cell range, B2:B6, the VBA code output is **2000**.

### Download Template

This article must be helpful to understand the **MODE Excel Function**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to MODE Excel Function. Here we learn how to use the MODE formula along with step by step examples and a downloadable excel template. You can learn more from the following articles –

## Leave a Reply