Another great question from a student in one of my Microsoft Excel courses.
What are some good uses of Excel’s MOD() function?
WHAT IS THE MOD() FUNCTION?
Before I go into a use for Excel’s MOD() Function, let’s make sure we understand what the function does. The MOD() function is actual quite a simple function. The following comes from the Microsoft site here…
Returns the remainder after number is divided by divisor. The result has the same sign as divisor.
The MOD function syntax has the following arguments:
- Number Required. The number for which you want to find the remainder.
- Divisor Required. The number by which you want to divide number.
The MOD functions purpose if to take two numbers, divide the numbers, and return what is left over (the remainder). For example: if you took 5 and divided that by 2.
You would get 2, as 2 can go into 5 2 times, with a remainder of 1
The MOD() function would return the 1, the remainder.
if you divided 4 by 2, the MOD() function would return 0, as 2 goes into 4, 2 times with nothing left over.
This is great and all but where would I use this. We can come up with many scenarios on using the MOD function, but I am going to show you one that I use it for. I use this in Excel as well as other applications such as Crystal Reports.
Imagine you have a list of records in Excel.
You would like to take that list of records and format every other rows background color. Something like this…
Have you ever done this manually? Select every other row and format it. This can become very tedious and time consuming, especially if you have 100s of rows of data. There are other tools, such as FORMAT AS TABLE, that can help automate this but sometimes those tools limit what you can now do with your data.
USE MOD() AND ROW() FUNCTIONS TOGETHER
=MOD(ROW(), 2) > 0
In the above formula I have taken the ROW() function and nested it within the MOD() Function. Remember, the MOD function takes two numbers and divides them, returning the remainder. Why the ROW() Function?
Ultimately I want to format every other row in my Excel list, the problem is how to automate this and identify which rows to format. The ROW() Function returns the row number of the row the function is used in. For example if I created the formula, =ROW(), and placed that in cell B10, the formula would return 10, because it was in row 10.
In the above formula the MOD Function takes the current row, ROW(), and divides that by 2. If there is a remainder then we know that the current row is an ODD number row.
If MOD() returns 0 then EVEN
If MOD() returns > 0 then ODD
In the following example I have taken the above formula and placed in a conditional format rules for the range of cells that make up my list.
That’s it. The next time you want to alternate row colors for 100s or 1000s of records, try this out.