# 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`

:

The result:

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:

**Step 2.** Enter your `MODE.MULT`

function. **IMPORTANT: DO NOT PRESS ENTER
(OR RETURN) YET!**

**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.

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:

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

**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.

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

.