The MOD function in Excel
MOD divides one number by another and returns what is left over. 17 divided by 5 is 3 with a remainder of 2, so =MOD(17,5) is 2.
A remainder of 0 means the number divides evenly. That gives simple tests for even numbers, every third row, or full boxes.
Syntax
=MOD(number, divisor)
| Argument | What it means |
|---|---|
| number | The number to divide. |
| divisor | The number to divide by. 0 gives a #DIV/0! error. |
Examples
1. Items left over
| A | B | |
|---|---|---|
| 1 | Items | 17 |
| 2 | Boxes of 5, left over | 2 |
Three full boxes hold 15 items. Two items remain.
2. Odd or even
| A | B | |
|---|---|---|
| 1 | Number | 18 |
| 2 | Type | Even |
An even number leaves 0 when divided by 2.
3. Clock time after a number of hours
| A | B | |
|---|---|---|
| 1 | Hours since midnight | 30 |
| 2 | Hour of day | 6 |
30 hours after midnight is 6 o'clock on the next day.
Common mistakes
Dividing by zero.
=MOD(10,0)returns #DIV/0!.Fix: Protect with IF, or check the divisor first.
Expecting a negative remainder. The result of MOD has the same sign as the divisor, so
=MOD(-3,2)is 1.Fix: That is the right behaviour for cycles. If you need the sign of the number, use ABS and decide the sign separately.
Frequently asked questions
How do I highlight every second row?
Use a conditional formatting rule with the formula =MOD(ROW(),2)=0.