How to Use MOD in Excel
Returns the remainder after division — the standard trick for alternating row colours, shift patterns and even/odd checks.
What is MOD in Microsoft Excel?
MOD divides one number by another and gives you what is left over. It returns #DIV/0! when the divisor is zero. In practice it is used for patterns: MOD(ROW(),2) tells you whether a row is odd or even, which drives banded formatting and alternating shift rosters.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| number | Required | The value being divided. |
| divisor | Required | The number to divide by. Cannot be zero. |
When to Use MOD (Real-World Business Scenarios)
- 1
Alternating row shading with conditional formatting: =MOD(ROW(),2)=0.
- 2
Building repeating cycles such as a 3-shift roster or every-4th-week schedule.
- 3
Testing whether a number is even or odd with =MOD(A2,2)=0.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Row | Value | Remainder |
|---|---|---|
| 1 | 17 | 1 |
| 2 | 24 | 0 |
| 3 | 31 | 1 |
How it works: 17 divided by 4 is 4 with 1 left over, so MOD returns 1. A remainder of 0 means the value divides evenly.
Common Errors & How to Fix Them
Why it happens: The divisor is zero or points to an empty cell.
Fix: Guard the divisor: =IF(C2=0,"",MOD(B2,C2)) or ensure the divisor column is populated.
Why it happens: The number is negative, and Excel follows the sign of the divisor rather than the number.
Fix: Use =MOD(ABS(B2),C2) if you only care about magnitude, or =B2-C2*INT(B2/C2) for a sign-preserving remainder.
Try MOD Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (MOD)
What does the MOD formula do in Excel?
MOD divides one number by another and gives you what is left over. It returns #DIV/0! when the divisor is zero. In practice it is used for patterns: MOD(ROW(),2) tells you whether a row is odd or even, which drives banded formatting and alternating shift rosters.
What is the syntax for MOD?
What are common mistakes when using MOD?
#DIV/0!: The divisor is zero or points to an empty cell. (Guard the divisor: =IF(C2=0,"",MOD(B2,C2)) or ensure the divisor column is populated.) Negative remainder: The number is negative, and Excel follows the sign of the divisor rather than the number. (Use =MOD(ABS(B2),C2) if you only care about magnitude, or =B2-C2*INT(B2/C2) for a sign-preserving remainder.)
Related Excel Formulas
Keep building your formula toolkit with these math & stats functions.
- SUMIFSSums numerical values across multiple conditions (e.g. Sales in North Region for Q1).
- COUNTIFSCounts the number of rows that satisfy multiple simultaneous criteria.
- SUMAdds numbers across cells, ranges and entire columns — the first formula most people ever type in Excel.
- SUMIFAdds only the numbers that match one condition — the fastest way to total a region, product or status without a Pivot Table.
- COUNTIFCounts how many cells match a condition — duplicates, blanks, specific text, or values above a limit.
- AVERAGEReturns the arithmetic mean of a range while automatically ignoring blanks and text.
Master MOD in Intermediate Excel
Learn MOD along with our formula library, practice files, and verifiable certificates in our complete curriculum.