In spreadsheet mathematics, standard division produces decimal quotients (such as 14 / 4 = 3.5). However, in many engineering, shift-scheduling, inventory packaging, and data segmentation scenarios, you do not need the decimal quotient; instead, you need the integer remainder left over after integer division. For example, packing 14 items into boxes of 4 leaves 2 leftover loose items. In Google Sheets, the native MOD function performs modulo arithmetic to return this exact remainder.
Understanding the MOD Function Syntax
The MOD function requires two numerical inputs: the dividend and the divisor:
=MOD(dividend, divisor)- dividend: The number to be divided (the numerator).
- divisor: The number by which to divide (the denominator). Must not be zero, or Google Sheets returns a
#DIV/0!error.
Basic Modulo Calculation Example
To calculate the remainder when 17 is divided by 5:
- Click an empty cell in your Google Sheet.
- Enter the formula:
=MOD(17, 5) - Press
Enter.
Google Sheets calculates that 5 goes into 17 three times (totaling 15) with 2 remaining. The formula returns 2.
Highlighting Alternating Rows with MOD and ROW
One of the most practical applications of the MOD function is creating custom zebra-striping patterns across large datasets:
- Highlight your target table range (e.g.
A2:E100). - Click Format > Conditional formatting in the menu.
- Under Format rules, select Custom formula is.
- To highlight every even row, enter:
=MOD(ROW(), 2) = 0 - To highlight every third row (tri-colour grouping), enter:
=MOD(ROW(), 3) = 0 - Choose your background fill colour and click Done.
Extracting Pure Time from Serial Date Timestamps
Because Google Sheets represents dates as integers and time as a fractional portion of 24 hours (a decimal between 0 and 1), dividing any date-time value by 1 strips the date and isolates the time:
=MOD(A2, 1)Format the output cell as Format > Number > Time to display clock hours and minutes cleanly.