How to Use the MOD Function in Excel to Find Remainders

You must have practiced long division in school – even if you didn’t like it.

The MOD function is similar to long division, but it’s easier, quicker, and more concise.

But what is the MOD function? How do you use the MOD function, and how can it help you?

Let’s find the answers to all these questions and more below. So without further ado, let’s dive right in 🤿

Also, download our sample workbook here if you want to follow along with the guide.

What is MOD (modulo)?

The Excel MOD is a mathematical function that returns the remainder of a division operation. It works the same as long division and is very easy to use. The divisor in a division operation is referred to as the modulo – hence, the name.

Its use seems pretty simple and straightforward at first. But MOD is usually used in combination with other functions. This combination makes it twice more powerful, and we will see its use in the blog below.

Let’s have a look at the MOD function’s syntax.

=MOD(number, divisor)

It’s that simple 🧐

The number argument refers to the dividend – the number you want to divide. And the divisor is the number you will be dividing the dividend by.

For instance, in the formula:

=MOD(7,3) – 7 is the dividend, and 3 is the divisor.

Using the MOD function to get remainders (basic)

Let’s now see how to use the MOD function to get remainders.

Let’s suppose a factory has to send out hundreds of bundles of sweaters. The sweaters are packed in cartons before they are delivered.

And each carton can only contain a specific number of sweaters. Any sweaters left back will then go in simple shopping bags.

So here we have some orders for sweaters along with the carton sizes requested by customers.

Can we readily check how many sweaters from each order will go in cartons and how many will go in shopping bags?

Yes! The MOD function will do that for us. Check this out.

1. Select cell D2 in the Excel worksheet.
2. Enter the MOD formula as:

=MOD(B2, C2) 

3. Press Enter

MOD function returns a value of 2. That’s because the first order requires 26 sweaters, and a single carton can fit 12 sweaters.

We can fill up to 24 sweaters in 2 cartons, and we will be left with two sweaters that will go in shopping bags.

Now copy the formula to the remaining cells by double-clicking the fill handle.

You now have an estimated value of the remaining sweaters that would go separately.

How convenient is that 😃

Try it yourself using the sample workbook.

SUM every Nth row formula example

In real-time, Excel users rarely use the MOD function on their own. It is often used in combination with other functions that require the remainder value.

We will use the MOD function in combination with the SUMPRODUCT function and ROW function for this example.

Our example data set is as follows:

Each level represents the total sales of three different shops in a city. We want to SUM every fourth row in our data set containing the total to reach a final value.

You can do that manually by selecting adjacent cells, but with an extensive data set, that is not a feasible option.

So, the formula we will use for this operation is

=SUMPRODUCT((MOD(ROW($B$2:$B$13)-1, 4)=0)*($B$2:$B$13))

Yeah, I know it looks scary 🥴

But don’t worry – we will go through each part of this formula in detail. And complete it step by step.

Let’s first discuss the SUMPRODUCT function.

The SUMPRODUCT function multiplies different ranges together and returns the SUM of the products as a result.

It is an array formula. And we will use its first argument =SUMPRODUCT (array1) to combine other functions.

Next comes the MOD function. The “(MOD” function contains two arguments, and the first one is the ROW function.

(ROW($B$2:$B$13) contains the cell references of the data set whose rows we want to total. Since we won’t have to change this, we will make it an absolute range.

The ROW function will return the ROW number of each row. And it acts as the number argument in the MOD formula (MOD(ROW($B$2:$B$13)-1.

Next, we add a -1 to the ROW function. This is because we want to total every fourth row in our data set. But the fourth row of the spreadsheet and the one containing the Sales total are different.

So to avoid confusion, we will take away one row by entering -1 in the formula. And Excel MOD function will then count each fourth row.

The second argument of the MOD function is “4”  – which is the divisor.

PRO TIP!

The =0 tells the function to return TRUE for the selected range and FALSE for others. In Excel, TRUE corresponds to 0, whereas FALSE equals 1. So we get an array of (1,0).

Next, we put the multiply sign *($B$2:$B$13) and use the Sales range again. We will use the range as an absolute reference here. The multiplication returns the product of the range.

Add the final bracket for the SUMPRODUCT and press Enter.

And it’s done 🥳

Excel returned the SUM of every fourth value of the data set.

That was pretty tough, no? 😅

Worry not, with a little practice, you should be able to master the MOD function and its usage with other functions.

That’s it – Now what?

In this article, you learned what Modulus is and how to use the MOD function in Excel. You also saw how to find the SUM of every nth row in Excel using MOD.

The MOD function is one of the most useful and resourceful functions in Microsoft Excel. It works great when used on its own. And becomes twice as powerful when combined with another function.

Just like MOD, Excel has many other similar functions. If you are expanding your Excel skills, we recommend you master the VLOOKUP, IF, and SUMIF functions. These Excel functions make an essential part of this giant spreadsheet software.

And you can learn these and many other fantastic functions for free in my 30-minute free email course. So register now! 🚀

Other resources

The MOD function is easy to use but often times we make mistakes in typing the formula. This leads to errors like #NAME, #Values, etc., in the Excel spreadsheet.

Learn why these errors occur and how you can remove them, here. Also read How to Highlight every other row, Highlight Cells with Conditional Formatting, and more.

Frequently asked questions