Excel MOD Function
last modified October 5, 2026
The MOD function is a mathematical function in Excel that returns
the remainder after a number is divided by a divisor. It is the building block
for cyclic calculations, even/odd tests, alternating row formats, and time
extraction. This tutorial provides a comprehensive guide to using
MOD with detailed examples. You will learn the syntax, the sign
rule, practical applications, and how MOD differs from
QUOTIENT.
MOD Function Basics
The MOD function returns the remainder left over after division.
Unlike the % operator found in many programming languages, Excel's
MOD always takes the sign of the divisor. The syntax is simple but
the behavior around negative numbers and zero deserves attention.
| Component | Description |
|---|---|
| Function Name | MOD |
| Syntax | =MOD(number, divisor) |
| Arguments | number - The number to divide divisor - The number to divide by |
| Return Value | The remainder after division |
This table breaks down the essential components of the MOD
function. It shows the function name, basic syntax format, argument
requirements, and return value characteristics.
| Argument | Meaning |
|---|---|
| number | The dividend. It can be a literal, a cell reference, or an expression
that returns a number, such as A1-B1. |
| divisor | The value you divide by. It can be a literal, a cell reference, or an
expression. A divisor of zero returns #DIV/0!. |
MOD takes exactly two arguments. The result always lies between
zero and the divisor in magnitude, and it carries the divisor's sign. Excel
defines the function through the INT function:
MOD(n, d) = n - d*INT(n/d). Because INT rounds down to
the nearest integer, this definition produces the sign behavior described in
the next section.
Basic MOD Example
This example demonstrates the simplest use of the MOD function
with two individual numbers.
=MOD(10, 3)
This formula divides 10 by 3 and returns the remainder. The result is 1 because
3 goes into 10 three times (9) with 1 left over. When the divisor divides the
number exactly, the result is 0, as in =MOD(12, 4). This shows the
basic operation of MOD.
MOD with Cell References
A more practical use of MOD involves using cell references for the
number and divisor. Here is an example with cell references.
| A | B | C |
|---|---|---|
| 25 | 4 | |
| =MOD(A1, B1) |
The table shows a simple spreadsheet with values in cells A1 and B1, and a
MOD formula in cell C1 that calculates the remainder of A1 divided
by B1.
=MOD(A1, B1)
This formula calculates the remainder when 25 (A1) is divided by 4 (B1). The
result is 1 because 25 = 6 × 4 + 1. Using cell references makes
MOD flexible for spreadsheet calculations; copy the formula down a
column and it follows the data automatically.
The Sign Rule
The result of MOD has the same sign as the divisor, not the
number. This is the most surprising aspect of the function for people who are
used to the % operator in other languages. The table below lists
the four sign combinations plus two decimal cases.
| Formula | Result | Reason |
|---|---|---|
| =MOD(3, 2) | 1 | Positive divisor, positive remainder |
| =MOD(-3, 2) | 1 | Divisor is positive, so the remainder is positive |
| =MOD(3, -2) | -1 | Divisor is negative, so the remainder is negative |
| =MOD(-3, -2) | -1 | Divisor is negative, so the remainder is negative |
| =MOD(5.5, 2) | 1.5 | Decimal numbers are handled directly |
| =MOD(-5.5, 2) | 0.5 | A positive divisor flips the sign of the remainder |
Applying the definition n - d*INT(n/d) confirms each row. For
MOD(-3, 2), INT(-3/2) is INT(-1.5) = -2,
so the result is -3 - 2*(-2) = 1. For MOD(3, -2),
INT(3/-2) = INT(-1.5) = -2, so the result is
3 - (-2)*(-2) = -1.
=MOD(-3, 2) ' returns 1 =MOD(3, -2) ' returns -1 =MOD(-3, -2) ' returns -1
Identifying Odd/Even Numbers
MOD is commonly used to identify odd and even numbers by checking
the remainder when divided by 2. This example shows this practical application.
| A | B |
|---|---|
| 7 | =MOD(A1, 2) |
| 12 | =MOD(A2, 2) |
This table demonstrates using MOD to determine if numbers are odd
or even. A result of 0 means even and 1 means odd. This is a fundamental use of
MOD in data analysis.
=MOD(A1, 2)
This formula checks if the number in A1 is odd or even. For 7, it returns 1
(odd). For 12, it returns 0 (even). This technique is often combined with
conditional formatting or IF statements, for example
=IF(MOD(A1, 2)=0, "Even", "Odd").
Excel also provides the dedicated ISEVEN and ISODD
functions, which return TRUE or FALSE directly:
=ISEVEN(12) returns TRUE and
=ISODD(7) returns TRUE. Use those when you only need
a logical test; use MOD when you also need the remainder itself.
Creating Cyclic Patterns
MOD can create repeating patterns by cycling through a range of
values. This example shows how to create a repeating sequence of numbers.
| A | B |
|---|---|
| 1 | =MOD(A1, 5) |
| 2 | =MOD(A2, 5) |
| ... | ... |
| 6 | =MOD(A6, 5) |
The table illustrates how MOD creates a repeating sequence from 0
to 4. When the input reaches the divisor (5), the sequence starts over at 0.
=MOD(A1, 5)
This formula creates a repeating sequence of 0-4 as the input increases. For
1 it returns 1, for 5 it returns 0, for 6 it returns 1, and so on. The same
idea maps a running counter onto a set of labels or shift rotations with
=CHOOSE(MOD(A1, 4)+1, "A", "B", "C", "D").
Extracting Time Components
MOD is excellent for extracting specific time components from
Excel's serial time values. This example shows how to extract minutes from a
time value.
| A | B |
|---|---|
| 0.75 | =MOD(A1*24, 1)*60 |
The table demonstrates extracting minutes from a time value (0.75 represents
6:00 PM in Excel). Multiplying by 24 converts the day fraction to hours, then
MOD(..., 1) keeps only the fractional hour and the final
multiplication by 60 converts that fraction to minutes.
=MOD(A1*24, 1)*60
For 6:00 PM (0.75) the formula returns 0, because there are no extra minutes.
For 6:30 PM (0.770833) it returns 30, since 18.5 hours leaves 0.5 of an hour,
which is 30 minutes. To extract whole hours instead, use
=INT(A1*24).
Alternating Row Colors
MOD combined with ROW or COLUMN creates
alternating row or column colors in conditional formatting. This example shows
the formula used for this purpose.
=MOD(ROW(), 2)=0
This formula returns TRUE for even rows and FALSE for
odd rows. When used in conditional formatting, it applies a format to every
other row. ROW() returns the row number and MOD
checks whether it is even. To shade every third row, use
=MOD(ROW(), 3)=0; replace ROW with
COLUMN to shade columns instead.
Grouping Data into Buckets
MOD tells you the position of a value within a fixed-size group,
while FLOOR and QUOTIENT tell you which group it
belongs to. This example shows both sides of the calculation for groups of 10.
| A | Bucket start (=FLOOR(A, 10)) | Offset (=MOD(A, 10)) |
|---|---|---|
| 37 | 30 | 7 |
| 42 | 40 | 2 |
The table shows grouping numbers into tens. FLOOR(37, 10) returns
30, the start of the 30-39 bucket, and MOD(37, 10) returns 7, the
offset inside that bucket. Together they reconstruct the original value:
30 + 7 = 37.
=MOD(A1, 10)
This formula returns the remainder when dividing by 10, effectively the last
digit of a positive integer. For 37 it returns 7. Combine it with
FLOOR for the bucket label, or use
=QUOTIENT(A1, 10)*10 if you prefer integer division.
MOD vs QUOTIENT
MOD and QUOTIENT are two halves of a division. For
positive numbers they fit together neatly:
QUOTIENT(10, 3) returns 3 and MOD(10, 3) returns 1,
so 3 × 3 + 1 = 10. The two functions do not line up the same way for negative
numbers, because QUOTIENT truncates toward zero while
MOD is built on INT, which rounds down.
| Formula | Result | Notes |
|---|---|---|
| =QUOTIENT(10, 3) | 3 | Integer part of the division |
| =MOD(10, 3) | 1 | Remainder; 3*3 + 1 = 10 |
| =QUOTIENT(-10, 3) | -3 | Truncated toward zero |
| =MOD(-10, 3) | 2 | Uses INT(-3.333) = -4, not -3 |
Because QUOTIENT(-10, 3) is -3 while MOD(-10, 3) is
2, the pair does not rebuild -10. If you need the quotient that matches
MOD for negative values, use =INT(A1/B1) instead of
QUOTIENT.
=INT(A1/B1) ' the quotient MOD uses =QUOTIENT(A1, B1) ' truncates toward zero
MOD Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Divisor is zero or an empty cell | #DIV/0! error | Guard with =IF(B1=0, "", MOD(A1, B1)) |
| Either argument is non-numeric text | #VALUE! error | Test with ISNUMBER before applying MOD |
| Result looks negative with a positive divisor | Unexpected negative remainder | Excel takes the sign of the divisor; check the divisor's sign |
| Very large numbers | #NUM! error | Keep operands within the floating-point range |
| Underlying value is already an error | The same error is passed through | MOD cannot mask #N/A, #DIV/0!, or similar errors |
The MOD function is a versatile tool for mathematical and logical
operations in Excel. From simple remainders to even/odd tests, cyclic
sequences, alternating formats, and time extraction, MOD offers
powerful capabilities. Remember the two rules that trip people up: the result
takes the sign of the divisor, and a zero divisor produces
#DIV/0!. Mastering these details, along with the distinction from
QUOTIENT, will let you solve a wide range of spreadsheet problems
cleanly and reliably.
Author
List all Excel Formulas.