MOD Function in Excel

Add to Favorites
Author: | Edits: don

The MOD function is very simple but it can be used to do wonderful things in Excel.

It returns the remainder after you divide one number by another.

Syntax

=MOD(number, divisor)
Argument Description
Number The number that will be divided by another number.
Divisor

The number by which the first number will be divided.

Example - Simple

  1. Type =MOD(
    1383a6e51bad58b891dac9a047e8b2a0.png
  2. Type the first number or select a cell that contains that number.
    46ecab988fb0bb2e6eb520377396ca01.png
  3. Type a comma and then type the second number, which is the number that we will use to divide the first one.
    a3dd143fa9b88459b73d43d54e4114b2.png
  4. Hit Enter and that's it!
    13bba999c0dfd10f7afac3813590466e.png

You can see that the remainder of dividing 10 by 4 is 2.

Example - More Helpful

So far, this might not help you unless you are doing straight-up math in Excel. However, we can use it to check what row or column we are in and this can really help us to create more powerful and useful spreadsheets.

If we divide 5 by 2, there will be a remainder, but if we divide 5 by 5 or 1, there will be no remainder and it is when there is no remainder that we can use this function to determine intervals. It sounds confusing so just pay attention to the example.

Type =MOD(5,1)

b97f57acf03b72a4e6f4447abbb6da9c.png

Hit Enter and we get 0

552fce06a51e52591c4491f54384f842.png

Now, combine this with the ROW() function, which returns the number of the current row, and we can get intervals:

Type =MOD(ROW(),2)

2ea5570b63e6eadf4caa7648b0376800.png

Copy the formula down and we get this:

dd11f780cd1324c9444ca591aae1e148.png

Using that pattern we can do everything from incrementing a value to automatically shading rows.

The MOD() function by itself is not so great, it is when we combine it with other functions that it shines.

Follow the links below for tutorials on more powerful things we can do in Excel with the help of the MOD() function.

Excel Tutorials that Use the MOD Function

Increment a Value Every X Number of Rows in Excel

Automatically Shade Every Other Row When You Add Data in Excel

Shade Every Other Row in Excel Quickly

Notes

The MOD function is best used in conjunction with other functions; using this as a building block allows you to create complex and useful formulas that will help you create better spreadsheets.

Make sure to download the attached sample file to see the MOD function in action.


Excel Function: MOD()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Sum Values from Every X Number of Rows in Excel
Tutorial: Add values from every x number of rows in Excel. For instance, add together every other va...
Understanding Formulas and Functions in Excel
Tutorial: In this tutorial I will cover the basic concepts of Formulas and Functions in Excel. A for...
Create a Custom Function in Excel - UDF
Tutorial: How to create a custom worksheet function in Excel. These are called User Defined Function...
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
Macro: Determine if a cell in Excel contains a formula or function with this UDF (user defined fu...
VLOOKUP() Function in Excel
Tutorial: Full explanation of the Vlookup function in Excel, what it is, how to use it, and when yo...
Easily Input Complex Functions in Excel
Tutorial: In this tutorial I am going to show you how to easily input complex Functions in Excel. To...
Tutorial Details
Excel Function: MOD()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course