How to use the Excel MOD function

In math, a modulo (also known as modulus) is a calculation that determines the remaining value following a division. This is a very commonly used method in computer science (and often one of the very first things you will learn when beginning to write just about any coding language).

Excel also has this feature as a nice simple formula called MOD. In this guide, we are going to run through how to use the Excel MOD function, along with some examples. We will cover:

• What is MOD?
• The formula
• Examples

Let’s start with what MOD does.

What is MOD?

As mentioned above, MOD (short for modulo), takes a number, and another number to divide the original value by, and calculates what value (if any) is remaining.

For example 7 modulo 3 leaves us with a value of 1. This is because 7 can be divided by 3 twice, but since 3 X 2 is 6 rather than 7, this leaves us with a remainder of 1. MOD simply works out what this remaining value is once we have completed the maximum number of rounds of this division.

One of the more common use cases for MOD is to quickly and easily determine if a value is odd or even. Any number where the result of a value MOD 2 equals 0 is even. Anything with a remainder is odd.

There are a range of use cases for modulo, but let’s jump ahead to look at the formula itself.

The MOD formula

The formula for MOD is super simple and only requires two values. The syntax is as follows:

=MOD(number, divisor)

That’s it! Looking at an example of our number being 6 and divisor being 3, we would see the following:

As mentioned, this formula is incredibly simple and just outputs a number as the result. The overall value of this function really depends on the use case itself. Let’s move onto some more examples.

Examples of MOD in Excel

Since the formula itself is quite a simple one, let’s look at a few different types of outputs we can get. Some like the above where we get a simple remainder, some where we get zero, and others when working with negative values.

Outputs of some of these are below:

Moving into something slightly more complex, let’s use the odd and even example mentioned at the start of the guide, and rather than outputting the number itself, let’s use an IF statement and create an output in the column stating whether a number is odd or even.

As mentioned, the way to determine if a number is odd or even is simply based on whether or not the MOD result is 0 or a remainder, when using a divisor of 2. If we have 0 then the number is even.

Our IF formula would then look like this:

=IF(MOD(Value,2)=0, “Even”, “Odd”)

Using our same numbers as above, but replacing all of the divisor numbers with a zero, we are left with the below results:

As we can see, by simply nesting our MOD function in Excel within another function it can become quite powerful. For the purposes of this guide we have kept things quite simple, but as we mentioned above, since MOD is such a common mathematical concept, and used so frequently in computer science, the possibilities here really are endless even in software such as Excel, thanks to additional tools in such as conditional formatting, advanced formulas and data validation functions.

This sums up our guide on how to use the Excel MOD function.

For more handy guides on working with Excel, be sure to check out our Excel Tips page.