The MOD function returns the remainder after one number is divided by another. It is useful for finding leftover amounts, grouping rows, and testing whether a number is even or odd.
=MOD(number, divisor)What does MOD do?
MOD is a simple but surprisingly useful math function. It takes two numbers, divides the first by the second, and gives you only the remainder. For example, =MOD(10,3) returns 1 because 3 goes into 10 three times with 1 left over. You do not need to do the division yourself; Excel handles it and returns the leftover value.
Remainders matter in everyday office work. You might need to know how many items are left after packing full boxes, which rows belong to every third record, or whether a number is even or odd. MOD answers those questions in one short formula, which makes it a handy building block inside larger formulas such as IF, SUMPRODUCT, and conditional formatting rules.
A common real situation is scheduling. If staff rotate through a weekly pattern, MOD can turn a row number into a repeating cycle so you can assign shifts, colours, or review dates automatically. Once you understand the remainder idea, you can reuse it in many small business calculations.
When to use MOD
- Finding how many items are left after packing full boxes or crates.
- Assigning rows to repeating groups such as teams or shifts.
- Checking whether a number is even or odd.
- Highlighting every nth row with conditional formatting.
- Building repeating schedules based on a row or day number.
- Splitting amounts into equal parts and finding the leftover.
MOD syntax
=MOD(number, divisor)| Argument | Required? | What to enter |
|---|---|---|
number | Required | The value you want to divide. It can be a number, a cell reference, or a formula result. Example: 17 or B2. |
divisor | Required | The number you divide by. It must not be zero. Example: 5 or C2. |
What it returns: A number: the remainder after dividing number by divisor. The result has the same sign as the divisor.
MOD examples
1 Basic remainder after division
This example shows the core idea. The goal is to find what is left after dividing each quantity by 7. The formula =MOD(B2,7) divides the value in B2 by 7 and returns only the remainder. For 23, 7 goes in three times (21) with 2 left over, so the result is 2. The same pattern applies down the column.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Quantity | |||
| 2 | Pens | 23 | 2 | ||
| 3 | Pencils | 41 | |||
| 4 | Erasers | 15 | |||
| 5 | Rulers | 30 | |||
| 6 |
2 Check whether a number is even or odd
MOD is a quick way to test for even and odd numbers. The goal is to see whether each order number is even. The formula =MOD(B2,2) divides the order number by 2. An even number leaves a remainder of 0, while an odd number leaves 1. You can wrap this in IF to display words such as Even or Odd.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Order No | |||
| 2 | SO-101 | 101 | 1 | ||
| 3 | SO-102 | 102 | |||
| 4 | SO-103 | 103 | |||
| 5 | SO-104 | 104 | |||
| 6 |
3 Find leftover items after packing full boxes
Here MOD solves a packing problem. The goal is to know how many units are left after filling boxes of 12. The formula =MOD(B2,12) divides the stock count by 12 and returns the remainder. For 50 units, four full boxes use 48, leaving 2 units. The leftover value tells you what will not fit in a full box.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Units in Stock | |||
| 2 | Mugs | 50 | 2 | ||
| 3 | Plates | 37 | |||
| 4 | Bowls | 24 | |||
| 5 | Glasses | 61 | |||
| 6 |
4 Calculate a repeating weekly schedule
This example turns a day number into a repeating weekly pattern. The goal is to assign a shift based on how many days have passed. The formula =MOD(B2,3)+1 divides the day number by 3 and adds 1, producing a cycle of 1, 2, and 3. Day 0 returns 1, day 1 returns 2, day 2 returns 3, and day 3 returns 1 again. This is a simple way to rotate shifts or tasks without a long lookup table.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Day Number | |||
| 2 | 2024-01-01 | 0 | 1 | ||
| 3 | 2024-01-02 | 1 | |||
| 4 | 2024-01-03 | 2 | |||
| 5 | 2024-01-04 | 3 | |||
| 6 |
Common MOD errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #DIV/0! | The divisor is zero or refers to an empty cell that Excel treats as zero. | Make sure the divisor is not zero. Use IF to guard the formula, for example =IF(C2=0,"",MOD(B2,C2)). |
| #VALUE! | One of the arguments is text that cannot be converted to a number. | Check that both arguments are numbers or numeric cell references. Remove spaces or text from the cells. |
| #NAME? | The function name is misspelled, such as MODD or MOD( without a closing bracket. | Type =MOD( and let Excel suggest the function, then close all brackets. |
| Wrong sign result | The number is negative and the divisor is positive, so the result takes the sign of the divisor. | Remember that MOD returns a result with the same sign as the divisor. Use ABS or adjust the divisor if you need a positive remainder. |
Tips for using MOD
- Use MOD with
ROW()to highlight every nth row in conditional formatting. - Wrap MOD in
IFto turn remainders into labels such as Even or Odd. - Test for a zero remainder to identify exact multiples.
- Keep the divisor in its own cell so you can change the group size easily.
- Remember that MOD returns the sign of the divisor, not the number.
- Combine MOD with
SUMPRODUCTto count values that meet a repeating pattern.
Frequently asked questions
What does the MOD function do in Excel?
MOD returns the remainder after dividing one number by another. For example, =MOD(10,3) returns 1 because 3 goes into 10 three times with 1 left over.
Why does MOD return a negative number?
MOD takes the sign of the divisor. If the divisor is negative, the result is negative or zero. Use a positive divisor if you want a positive remainder.
Can MOD be used to check if a number is even or odd?
Yes. =MOD(number,2) returns 0 for even numbers and 1 for odd numbers. You can wrap it in IF to display text such as Even or Odd.
What happens if the divisor is zero?
Excel returns the #DIV/0! error because division by zero is not allowed. Check the divisor cell or use an IF guard.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. MOD reference