MODE Function Examples in Excel, VBA, & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on November 9, 2023
Download Example Workbook

Download the example workbook

This tutorial demonstrates how to use the MODE Function in Excel to calculate the most common number.
MODE Main Function

What is the Mode?

The mode is the most repeated number in a group of numeric data. It also represents the central tendency of a data set, similar to mean or median.

MODE Function in Excel

The MODE function takes a range of data and returns the most frequently occurring number. 


Example 01 NEW

If there is no mode in a group of data, the MODE function will return #N/A

Example 02 NEW

Empty Cells or Cells with Text

The MODE Function ignores cells that are empty. However, it will return #N/A error if there are non-numeric data in the group.

Example 03 NEW

Important: The MODE Function will return the same error for numbers stored as text. To use the MODE Function with numbers stored as text, first, use the VALUE Function to convert the numbers stored as text to actual numbers.


Example 04 NEW NEW


The MODE.MULT and MODE.SNGL are new MODE Function variations available in Excel 2019.

MODE.SNGL is very similar to MODE. It is used when only one mode is present in the data set.


MODE.MULT is used when there is more than one mode present. It is an array function.


When building array functions, you must press CTRL + SHIFT + ENTER instead of just ENTER after creating your formula.

You’ll notice how the curly brackets appear. You can not just manually type in the curly brackets; you must use CTRL + SHIFT + ENTER.


Notice how the MODE.MULT function returns the result in a vertical array. We can display them in a horizontal array by using the TRANSPOSE Function. It is also an array function.


Example 06 NEW NEW

MODE function in Google Sheets

The MODE function works exactly the same in Google Sheets as in Excel.

MODE Google

MODE Examples in VBA

You can also use the MODE function in VBA. Type:


Executing the following VBA statement

Range("B2") = Application.WorksheetFunction.Mode(Range("A2:A8"))

will produce the following results

Vba mode function

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to List of Excel Functions