Find the Average (Mode) With Excel's MODE Function

Excel's MODE function can help you find averages

Christin Hume \ Unsplash

Using the MODE function in Excel makes it easy to find the value that occurs most often in a set of selected data. Learn how the mode functions, in addition to its new function replacements, MODE.SNGL and MODE.MULT.

This article has been tested to be compatible with Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel 2011 for Mac, and Excel 365.

Different MODE Functions

Financial figures in white with blue background

Getty Images/Sean Gladwell

Starting with Excel 2010, Microsoft introduced two alternatives to using the all-purpose MODE function:

  • MODE.SNGL: Used for data with a single mode; very similar to the MODE function covered here.
  • MODE.MULT: Used if the data is likely to have multiple modes — two or more values that occur with the same frequency.

To use the regular MODE function in Excel 2010 and later versions, it must be entered manually, as there is no dialog box associated with it in these versions of the program.

MODE Function Syntax and Arguments

Find the Most Frequent or Average Value with Excel's MODE Function
Find the Most Frequent or Average Value with Excel's MODE Function.

The syntax for the MODE function is:

= MODE ( Number1, Number2, Number3, ... Number255 )

Number1 (required): The values used to calculate the mode; this argument can contain the following:

  • Actual numbers separated by commas — see the example in row 3 in the image above.
  • A single range containing cell references to the location of the data in the worksheet — see the examples in rows 2, 5,6, and 7.
  • A combination of cell references and data — see the example in row 4.
  • A named range.
  • Individual cell references separated by commas.

Number2, Number3,... Number255 (optional): Additional values or cell references up to a maximum of 255 used to calculate the mode.

Factors to Know Before Using the MODE Function.

  • If the selected data range contains no duplicate data, the MODE function will — see row 7 in the image above.
  • If multiple values in the selected data occur with the same frequency (in other words, the data contains multiple modes) the function returns the first such mode it encounters as the mode for the entire dataset — see the example in row 5 in the image above.
  • The function ignores text strings, boolean values, and empty cells.

Finding the MODE in Excel

Excel MODE function example

Two options exist for entering the MODE function's arguments within Excel. You can either type in the data or cell references, or use point and click to select the cell references in the worksheet.

The advantage of point and click, which involves using the mouse to highlight the cells of data, is that it reduces the possibilities of errors caused by typing mistakes.

Enter the Mode Function into Cell F2 Using Point and Click

  1. Click on cell F2 make it the active cell.
  2. Type or paste =MODE( into the formula bar.
  3. Click and drag with the mouse to highlight cells A2 to D2 in the worksheet to enter this range as the function's arguments.
  4. Type a closing round bracket or parenthesis to enclose the function's argument.
  5. Press the Enter key on the keyboard to complete the function.

The answer 3 should appear in cell F2 since this number appears the most (twice) in the list of data. When you click on cell F2 the complete function appears in the formula bar above the worksheet.

= MODE(A2:D2)