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.
=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. |
You can see that the remainder of dividing 10 by 4 is 2.
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)
Hit Enter and we get 0
Now, combine this with the ROW() function, which returns the number of the current row, and we can get intervals:
Type =MOD(ROW(),2)
Copy the formula down and we get this:
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.
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
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.