Updated August 23, 2023

## What is a MODE in Excel?

MODE in Excel is a statistical function that helps you find which value appears most frequently in a range of data. For instance, if you have a dataset with the numbers 5, 4, and 5. In this case, MODE in Excel will give the result as 5 since it appears most frequently (twice).

The MODE function in Excel can be particularly useful when working with a large dataset and you want to identify the most common value.

Excel functions, formula, charts, formatting creating excel dashboard & others

### Syntax

In the Syntax,

- number1: The first number or range for the dataset.
- number2, [number3], … (Optional): Additional numbers or cell references for the dataset.

##### Table of Contents

### How to Use MODE in Excel?

In some datasets, there can be more than one mode value. That is, two, three, or more values (number or text) appear a maximum number of times. Thus, some datasets have only one mode value (single), and others have more than one (multiple).

- MODE Function (To find Single Mode Value)
- MODE.SNGL (To find Single Mode Value)
- MODE.MULT (To find Multiple Mode Value)
- Pivot Table (To find Multiple Mode Value)

#### 1. MODE in Excel

Let’s consider an example involving the number of vacation photos people took during their trips. We need to find which value for the number of photos occurs more than once.

**Given:**

**Solution:**

**Step 1:** Select a cell where you want to display the result, **cell B9**.

**Step 2:** In the selected cell** (B9)**, enter the formula: **=MODE.**

**Step 3: **Select** MODE **by double-clicking from the given options.

**Step 4: **Select the range **B2 to B8** as the input, which contains the number of photos taken.

**Step 5:** Press **Enter. **The result in** cell B9 **will be the mode (the most frequently occurring value in the dataset).

**Result:**

The mode preferred number of photos taken is **120** because it appears thrice in the dataset (Alice, Emily, and Grace).

#### 2. SNGL Function Using Excel Ribbon

You can also use the MODE function in Excel using Ribbon. Let us use the same example given above and try to find the mode value using the Excel ribbon. However, we made a small change. The number for Grace is now 80.

**Note:**MODE.SNGL is the exact same as MODE.

**Step 1:** Select the cell** (B9)** where you want the result (the mode) to appear.

**Step 2:** Go to the **“Formulas”** tab on the **Excel ribbon**.

**Step 3:** In the **“Function Library”** group, click on the **“More Functions”** dropdown (small inverted triangle).

**Step 4:** Select **“Statistical”** and then choose **“MODE.SNGL.”**

**Step 5:** Once the **“Function Arguments”** dialog box appears,

- For the
**“Number1”**field, select the range of cells that contain your data (e.g., B2:B8 if your data is in column B from rows 2 to 8). - Leave the “Number2”, “Number3”, etc., fields empty, as you work with a single range.

**Step 6:** Click the **“OK”** button.

**Result:**

The MODE function will display the result (120) in the selected cell.

**Note:**If you notice, all 3 numbers (120, 80, and 150) appear twice in the dataset. In such cases, the MODE function returns the first number from the list, i.e., 120. To resolve this, we can use the MODE.MULT Function.

#### 3. MODE.MULT Function in Excel

We can also use the MODE function using Insert Function from the Formula tab. Let us use this method to see how we can find a mode for multimodal data (the dataset that has more than one mode value).

**Note:**The MODE.MULT will return more than one mode value only if all those mode values appear an equal number of times.

Let’s say we have a dataset that represents the scores of students in a class on a recent math quiz. We want to find the mode value for the scores.

**Step 1:** Select the cell where you want the results to appear. Let’s use cell **D3**.

**Step 2:** Go to the **“Formula”** tab in the Excel ribbon.

**Step 3:** Click on the **“Insert Function”** button. It looks like ** fx** and is usually present at the left of the formula bar. A dialog box for

**“Insert Function”**will open.

**Step 4:**From the list of functions that appear, select

**“MODE.MULT,”**present under the

**“Statistical Functions,”**and click

**“OK.”**

**Step 5:**A dialog box named

**“Function Arguments”**appears for the MODE.MULT function.

**Step 6:**In the

**“Number1”**field, select the range of data for which you want to find the modes. Select the range

**B2:B9**in this example and click

**“OK.”**

**Step 7:**Excel will display the results in the selected cells.

**Step 8:**Now, double-click on the first cell

**(D3)**and press

**Ctrl+Shift+Enter**. The array formula will now be enclosed in curly braces like this:

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

#### 4. MODE using Pivot Table

Suppose you have two columns, “Flower” and “Color,” and want to find all the mode values in the data and the number of times they appear. Let us use the pivot table to find the mode values.

**Given:**

**Step 1: **Go to the** “Insert” **tab in the Excel ribbon

**Step 2: **Click **“PivotTable”** from the **“Tables” **group. The PivotTable dialog box will appear.

**Step 3:** Verify that the** “Select a table **or **range” **option displays your data range correctly. If not, click and drag to adjust the content to cover your data.

**Step 4:** Choose the **“New Worksheet”** option to place the Pivot Table in a new sheet

**Step 5:** Click **“OK.”**

**Step 6:** In the new worksheet, you’ll see the **“PivotTable field” **on the right side.

In the field list,

- Drag the
**“Flower”**field to the**“Rows”**area - Drag the
**“Color”**field to the**“Columns”**

This will list unique flower names and colors in respective rows and columns.

**Step 7:** Again, drag any field (e.g., **“Color” or “Flower”**) to the **“Values”** area. Excel will automatically count the times that each color occurs in the dataset.

**Result:**

In the Pivot Table, you will see the flower names in rows and colors in columns. The cells where flower names and colors meet display how often the combination occurs.

For instance, the cell where **“Rose”** and **“Red”** intersect in the below image has the highest count, **“4.”** Hence, **“Rose”** with the color **“Red”** is the mode flower-color combination in the dataset.

### Things to Remember

- MODE shows the #N/A error value if there are no duplicates in the data.
- Arguments can be numbers or names, references, or arrays containing numbers.
- If you are unsure whether there is more than one mode in your data, you can use the MODE.MULT function to see all the mode values. You can check example 3 of this article.

### Frequently Asked Questions (FAQs)

**Q1. Which functions in Excel can you apply the MODE function to?**

**Answer: **We can use the MODE in Excel with several other Excel functions such as IF, SUMIF, and COUNTIF to count the occurrences of each unique value or find the sum of those values. We can also use the MATCH function to find information for a specific item.

**Q2. What is the purpose of using the mode formula in Excel?**

**Answer: **The mode formula in Excel helps find the most frequent value in a data set. It can help businesses find which value occurs most frequently and make decisions accordingly.

For example, if a company takes a survey from 100 people on which product they like the most. The company can then use MODE in Excel to find which product’s name occurs most frequently. This way, they can identify their best-selling product.

### Recommended Articles

This is a detailed guide on MODE in Excel. We explain the 4 methods in which you can use the MODE function in Excel to find the mode values. Visit the following articles for more Excel-related information.