Calculate the Mode in Excel

Your goal

You need to find the mode of a dataset in Excel.

Step-by-step tutorial

Very annoyingly, Excel's built-in functions for calculating the mode handle only numerical data. And even for numerical data, it's not entirely straightforward.

First we'll learn how to use the functions for numerical data. Then we'll see how to calculate the mode for categorical data.

Calculating the mode of numerical data

For data with a single mode, you can use MODE.SNGL:

Calculating the mode using MODE.SNGL

The result:

Calculating the mode using MODE.SNGL

Note that if your data happens to have multiple modes, then MODE.SNGL returns only the first one. You may prefer to use MODE.MULT instead, which returns multiple values using an Excel array formula. Array formulas are a little tricky if you haven't worked with them before (and most people haven't), so here are the precise steps to follow:

Step 1. Select a vertical range of cells to store your results:

Select your result cells
Select your result cells.

Step 2. Enter your MODE.MULT function. IMPORTANT: DO NOT PRESS ENTER (OR RETURN) YET!

Enter your MODE.MULT formula
Enter your MODE.MULT formula.

Step 3. Press Shift-Control-Enter (or Shift-Control-Return). This causes Excel to interpret your formula as an array formula, which produces array output. Excel will automatically wrap your formula in braces—don't type the braces yourself.

Press Shift-Control-Enter to create an array formula
Press Shift-Control-Enter to create an array formula.

The unused result cells will just say #N/A.

Calculating the mode of categorical data

For categorical data, we need to assign numbers to the categorical values, and then run either MODE.SNGL or MODE.MULT (see above) on the values. We'll use MODE.MULT here since the approach requires us to use an array function anyway. Before I present the steps, let me let me describe the three functions we'll use:

  • MATCH converts the categories to corresponding integers.
  • MODE.MULT calculates the mode(s) of the integers we got from MATCH.
  • INDEX converts the modes back to the categories.

With that, here are the steps:

Step 1. Select a vertical range of cells to store your results:

Select your result cells
Select your result cells.

Step 2. Enter your function, as shown below but adjusting the array references as needed. IMPORTANT: DO NOT PRESS ENTER (OR RETURN) YET!

Enter your formula
Enter your MODE.MULT formula.

Step 3. Press Shift-Control-Enter (or Shift-Control-Return). This causes Excel to interpret your formula as an array formula, which produces array output. Excel will automatically wrap your formula in braces—don't type the braces yourself.

Press Shift-Control-Enter to create an array formula
Press Shift-Control-Enter to create an array formula.

The unused result cells will just say #N/A.