MOD function in Excel

MOD returns the remainder after one number is divided by another.

Divide 20 by 12 and you get 1 with 8 left over. MOD gives you the 8. A remainder sounds like a small thing, but it is the key to anything that repeats in a cycle: every second row, every third item, days of the week, hours on a clock.

Syntax

=MOD(number, divisor)

Argument	What it is
`number`	The number to divide.
`divisor`	The number to divide by.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Twelve sales made by four salespeople in three regions.

	A	B	C	D	E	F
1	Date	Salesperson	Region	Product	Units	Amount
2	05-Jan-2026	Asha	North	Notebook	20	1200
3	12-Jan-2026	Ravi	South	Pen	50	500
4	20-Jan-2026	Meena	North	Bag	4	3000
5	02-Feb-2026	Asha	East	Pen	30	300
6	14-Feb-2026	Kabir	South	Notebook	15	900
7	28-Feb-2026	Ravi	North	Bag	2	1500
8	03-Mar-2026	Meena	East	Notebook	40	2400
9	15-Mar-2026	Asha	North	Bag	3	2250
10	22-Mar-2026	Kabir	South	Pen	60	600
11	01-Apr-2026	Ravi	East	Notebook	25	1500
12	18-Apr-2026	Meena	North	Pen	80	800
13	06-May-2026	Asha	South	Bag	5	3750

=MOD(E2,12)
Result: 8
20 units divided into dozens leaves a remainder of 8.
=MOD(E3,2)
Result: 0
Remainder on dividing by 2: 0 for an even number, 1 for an odd one. 50 is even.
=IF(MOD(E9,2)=0,"Even","Odd")
Result: Odd
The usual even-or-odd test. E9 holds 3.
=MOD(ROW(A5),2)
Result: 1
ROW gives the row number, 5. The remainder shows whether a row is odd or even, the basis of striped shading.
=MOD(135,60)
Result: 15
Minutes left after taking out the full hours from 135 minutes.
=MOD(-7,3)
Result: 2
With a negative number the result takes the sign of the divisor: 2, not -1.
=MOD(10,0)
Result: #DIV/0!
Dividing by zero is not possible: #DIV/0!.

How it works

MOD divides the number by the divisor and returns what is left after taking out as many whole divisors as possible. The result always lies between 0 and the divisor. When the number divides exactly, the result is 0.

Uses

Even or odd. =MOD(A1,2) is 0 for even numbers and 1 for odd numbers.

Every nth row. =MOD(ROW(),3)=0 is TRUE on every third row. Used as a formula rule in Conditional Formatting, =MOD(ROW(),2)=0 shades alternate rows. Inside SUMPRODUCT it adds every other row.

Hours and minutes. =INT(135/60) gives the hours and =MOD(135,60) the minutes left. The same pattern splits paise from rupees, inches from feet and items from full boxes.

The time part of a date and time. =MOD(A1,1) is the fraction of a day, the time, whatever the date. It is shorter than A1-INT(A1).

Shifts that cross midnight. With a start time in A1 and an end time in B1, =MOD(B1-A1,1) gives the right duration even when the shift ends after midnight, where a plain subtraction would be negative.

Does it divide exactly? =MOD(A1,5)=0 tests whether A1 is a multiple of 5. A leap year test is built from three such checks.

Going round in a loop. To number a long list 1, 2, 3, 1, 2, 3, write =MOD(ROW()-2,3)+1 starting in row 2. This shares people into teams or tasks into a rota.

Negative numbers

The result of MOD always carries the sign of the divisor. =MOD(-7,3) is 2, not -1. Excel takes out whole threes going downwards, which reaches -9, and 2 is what must be added to get back to -7. Many programming languages give -1 here. If your sheet has negative values, test what you get.

Common mistakes

#DIV/0! when the divisor is zero or an empty cell.
Tiny decimal errors. =MOD(1.1,0.1) may not be exactly 0. Work in whole numbers where you can, for example in paise.
Confusing MOD with a percentage. In some programming languages the percent sign means remainder. In Excel, % after a number means "divided by 100".

In Google Sheets

MOD is the same in Google Sheets.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.