MOD Function in Excel: Remainder After Division

📎 This article includes 1 downloadable practice file ↓

Excel Core FunctionsLevel: Foundational → IntermediateCompatibility: All Excel Versions

In this article

1. Syntax & Parameter Breakdown
2. Where You Will Use It
3. Practical Worked Examples
4. Mistakes People Make & How to Avoid Them
5. Frequently Asked Questions
6. How does MOD handle negative divisors?
7. How do you extract just minutes from a decimal hour value?
8. Can MOD be used in VBA as well?
9. MOD in practice
10. Where you will use it
11. Worked example
12. Mistakes people make
13. Continue learning

The MOD function in Excel is an essential spreadsheet tool designed to Calculate the remainder after dividing a number by a divisor, powering alternating row formatting, time calculations, and cyclic patterns.

Syntax & Parameter Breakdown

=MOD(number, divisor)

Argument	Requirement & Description
number	Required. The numeric value you want to divide (the dividend).
divisor	Required. The number by which you want to divide.

Where You Will Use It

Used to split elapsed hours and minutes, create conditional formatting rules for alternating rows, and group transactional items into batches.

Practical Worked Examples

Here are common real-world formula scenarios utilizing MOD:

Formula	Expected Behavior & Purpose
=MOD(ROW(), 2) = 0	Conditional formatting formula that highlights every even row in a table.
=MOD(End_Time - Start_Time, 1)	Calculates elapsed time accurately even when working shifts cross midnight.
=MOD(A2, 5) = 0	Identifies if a number in cell A2 is an exact multiple of 5.

Mistakes People Make & How to Avoid Them

Dividing by zero (=MOD(A2, 0)) triggers a #DIV/0! error. In Excel, the sign of the result matches the sign of the divisor.

Frequently Asked Questions

How does MOD handle negative divisors?

Excel’s MOD function follows the mathematical floor definition: the result always carries the same sign as the divisor.

How do you extract just minutes from a decimal hour value?

Use =MOD(A2, 1) * 60 to convert the fractional part of an hour into exact minutes.

Can MOD be used in VBA as well?

Yes, VBA has a native Mod operator: Result = Number Mod Divisor.

📚 Master spreadsheet formulas with our Free Excel Course or explore related functions in the Formula Lab.

MOD in practice

Where you will use it

• Alternate row shading
• Odd/even checks
• Minutes left after full hours

Worked example

Number	Divisor	Formula	Result
17	5	=MOD(A2,B2)	2
-7	3	=MOD(A3,B3)	2
125	60	=MOD(A4,B4)	5

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
Negative numbers	MOD takes the sign of the divisor: MOD(-7,3)=2
Floating point	MOD(0.3,0.1) is not 0; ROUND first

Continue learning

All Math & Trig guides →

RANDARRAY Function in Excel: A Whole Grid of Random NumbersRANDARRAY returns an array of random numbers, optionally whole numbers between limits — instant…PERCENTOF Function in Excel: Share of the TotalPERCENTOF returns a subset’s share of a total — mostly used inside GROUPBY and…RAND Function in Excel: Random Decimal Between 0 and 1RAND returns a new random number between 0 and 1 on every recalculation —…RANDBETWEEN Function in Excel: Random Whole Numbers for Test DataRANDBETWEEN returns a random integer between two numbers. It recalculates on every change —…

📎 Practice files for this article

• 📗
  Practice workbook: Math & rounding24 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 35 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.