Excel MOD Function: Get the Remainder of a Division
Returns what is left over after dividing one number by another.
MOD divides one number by another and returns the remainder rather than the quotient. 17 divided by 5 is 3 with 2 left over, so =MOD(17, 5) is 2. That sounds like a maths-class curiosity until you notice what a remainder tells you: whether something divides evenly.
That is where all of MOD's real uses come from. =MOD(n, 2) is 0 for even numbers and 1 for odd. =MOD(ROW(), 3) cycles 0, 1, 2 down a column, which lets a formula do something to every third row. Conditional formatting rules that shade alternate rows are almost always MOD underneath.
It has one behaviour worth knowing before it surprises you: the sign of the result follows the divisor, not the number. =MOD(-4, 5) is 1, not -4. That is mathematically standard and different from what most programming languages do.
Syntax
=MOD(number, divisor)Arguments
numberRequired- The value being divided.
divisorRequired- What to divide by. It cannot be zero, and its sign determines the sign of the result.
The example data
Headers in row 1, data in A2:D6. C4 is blank and C6 holds text.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Sensor | Reading | Calibration | Batch |
| 2 | North inlet | 17 | 3 | 12 |
| 3 | South inlet | -4 | 5 | 7 |
| 4 | Header tank | 63 | 12 | |
| 5 | Overflow | 8 | 2 | 9 |
| 6 | Return line | -21 | n/a | 7 |
Worked examples
=MOD(B2, 5)Result: 2
17 divided by 5 leaves 2. The remainder, not the quotient.
=MOD(B2, 2) = 0Result: FALSE
The odd/even test. 17 is odd, so the remainder is 1 and this is FALSE.
=MOD(B3, 5)Result: 1
The negative case. -4 mod 5 is 1, because the sign follows the divisor rather than the number.
=IF(MOD(ROW(), 2) = 0, "Shade", "")Result: Shade
The alternating-rows pattern. In a conditional formatting rule this is what produces banded tables.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#DIV/0!
Why it happens: The divisor is zero, or a blank cell being read as zero.
How to fix it: Guard it: =IFERROR(MOD(A2, B2), 0), or test the divisor before dividing.
Negative result is unexpected
Why it happens: The sign follows the divisor. =MOD(4, -5) is -1 and =MOD(-4, 5) is 1, which surprises anyone coming from a programming language.
How to fix it: Keep the divisor positive unless you specifically want a negative range of results.
Decimal remainders look wrong
Why it happens: Floating-point arithmetic means MOD on decimals can return something like 0.09999999999.
How to fix it: Round the result: =ROUND(MOD(A2, 0.1), 10).
Tips worth knowing
- =MOD(n, 2) is the shortest odd/even test; ISODD and ISEVEN read better if others will maintain the sheet.
- =MOD(ROW(), 3) = 0 targets every third row, which generalises to any interval.
- Convert minutes past midnight to a clock time with =MOD(mins, 60) for the minutes part.
- QUOTIENT gives the whole-number part of the same division that MOD gives the remainder of.
Frequently asked questions
How do I check whether a number is even?
=MOD(A2, 2) = 0 returns TRUE for even numbers. ISEVEN(A2) does the same and reads more clearly, so prefer it unless you need the remainder itself for something else.
Why does MOD return a positive number for a negative input?
Excel follows the mathematical convention where the result takes the sign of the divisor. =MOD(-4, 5) is 1 because -4 is one more than -5. Most programming languages return -4 instead, which is why this catches developers.
How do I shade every other row?
Use a conditional formatting rule with the formula =MOD(ROW(), 2) = 0. It returns TRUE on even rows, so the format applies to alternating bands and keeps working when rows are inserted.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.