The Excel Function MOD Explained:
Get Division Remainders

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

In elementary school, you probably did long division that resulted in remainders: the parts of the numerator that didn’t fit nicely into the divisor.

It’s probably been a while since you’ve done long division, but those remainders can be quite useful. And in Excel, you can get them with the MOD function.

Let’s take a look at the MOD function as well as a few cases in which you might find it useful.

Kasper Langmann, Co-founder of Spreadsheeto

Understanding modulo

Before we get into the specifics of the MOD function, let’s make sure we know exactly what we’re talking about.

The MOD function is named after “modulo,” which is a term used in computing. The divisor in a division equation is also known as the “modulus,” giving us the name of the operation.

If a number can’t be equally divided among a divisor, there’s a “leftover”—that’s the remainder.

For example, 10/3 = 9 remainder 1. Therefore, 10 mod 3 = 1.

3 goes into 10 three times, giving us 9 . . . and there’s one left over. That’s the modulo value.

If you find this idea or the notation confusing, don’t worry—it’ll become clear when we go over some examples.

Kasper Langmann, Co-founder of Spreadsheeto

And while this information might not seem very useful, there are quite a few cases in which it’s very handy. Let’s get started and take a look.

Get your FREE exercise file

To help you practice using the MOD function, we’ve put together an example workbook.

We’ll be using this workbook throughout the rest of the article, so download it now to follow along!

Download the FREE Exercise File

Download exercise file

Using the MOD function

The MOD function is remarkably simple. Take a look at the sparse syntax:

The syntax of the MOD function

=MOD(number, divisor)

The number is the number you’re dividing, and the divisor is the number you’re dividing it by.

So in 10/3, 10 is the number and 3 is the divisor.

That’s really all there is to it.

Open up the example workbook and we’ll take a look at a case in which this information might be useful.

On the first sheet, we have a number of classes. Each class has an associated group size, but most of them have more than class members than fit into a single group.

If each class is split into groups, there may be a few people left over. The MOD function tells us how many.

Click into cell C2 and type the following formula:

=MOD(A2, B2)

mod-function-formula

In this case, A2 is the number, and B2 is the divisor (we’re dividing A2 by B2).

Hit Enter and let’s see what happens.

mod-formula-result

The function returns a 1. A class of 46 can be divided into three equal groups of 15—but there will be one left over.

Click and drag the function down to fill in the rest of the cells in column C.

mod-function-results

All the results should be what you expect.

Except for the last one.

Here’s where the MOD function behaves a little strangely. Because 19 doesn’t fit into 20 evenly, Excel returns the entire number as the modulo value.

For this reason, you should always double-check your MOD results.

Combining MOD with other functions

This is where MOD gets really cool.

You can combine MOD with other functions to solve all sorts of problems. One of the most common is to perform some operation on every nth row or column.

This example gets a little complicated, so we’re going to break down each step. Don’t be intimidated by the whole formula!

Kasper Langmann, Co-founder of Spreadsheeto

On the second sheet of the workbook, we have several sales numbers for each month.

We can combine MOD with SUM and COLUMN to get the total sales for odd months, even months, every third month, and so on.

Here’s the formula we’re going to use to get the sum of sales for odd months:

=SUM(B2:G4*(MOD(COLUMN(B2:G2), 2)=0))

Let’s start in the middle, with the COLUMN function.

COLUMN returns the column number for all the cells in the range. So for B2, it returns 2. For C2, 3. For D2, 4. And so on.

column-numbers

MOD, as we’ve seen, returns the remainder of a division equation. The number divided in this case is the column number of each cell, and the divisor is 2.

In essence, this returns a 0 for every even column (keep in mind that we’re using the even columns because January is in column 2).

column-number-mods

The =0 is important here. This creates a logical statement. Whenever the MOD function returns a 0, we’ll have 0=0, which resolves to TRUE. Excel treats TRUE as 1.

When MOD returns 1, we’ll have 1=0, which is FALSE. FALSE is also treated as a 0.

The sum of each column is multiplied by that 1 or 0, and then added to the final total.

column-sum-multipliers

This formula is a rather complicated one. But it’s a great example of how you can use MOD to solve some rather difficult problems.

Kasper Langmann, Co-founder of Spreadsheeto

This is an array function, so to properly execute it, you’ll need to press Ctrl + Shift + Enter.

After that, take a deep breath—you have the sum of all the sales in odd months!

final-total

Feel free to sum each column individually and add the results to check the math. It works!

Kasper Langmann, Co-founder of Spreadsheeto

If you want to sum every third or fourth month, just change the divisor from 2 to 3 or 4.

Find remainders with ease

The MOD function is simple, and it’s easy to overlook.

But when you start combining it with other functions, it gets really powerful. Don’t underestimate how useful it can be!

And when you need a simple remainder calculated, you know what to use.