Home › Excel functions › MOD

MOD Function in Excel: Find the Remainder

The MOD function returns the remainder after you divide one number by another. 10 divided by 3 is 3 with 1 left over, so =MOD(10, 3) returns 1. That sounds small, but it's behind many handy tricks: checking for even numbers, shading every other row and calculating shifts that run past midnight.

Excel CSE · 3 min read

In this guide

MOD syntax
Even or odd
Shade every other row
Time past midnight
Minutes to hours
Repeating numbers
FAQ

MOD syntax

=MOD(number, divisor)

Formula	Result	Why
`=MOD(10, 3)`	1	3 fits 3 times into 10, 1 is left
`=MOD(17, 2)`	1	17 is odd
`=MOD(3.75, 1)`	0.75	The decimal part
`=MOD(-10, 3)`	2	The result takes the sign of the divisor
`=MOD(10, -3)`	-2	Same rule, negative divisor
`=MOD(10, 0)`	#DIV/0!	You can't divide by 0

Check if a number is even or odd

An even number divided by 2 leaves nothing. So:

=IF(MOD(A2, 2) = 0, "Even", "Odd")

Excel also has ISEVEN and ISODD, but the MOD pattern works for any divisor: MOD(A2, 5) = 0 checks whether A2 is a multiple of 5.

Shade every other row

Combine MOD with ROW in conditional formatting:

Select the range, for example A2:D100.
Go to Home › Conditional Formatting › New Rule › Use a formula.
Enter =MOD(ROW(), 2) = 0 and pick a fill colour.

Every even row gets the colour. Use =MOD(ROW(), 3) = 0 for every third row. Unlike manual colouring, the stripes stay correct after sorting or inserting rows.

Time difference past midnight

A night shift from 22:00 to 06:00 gives a negative result with =B2-A2, and Excel shows ####. MOD fixes it, because a full day is 1 in Excel:

=MOD(B2 - A2, 1)

The result is 8:00. Multiply by 24 to get 8 hours as a number. More on this in how to calculate time difference.

Convert minutes to hours and minutes

With 135 minutes in A2, INT gives the whole hours and MOD the minutes that are left:

=INT(A2 / 60) & " h " & MOD(A2, 60) & " min"

Result: 2 h 15 min. The same idea turns days into weeks and days (INT(A2/7) and MOD(A2,7)).

Number in repeating groups

To give rows the numbers 1, 2, 3, 1, 2, 3… (for example to split people into three teams), enter in row 2 and fill down:

=MOD(ROW() - 2, 3) + 1

Frequently asked questions

What does MOD stand for?

Modulo, the mathematical name for the remainder after division.

Why does MOD return a positive number for a negative value?

In Excel the result always has the same sign as the divisor. =MOD(-10, 3) is 2, because -10 = -4 × 3 + 2.

How do I get the whole number part of a division?

Use =INT(A2/B2) or =QUOTIENT(A2, B2). Together with MOD you get both parts of the division.

Why does MOD give #NUM! with very large numbers?

Older Excel versions fail when the number divided by the divisor is extremely large. Rewrite it as =A2 - B2*INT(A2/B2), which gives the same result.