How to Use the MODE Formula in Excel: Functions, Examples, and Writing Steps - Compute Expert

How to Use the MODE Formula in Excel: Functions, Examples, and Writing Steps


Home >> Excel Tutorials from Compute Expert >> Excel Formulas List >> How to Use the MODE Formula in Excel: Functions, Examples, and Writing Steps





In this tutorial, you will learn how to use the MODE formula in excel completely.

When working with numbers in excel, we may sometimes need to find the mode of a group of numbers. If we know how to use the MODE function in excel, then we can get our mode fast and easily.

Want to know more about MODE and how to use the MODE formula in excel correctly? Read this tutorial until its last part!

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more







What is the MODE Formula in Excel?

MODE formula in excel is a formula that helps you to get the mode of a group of numbers in excel. Mode is a number(s) that most frequently shows up from a group of numbers.



MODE Function in Excel

You can use MODE to get the number that most frequently shows up (the mode) from a group of numbers in excel.



MODE Result

The MODE result is a number that most frequently shows up from the numbers you input into MODE.

MODE will only return one mode (the first number that most frequently shows up from our numbers) to us even if there are multiple modes in its number inputs. Its result will be a #N/A error if there is no duplicate number in its number inputs (because that means there is no mode there).



Excel Version from Which We Can Start Using MODE

We can start using MODE in excel since excel 2003.



The Way to Write It and Its Inputs

Here is the general writing form of MODE in excel.

= MODE ( number1 , [ number2 ] , … )


You just need to input all the numbers you want to get your mode from into MODE. You can input them by typing the numbers directly in MODE or by using cells or cell ranges.

If you give more than one inputs, separate them with comma signs ( , ).




Example of Its Usage and Input

Here is an implementation example of the MODE formula in excel.

How to Use the MODE Formula in Excel: Functions, Examples, and Writing Steps - Screenshot of the MODE Implementation Example

As you can see there, we can get the mode of our numbers in excel by using MODE. Just input all the numbers from where we want to get our mode into MODE to get it.

MODE will only produce the first mode if there are multiple modes in our number inputs (second row of the example). It will produce a #N/A error if all of our number inputs just show up once each (third row of the example).

When you have empty cells, texts, or logic values in your MODE cell range input, it will ignore them (fourth, fifth, and sixth rows of the example). If you input them by typing them directly or by using cells, you will get a #VALUE error from MODE.



Writing Steps

Want guidance when you write your MODE formula in excel? Take a look at the MODE writing steps we have made for you below.

  1. Type an equal sign ( = ) in the cell where you want to put the mode of your numbers

    How to Use the MODE Formula in Excel: Functions, Examples, and Writing Steps - Screenshot of Step 1

  2. Type MODE (can be with small and large letters) and an open bracket sign after =

    How to Use the MODE Formula in Excel: Functions, Examples, and Writing Steps - Screenshot of Step 2

  3. Input all the numbers. from where you identify the mode. You can input them by typing them directly or by using cells or cell ranges. If you give more than one inputs, separate them with comma signs

    How to Use the MODE Formula in Excel: Functions, Examples, and Writing Steps - Screenshot of Step 3

  4. Type a close bracket sign after you have inputted all the numbers you want to get your mode from

    How to Use the MODE Formula in Excel: Functions, Examples, and Writing Steps - Screenshot of Step 4

  5. Press enter
  6. Done!

    How to Use the MODE Formula in Excel: Functions, Examples, and Writing Steps - Screenshot of Step 6




Get Multiple Mode Results: MODE.MULT

Have multiple modes in your group of numbers and you want to identify them all? If you use MODE, you will only get the first mode of your group of numbers.

So, what should we do if we need to get multiple modes? Well, if you use excel 2010 or newer, you can use the MODE.MULT function to help you with that.



The way to write MODE.MULT is similar to MODE in excel.

{ = MODE.MULT ( number1 , number2 , … ) }


However, as the result of MODE.MULT can be more than one, you need to treat it as an array formula.

That means you need to highlight multiple adjacent cells depending on how many modes you expect from MODE.MULT. As MODE.MULT produces its results vertically, you need to highlight a corresponding number of cells vertically too (if you want to get your modes horizontally, you need to write your MODE.MULT inside a TRANSPOSE function).

As an array formula, you need to also press Ctrl + Shift + Enter buttons after you write it instead of just Enter.

To better understand MODE.MULT, here is its implementation example in excel.

How to Use the MODE Formula in Excel: Functions, Examples, and Writing Steps - Screenshot of the MODE.MULT Implementation Example

As you can see there, we can get multiple modes from our MODE.MULT formula.

To get multiple modes from MODE.MULT, you need to highlight a vertical cell range before you write MODE.MULT. The number of cells you highlight in the cell range should be the same or more than the number of modes.

You must also press Ctrl + Shift + Enter after you write the MODE.MULT formula. If you just press Enter, you will only get the first mode.



MODE Alternative: MODE.SNGL

In excel 2010 or newer, you can also use MODE.SNGL as an alternative to MODE to get the same result. In fact, Microsoft recommends we use MODE.SNGL instead of MODE (they preserve MODE as a compatibility function to the older versions of Excel).

The way to write MODE.SNGL is also similar to MODE.

= MODE.SNGL ( number1 , number2 , … )


And here is its implementation example in excel.

How to Use the MODE Formula in Excel: Functions, Examples, and Writing Steps - Screenshot of the MODE.SNGL Implementation Example

No difference with MODE, isn’t it? As implied in its name, MODE.SNGL also produces only one mode even if there are multiple modes in our numbers.



Exercise

After you have learned how to use MODE in excel in this tutorial, let’s do an exercise. This is so you can understand the tutorial lessons more practically.

Download the exercise file and answer the following questions. Download the answer key file if you have done the exercise and want to check your answers.

Link to the exercise file:
Download here

Questions

Answer each question in the appropriate gray-colored cell according to the question number!
  1. What is the mode of the numbers on the left?
  2. What is the mode of the numbers on the left if we add 6, 8, 10, 6, and 10?
  3. What are the modes of the numbers on the left if we add 9, 9, 5, 3, and 3?

Link to the answer key file:
Download here



Additional Note

You can give up to 255 different inputs to MODE.



Related tutorials you should learn from:



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!





Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!




Follow our tutorial content also on


Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert