Excel MOD Function: How to Find the Remainder in Excel

The Excel MOD function returns the remainder after one number is divided by another, using the syntax =MOD(number, divisor). Typing =MOD(23, 5) returns 3, because 5 fits into 23 four whole times with 3 left over.

One detail separates Excel’s MOD from a hand calculation, and it only shows up with negative numbers: the result always takes the sign of the divisor, not the sign of the number being divided. That single rule explains every surprising result MOD ever produces.

This guide covers the syntax, cell references, the negative-number rule and the formula behind it, the divide-by-zero error, even and odd tests, repeating patterns and every-nth-row highlighting, and how MOD differs from QUOTIENT in a way that trips up a lot of spreadsheets.

MOD Function Syntax

=MOD(number, divisor)

• number is the value being divided, called the dividend. Required.
• divisor is the value dividing it. Required, and it must not be 0.

Both arguments can be typed directly, entered as cell references, or produced by another formula. The function returns a single numeric value.

Basic MOD Examples

Two behaviours worth noting from this table. When the number is smaller than the divisor, MOD simply returns the number itself. And MOD is not restricted to whole numbers: =MOD(7.5, 2) returns the decimal 1.5.

Using Cell References

Typing values directly is fine for a one-off check. In a real worksheet the arguments come from cells.

Suppose column A holds totals and column B holds group sizes:

To lock the divisor to one cell while filling down, use an absolute reference:

=MOD(A2, $B$1)

The $B$1 stays fixed as the formula is copied, while A2 steps down to A3, A4 and so on.

To get the number of complete boxes alongside the leftover, pair MOD with QUOTIENT:

=QUOTIENT(A2, B2)     returns 13 full boxes
=MOD(A2, B2)          returns 2 pencils left over

MOD With Negative Numbers

This is where Excel surprises people. The rule is short:

The result of MOD always has the same sign as the divisor.

=MOD(-23, 5) returning 2 rather than −3 catches out anyone expecting the C-style behaviour. Excel is not wrong here; it is using a different and perfectly standard convention.

The documented formula behind MOD explains all four rows:

MOD(n, d) = n − d * INT(n / d)

INT rounds down toward negative infinity, not toward zero. For =MOD(-23, 5):

-23 / 5 = -4.6
INT(-4.6) = -5              (down, not toward zero)
-23 − 5 × (-5) = -23 + 25 = 2

This is the floored convention, the same one Python’s % uses. C, Java and JavaScript truncate instead and would return −3 for the same inputs. If you move formulas between a spreadsheet and code, that difference is a real source of bugs, and the remainder of negative numbers guide covers both conventions side by side.

Forcing a positive result

If you always want a non-negative answer regardless of the divisor’s sign, use the absolute value of the divisor:

=MOD(A2, ABS(B2))

The #DIV/0! Error

A divisor of 0 returns an error:

=MOD(23, 0)     →     #DIV/0!

An empty cell counts as 0, so =MOD(A2, B2) with B2 blank produces the same error. Guard against it with IFERROR:

=IFERROR(MOD(A2, B2), "")

or test the divisor explicitly, which gives you room for a clearer message:

=IF(B2 = 0, "No divisor", MOD(A2, B2))

Testing for Even and Odd

A number is even when dividing by 2 leaves nothing:

=MOD(A2, 2) = 0        TRUE for even numbers
=MOD(A2, 2) = 1        TRUE for positive odd numbers

Wrapped in IF, that becomes a readable label:

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

Excel also ships ISEVEN() and ISODD(), which read more clearly for this one job. MOD stays the better choice the moment the divisor is anything other than 2, since it handles every divisor with the same formula.

Highlighting Every Nth Row

MOD combined with ROW() is the standard way to act on a repeating pattern of rows.

Shade every other row. Select the data range, open Conditional Formatting, choose “Use a formula”, and enter:

=MOD(ROW(), 2) = 0

Every even-numbered row satisfies this and gets the format.

Act on every third row. Subtract 1 first so the count starts at the top of your data rather than at row 1 of the sheet:

=MOD(ROW() - 1, 3) = 0

Rows 1, 4, 7, 10 and so on return TRUE. The pattern for rows 1 to 8 is 0, 1, 2, 0, 1, 2, 0, 1, and only the zeros match.

Sum every nth value. To total every third entry in A2:A100:

=SUMPRODUCT((MOD(ROW(A2:A100) - ROW(A2), 3) = 0) * A2:A100)

Subtracting ROW(A2) makes the formula independent of where the range starts, so it keeps working if rows are inserted above it.

Repeating Cycles and Schedules

Any pattern that repeats on a fixed period is a MOD problem, because the remainder tells you the position within the cycle.

Position in a rotation. With 4 staff rotating through a duty and a day number in A2:

=MOD(A2 - 1, 4) + 1

The result cycles 1, 2, 3, 4, 1, 2, 3, 4. The - 1 and + 1 shift the natural 0-based remainder into a 1-based position number.

Days into a pay cycle. For a 14 day cycle and a day count in A2:

=MOD(A2, 14)

Day 45 returns 3, so day 45 is the third day of a cycle.

Hours on a 12 hour clock. For a 24 hour value in A2:

=MOD(A2, 12)

MOD and Time Values

Excel stores a time as a fraction of a day, so 1 represents 24 hours. That makes MOD the standard fix for a shift that crosses midnight.

Subtracting a 06:00 finish from a 22:00 start gives a negative number and displays as #####. Wrapping it in MOD with a divisor of 1 wraps the value back into a single day:

=MOD(B2 - A2, 1)

With A2 = 22:00 and B2 = 06:00, the raw difference is −0.666…, and MOD returns 0.333…, which formats as 8:00. The sign rule is doing the work: the divisor 1 is positive, so the result is forced positive.

Format the result cell as h:mm to read it as a duration.

MOD vs QUOTIENT

The two functions split a division between them.

For positive numbers they fit together perfectly:

QUOTIENT(23, 5) * 5 + MOD(23, 5)  =  4 * 5 + 3  =  23    ✓

With a negative number they do not. The two functions round in different directions, so the identity breaks:

QUOTIENT(-23, 5) * 5 + MOD(-23, 5)  =  -4 * 5 + 2  =  -18    ✗

The correct partner for MOD is INT, not QUOTIENT:

INT(-23 / 5) * 5 + MOD(-23, 5)  =  -5 * 5 + 2  =  -23    ✓

This matters in any worksheet that reconstructs a total from a quotient and a remainder while negative values are possible. Use INT(A2/B2) for the whole part whenever the numbers can go below zero.

Practical Worksheet Examples

Split minutes into hours and minutes. With total minutes in A2:

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

480 minutes returns “8 h 0 min”, and 500 returns “8 h 20 min”.

Convert a total in cents to dollars and cents. With cents in A2:

=INT(A2 / 100) & "." & TEXT(MOD(A2, 100), "00")

Flag values that are not exact multiples. To find quantities that will not pack evenly into boxes of 12:

=IF(MOD(A2, 12) = 0, "Exact", "Short by " & 12 - MOD(A2, 12))

A quantity of 158 returns “Short by 10”, because 10 more units would complete a fourteenth box.

Alternate two labels down a column.

=IF(MOD(ROW(), 2) = 0, "Group A", "Group B")

Common MOD Mistakes in Excel

• Expecting a negative result from a negative number. =MOD(-5, 3) returns 1, not −2. The divisor sets the sign.
• Pairing MOD with QUOTIENT on negative data. Use INT for the whole-number part instead.
• Leaving the divisor cell blank. Blank counts as 0 and raises #DIV/0!.
• Using =MOD(ROW(), 3) = 0 when the data starts partway down the sheet. Subtract the first row number so the pattern lines up with your data rather than with the sheet.
• Comparing a decimal remainder to 0 with =. Floating point means =MOD(0.3, 0.1) = 0 can return FALSE. Compare against a tolerance instead, for example =ABS(MOD(A2, 0.1)) < 0.000001.
• Feeding MOD an enormous number. With a very large ratio of number to divisor, older Excel versions return #NUM!. Reducing the number first avoids it.

Excel MOD FAQ

What does the MOD function do in Excel?

It returns the remainder after dividing one number by another. =MOD(17, 5) returns 2, because 5 goes into 17 three times with 2 left over. It answers “what is left over”, while QUOTIENT answers “how many whole times”.

Why does Excel MOD return a positive number for a negative input?

Because the result takes the sign of the divisor. MOD is defined as n − d * INT(n / d), and INT rounds down rather than toward zero, which pushes the answer to the divisor’s side of zero. =MOD(-23, 5) returns 2 for this reason.

How do I get the remainder and the quotient together in Excel?

Use =MOD(A2, B2) for the remainder and =QUOTIENT(A2, B2) for the whole part. If negative values are possible, use =INT(A2/B2) in place of QUOTIENT so the two results still reconstruct the original number.

Does Excel have a modulo operator like % in code?

No. Excel’s % symbol means percent, so 50% is 0.5. The MOD function is the only built-in way to get a remainder. The % operator in Python, JavaScript and C is a different thing entirely, compared in modulo in Python, JavaScript and C.

Can MOD be used with decimals?

Yes. =MOD(7.5, 2) returns 1.5 and =MOD(10.5, 0.25) returns 0. Be careful comparing decimal results to exact values, since floating point arithmetic can leave a tiny residue.

How do I highlight every 5th row using MOD?

Select the range, add a conditional formatting rule using a formula, and enter =MOD(ROW() - ROW($A$2) + 1, 5) = 0, replacing $A$2 with the first cell of your data. Rows 5, 10, 15 and so on relative to the start of the data will be formatted.

What is the difference between MOD in Excel and a remainder in ordinary division?

For positive numbers there is none: both give the leftover after whole copies of the divisor are removed, exactly as described in how to find the remainder. They only part company when one of the values is negative, where Excel forces the answer to match the divisor’s sign. The Remainder Calculator shows the plain arithmetic result for any pair of numbers.