If you want the leftover from a division, the MOD function in Google Sheets returns the remainder in one step. In this article I’ll show you five examples covering basic remainders, even/odd checks, banded row patterns, leftover minutes, and cycling values through fixed buckets.
MOD Function Syntax in Google Sheets
Here is what the function looks like.
=MOD(dividend, divisor)
- dividend – the number being divided (the one on top in a fraction).
- divisor – the number you’re dividing by (the one on the bottom). Cannot be 0, or MOD throws a #DIV/0! error.
When to Use MOD Function
- Get the remainder after a division (the part that doesn’t divide evenly).
- Check if a number is even or odd by dividing by 2.
- Flag rows that fall on a fixed interval, like every third or every fifth row.
- Peel off leftover minutes after extracting whole hours from a total-minutes count.
- Cycle a running index through a fixed range of buckets (1, 2, 3, 1, 2, 3…).
Example 1: Basic Remainder of a Division
Let’s start with the textbook case, just feeding MOD two numbers.
Below is the dataset, with numbers in column A and divisors in column B.
I want column C to show the remainder when each number is divided by its divisor.
Here is the formula:
=MOD(A2,B2)
MOD does the division in the background and hands back only what’s left over. Take row 1: 10 divided by 3 is 3 with a remainder of one, so the cell shows one. Row 4 has 8 divided by 2 with no leftover, so the cell shows zero.
Pro Tip: If you want the integer-division half (how many whole times the divisor fits), use QUOTIENT instead. MOD and QUOTIENT are paired functions: together they reconstruct the original number.
Example 2: Check if a Number Is Even or Odd
Dividing by 2 turns MOD into a built-in even/odd flag.
Below is the dataset, with a single column of numbers in column A.
I want column B to flag even numbers with zero and odd numbers with one.
Here is the formula:
=MOD(A2,2)
Any even number divides cleanly by 2, so the leftover is zero. Any odd number has exactly one left over after the division. So 4, 12, and 100 all show zero, while 7, 25, and 33 all show one.
Pro Tip: For a more readable check, ISEVEN and ISODD do the same job and return TRUE/FALSE instead of 0/1. Use whichever fits the downstream formula better.
Example 3: Flag Every Third Row
MOD is a natural fit for marking rows on a fixed interval.
Below is the dataset, with row numbers in column A.
I want column B to say “Yes” for every third row and “No” for the rest.
Here is the formula:
=IF(MOD(A2,3)=0,"Yes","No")
How this formula works:
- MOD(A2, 3) cycles through the values 1, 2, 0, 1, 2, 0 as you walk down the rows.
- The zero only lands on multiples of 3 (rows 3, 6, 9, and so on).
- IF then checks for that zero and writes “Yes” when it hits, “No” otherwise.
Swap the 3 for any other interval (5, 10, etc.) to flag a different banding pattern.
Example 4: Leftover Minutes After Whole Hours
A common real-world use is splitting a total-minutes count into hours and minutes.
Below is the dataset, with total minutes in column A. Some are over an hour, some aren’t.
I want column B to show only the leftover minutes once full hours are subtracted out.
Here is the formula:
=MOD(A2,60)
Dividing by 60 strips out the whole hours and leaves only the remaining minutes. So 65 minutes is one hour plus five (the cell shows 5), 130 minutes is two hours plus ten (the cell shows 10), and 600 minutes is ten whole hours with nothing left over (the cell shows 0).
Example 5: Cycle Values Through Fixed Buckets
MOD with a small offset turns a running counter into a repeating cycle.
Below is the dataset, with a running index in column A.
I want column B to cycle through 1, 2, 3, 1, 2, 3 as I walk down the index.
Here is the formula:
=MOD(A2-1,3)+1
How this formula works:
- Subtracting 1 from the index shifts the starting point so the cycle aligns properly.
- MOD by 3 then wraps the shifted index into the range 0, 1, 2.
- Adding 1 back shifts the output range to 1, 2, 3 (which is what people usually want for bucket labels).
Swap the 3 for any other bucket size to cycle through that many values.
Tips & Common Mistakes
- Divisor of zero throws an error. MOD can’t divide by zero, so it shows #DIV/0! when the second argument is 0. Wrap in IFERROR if your divisor comes from another cell that might be empty.
- Negative numbers follow Sheets’ sign convention. When the dividend is negative, the result takes the sign of the divisor. So MOD(-7, 3) gives 2, not -1. Check carefully if you’re working with negatives.
- MOD and QUOTIENT are siblings. MOD gives the remainder; QUOTIENT gives the whole-number part of the division. Use both together when you need to split a number into hours + minutes, weeks + days, or any similar split.
That covers the main ways to use MOD in Google Sheets. The function shines when you pair it with IF for banding patterns, with a subtraction-and-add trick for bucket cycling, or with 2 as the divisor for a quick even/odd check.
List of All Google Sheets Functions
Related Google Sheets Functions / Articles: