Very early in my intro-level statistics courses, I always cover the topics of mean, median, and mode.  For any dataset, there can only be a single mean and/or
median value, but there can be multiple modes.  For some strange reason, the =mode() command in Excel will only provide the first mode that appears in a dataset, even when multiple modes exist!  Below is a guide, however, on how to use the =mode.mult() command.  Never again should you incorrectly identify the number of modes in a dataset!

For this example, we’ll use the following dataset:

Just copy-and-paste this data or retype it into excel.

As you can see from the data, there are four modes: 1, 2, 3, and 4.  Let’s try to use the =mode() command first.  In an empty cell, type “=mode(” (without quotations).  Then, with your pointer, highlight the data (no label) and type “)” (without quotations).   You should get something that looks like “=mode(A2:A11)”.  Press enter.

Hmm.  The reported mode is “1”.  But shouldn’t it be 1, 2, 3, and 4?  Obviously something isn’t correct.  And indeed it isn’t.

When multiple modes are present, we need to use the =mode.mult() command.  This allows us to identify multiple commands, but it is certainly trickier to use.

First, click and drag to highlight multiple empty cells.  Let’s highlight six or seven, as seen below.

Second, with the cells highlighted, type “=mode.mult(“.  Third, highlight all of your data (no label) and type “)” (without quotations).  Now, you should get something that looks like =mode.mult(A2:A11).  Fourth, you MUST hold down CTRL and SHIFT while pressing enter.  Once you do this, you should get something like this below:

There’s our answer!  Excel says that there are four modes: 1, 2, 3, and 4.  But what is “#N/A”?  Because we initially had seven cells highlighted, Excel returns “#N/A” for each unused cell after the modes because it doesn’t know what else to say.  Weird, but at least we got the correct answer!

That’s how you find multiple modes in Excel.  Do you have any questions?  Or comments?  Feel free to email me at MHoward@SouthAlabama.edu.  I’d be happy to chat about statistics, Excel, or (most) anything else.