How to Use MOD Function in Excel (9 Suitable Examples)

In Microsoft Excel, the MOD function is widely used to extract the data from the series of n^th rows or columns for calculation and other purposes. In this article, you’ll get to learn how to use this MOD function efficiently in Excel.

The above screenshot is an overview of the article, representing an application of the MOD function in Excel. You’ll learn more about the dataset as well as the methods to use the MOD function properly in the following sections of this article.

Introduction to the MOD Function

• Function Objective:

Returns the remainder after a number is divided by a divisor.

• Syntax:

=MOD(number, divisor)

• Arguments Explanation:

• Return Parameter:

The remainder of a division.

How to Use MOD Function in Excel: 9 Suitable Examples

1. Using MOD Function to Find the Remainder

In the first section of the uses of the MOD function in Excel, we’ll find out the remainder from a division. In the picture below, there are 3 columns with dividends, divisors and remainders. We’ll find out the remainders in Column C.

📌 Steps:

➤ In Cell D5, type:

➤ Press Enter, autofill the rest of the cells in Column D and you’ll get all the remainders at once.

There are some facts to notice with the MOD function while determining the remainders. The remainder will always follow the +/- sign of divisor and it doesn’t matter if a dividend is positive or negative.

Another important fact is if you divide a number with a decimal then the function will return a wrong output which is illustrated in Row 9 as the value 32 is divisible with 1.6 and the remainder should have been 0 but here the function has returned the value 1.6. So this function is useful only with the integer divisors and you cannot expect the function to return an accurate +/- sign either.

2. Determining Odd or Even Number with MOD Function

By combining IF and MOD functions together, we can determine odd or even numbers so easily.  There are some numbers in Column B, and in Column C, we’ll show the messages which numbers are odd and which ones are even.

📌 Steps:

➤ In Cell C5, we have to type:

➤ Press Enter, autofill the entire column with Fill Handle and you’re done.

3. Performing Calculation on Only Odd Rows with MOD Function

The picture below represents the marks of a student’s exams in two terms which have been defined by Test 1 and Test 2. Test 1 results are lying in odd rows and Text 2 results in even rows. Let’s assume, we want to know the marks of Test 1 for all subjects.

📌 Steps:

➤ In the output Cell D16, the related formula will be:

➤ Press Enter and the function will return the total marks for all subjects in Test 1.

🔎 How Does This Formula Work?

➤ ROW function extracts all the row numbers of the range of cells C5:C14 and will return an array of:

{5;6;7;8;9;10;11;12;13;14}

➤ MOD function finds out only odd-numbered rows from these values and will return:

{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}

➤ Finally, the SUM function sums all the values by following the logical value- TRUE only and ignores another logical value- FALSE.

4. Performing Calculation on Only Even Rows with MOD Function

Now to find the total marks of Test 2, we have to calculate the data for even rows only. The procedure is the same as the previous method but here as we’re extracting the even rows only, so in the MOD function, the remainder will be 0 here to find out the even-numbered rows.

📌 Steps:

➤ In Cell D16, the related formula will be:

➤ After pressing Enter, you’ll get the calculated sum for all subjects in Test 2.

5. Carrying out Calculation on Every n^th Row with MOD Function

If we have to perform calculations for every n^th row in a column, then we have to combine SUMPRODUCT, MOD, SEQUENCE and ROWS functions together. In the picture below, Column B contains a number of integer values. We want to make a sum of every 3rd row from that column.

📌 Steps:

➤ In the output Cell E13, the related formula will be:

➤ Press Enter and the resultant value will be shown right away.

🔎 How Does This Formula Work?

➤ Here, the ROWS function returns the total number of rows- 19 from the range of cells B5:B23.

➤ SEQUENCE function returns the serial of 19 numbers starting from 1 and the array will look like this:

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}

➤ MOD function then divides all these 19 numbers with 3 and will return the logical value- TRUE for all divisions where no remainder was found. So, the return values from this step will be:

{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}

➤ Finally, the SUMPRODUCT function extracts data from the specified rows where the logical value TRUE is assigned and then returns the sum of those values.

6. Carrying Out Calculation on Every n^th Column with MOD Function

In the case of performing calculations on every n^th column, we cannot use SEQUENCE and ROWS functions here. Rather we have to use COLUMN functions inside the MOD function now. For example, in Row 5 in the picture below, there are some integer values and we’re going to determine the sum of every 3rd column in that row.

📌 Steps:

➤ In our output Cell E8, the formula will be:

➤ After pressing Enter, you’ll get the resultant value as 172.

7. Counting Cells Containing Odd or Even Numbers with SUMPRODUCT and MOD Functions

By combining SUMPRODUCT and MOD functions, we can count the cells containing odd and even numbers. In the picture below, Column B lies with several integer values and in Cells D10 and D13, we’ll determine the total counts of odd and even numbers respectively.

📌 Steps:

➤ In Cell D10, you have to type:

➤ Press Enter and you’ll get the total count of odd numbers in Column B.

➤ Now copy the formula, replace 1 with 0 for the MOD function to find out the even numbers, and input the modified formula in Cell D13.

➤ Press Enter and you’ll get the total count for even numbers as well right away.

8. Using MOD Function to Highlight Cells

We can use the MOD function to highlight specific rows or columns in Excel spreadsheets. For example, in the following picture, we want to highlight the marks of all subjects for Test 1 only. As the marks of Test 1 are lying in odd-numbered rows so we have to use the MOD function while formatting to specify those rows.

📌 Step 1:

➤ Select the range of cells C5:D14 first.

➤ Under the Home tab, select the More Rules command from the Conditional Formatting and Highlight Cell Rules drop-downs in the Styles group of commands.

📌 Step 2:

➤ In the New Formatting Rule dialogue box, select the “Use a formula to determine which cells to format” from Rule Type options.

➤ Go to the Rule Description box and type the following formula there:

➤ Press Format option. Another dialogue box will appear.

📌 Step 3:

➤ From the Fill tab, choose any color you want to highlight the cells

➤ Press OK and you’ll be brought to the previous dialogue box again.

📌 Step 4:

➤ Now you’ll be shown a preview of the highlighted cell in the New Formatting Rule dialogue box. Press OK and you’re done.

Like the picture below, you’ll find your highlighted rows with the selected color.

9. Data Validation with MOD Function in Excel

Data validation in Excel is very important when you have to input accurate data or values in the proper format. If you give a wrong input then the cell will not accept that value and will show an error message. In the picture below, we have to input the heights in centimeters in Column C and the input data will accept integer values only. If you input a decimal value, an error message will be shown. So we’ll now learn how we can assign these criteria for Column C.

📌 Step 1:

➤ Select Cell C5 first.

➤ Under the Data ribbon, choose the Data Validation command from the Data Tools drop-down. A dialogue box named Data Validation will appear.

📌 Step 2:

➤ Under the Settings tab, select Custom in the Allow box for Validation criteria.

➤ In the formula box, type:

➤ Press OK.

📌 Step 3:

➤ Use Fill Handle from Cell C5 to copy the cell format into the next cell C6.

➤ Enter a height in integer value in C5 and you’ll be shown no error message as you’ve just put an integer value.

📌 Step 4:

➤ Now try to input a height in decimal value in Cell C6 and you’ll be shown an error message.

So, this is how you can assign a fixed format of a data entry in a certain range of cells.

Read More: [Fixed!] Excel MOD Function Not Working

Alternative to the MOD Function to Find the Remainder

We have an alternative to the MOD function which is basically a customized algebraic formula to find the remainder from a division. In the picture below, we’ll find the remainders in Column D based on the dividends and divisors from the other two columns.

📌 Steps:

➤ In the output Cell D5, the related formula will be:

➤ After pressing Enter and auto-filling the entire column with the Fill Handle, you’ll find all the remainders at once.

💡 Things to Keep in Mind

🔺 MOD function always returns a similar +/- sign as the divisor.

🔺 The divisor must be an integer value in the MOD function otherwise, you won’t get the accurate value of the remainder.

🔺 If the divisor in the MOD function is 0, the function will return a #DIV/0 error.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Concluding Words

I hope all of the methods mentioned above to use the MOD function will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

Excel MOD Function: Knowledge Hub

• How to Divide Without Remainder in Excel
• How to Get Remainder in Decimal in Excel
• How to Separate Quotient And Remainder in Excel

<< Go Back to Excel Functions | Learn Excel