Knowledge base / Excel
MOD in Excel: remainder signs, divisibility and cyclic positions
Follow a remainder with a negative divisor and build repeated numbering. Distinguish a position within a cycle from a sequential group number.

MOD returns a division remainder. With positive integers, the result is easy to picture: 17 items form four complete groups of 4, leaving 1. Negative arguments require a more precise rule: a nonzero remainder has the divisor’s sign, rather than always following the number being divided.
I use small synthetic examples to explain that remainder before building a cycle or a row-formatting rule. They help distinguish the function’s behavior from an informal description that happens to work only for positive inputs.
Syntax and the sign rule
Microsoft’s documentation describes two arguments:
=MOD(number,divisor)
The function is called MOD in both English and Polish Excel. English examples here use commas between arguments; Polish examples use semicolons. Actual separators depend on regional settings. The divisor must be nonzero: zero produces #DIV/0!.
| Formula | Result |
|---|---|
=MOD(17,4) |
1 |
=MOD(20,5) |
0 |
=MOD(-10,3) |
2 |
=MOD(10,-3) |
-2 |
=MOD(17,-4) |
-3 |
The final example is a useful check on your understanding. A negative divisor does not behave like its positive counterpart. For 17 and -4, the remainder is -3 rather than 1. Exact division produces a remainder of zero.
Why the remainder can be -3
Microsoft gives the relationship MOD(n,d) = n - d*INT(n/d). INT rounds down to an integer, including for negative inputs. That part of the rule explains the apparently surprising remainder.
Dividing 17 by -4 gives -4.25. Rounding down gives -5. Multiplying -4 by -5 gives 20, and subtracting 20 from 17 gives -3. Every step can be followed by hand, without treating a negative remainder as a special exception requiring an unrelated workaround.
For -10 and 3, the quotient is negative three and one third. Rounding down gives -4; multiplying by 3 gives -12, leaving a remainder of 2. Both results follow the same relationship. Excel is not simply trying to make a remainder positive whenever possible.
Even numbers and divisibility
For an integer in A2, =MOD(A2,2)=0 checks whether it is even. Both 6 and -6 satisfy the condition; 7 and -7 do not. With another positive divisor, zero means the input is a multiple of that divisor.
First establish that the input actually represents an integer. Checking an item number and processing a time stored as a fraction of a day are different tasks. A displayed value without decimal places is insufficient evidence, because cell formatting can hide part of the stored value.
Repeat positions from 1 to 3
Enter this formula in A2 and copy it down:
=MOD(ROW(A2)-ROW($A$2),3)+1
ROW returns the worksheet row number of a reference. Subtracting the starting row gives 0, 1, 2, 3, 4 and 5. Their remainders after division by 3 are 0, 1, 2, 0, 1 and 2. Adding one produces 1, 2, 3, 1, 2, 3.
This is a position within a cycle. A sequential three-row group number would instead look like 1, 1, 1, 2, 2, 2. Decide which information the reader needs before calling the column group. A misleading heading can undermine the interpretation of correctly calculated values.
Row-based numbering describes the worksheet arrangement. Do not treat it as a permanent record identifier. Sorting or moving rows requires checking the relationship between positions and observations again. A lasting assignment needs a rule based on what the records mean rather than where they happen to sit in the workbook.

Alternate row shading
For a range beginning in A2, a conditional-formatting formula can use:
=MOD(ROW()-ROW($A$2),2)=0
The first row has an offset of zero and satisfies the condition; the next does not. Select the table rows you want to shade and inspect the start of the range. Moving the reference point changes which half of the rows receives the format.
The colour helps someone read a table. It does not identify a common customer, create a business category or determine a processing priority. If highlighting should follow the data, the condition needs to reference that data rather than relying only on worksheet position.
Limits and a final exercise
For item-count exercises, begin with integers and an explicitly positive divisor. Fractional calculations with precision requirements also need an agreed representation and tolerance. This guide does not propose one comparison rule for every such process. For rounding itself, see FLOOR.
Follow MOD(10,3), MOD(-10,3) and MOD(10,-3) on paper. The results are 1, 2 and -2. Then change the cycle length from 3 to 4 and list the first six positions: 1, 2, 3, 4, 1, 2. Label the column position in cycle to preserve what the formula actually describes.
Key takeaways
Is the MOD remainder always positive?
A nonzero remainder has the divisor’s sign. MOD(17,-4) gives -3, while MOD(-10,3) gives 2.
What happens with a zero divisor?
The function returns a division-by-zero error. First establish what that input means in the source data.
Does a repeated 1, 2, 3 cycle identify sequential groups?
It identifies positions in a repeating cycle. Sequential three-row group numbers would be 1, 1, 1, 2, 2, 2 instead.
AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.
