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.

**Table of Contents**hide

**Download Practice Workbook**

You can download the Excel workbook that we’ve used to prepare 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:**

Argument | Required/Optional | Explanation |
---|---|---|

number |
Required |
Dividend or the number that has to be divided. |

divisor |
Required |
Integer number, by which you want to divide the dividend. |

**Return Parameter:**

**The remainder of a division.**

**9 Suitable Examples of Using MOD Function in Excel**

**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:

`=MOD(B5,C5)`

➤ 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.

**Read More:** **44 Mathematical Functions in Excel (Download Free PDF)**

**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:

`=IF(MOD(B5,2)=0,"Even","Odd")`

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

**Read More:** **51 Mostly Used Math and Trig Functions in Excel**

**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:

`=SUM((MOD(ROW(C5:C14),2)=1)*(D5:D14))`

➤ 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:

`=SUM((MOD(ROW(C5:C14),2)=0)*(D5:D14))`

➤ 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**

^{th}

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:

`=SUMPRODUCT((MOD(SEQUENCE(ROWS(B5:B23)),3)=0)*(B5:B23))`

➤ 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.

**Similar Readings**

**How to Use MMULT Function in Excel (6 Examples)****How to Use TRUNC Function in Excel (4 Examples)****VBA EXP Function in Excel (5 Examples)****How to Use ABS Function in Excel (9 Suitable Examples)****How to Use PRODUCT Function in Excel (With 9 Examples)**

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

^{th}

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:

`=SUMPRODUCT((MOD(COLUMN(B5:T5)-COLUMN(B5)+1,3)=0)*(B5:T5))`

➤ 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:

`=SUMPRODUCT((MOD(B5:B20,2)=1)*1)`

➤ 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:

`=MOD(ROW(C5:C14),2)=1`

➤ 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:

`=MOD(C5,1)=0`

➤ 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.

**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:

`=B5-(INT(B5/C5)*C5)`

➤ 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.

**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.