Pinterest Pixel

How to get Remainder in Excel using MOD Formula

Bryan
In Excel, it is very easy to divide two numbers.
But how about if you need to get the remainder from a division operation? Thankfully we can do the above complex and manual calculation with ease using Excel's MOD formula!
How to get Remainder in Excel using MOD Formula | MyExcelOnline How to get Remainder in Excel using MOD Formula | MyExcelOnline

Key Takeaways

 

MOD Function – Syntax & Basic Use

What does it do?

Gives you the remainder after dividing one number with another

Formula breakdown:

=MOD(number1, number2)

What it means:

  • number1 – A number whose remainder you wish to find.
  • number2 – A number to divide with.

For example, divide 13 by 4:

  • Divide the two numbers (i.e. 13 / 4)
  • Get the quotient (which is 3)
  • Multiply it back to the divisor (3 * 4)
  • Subtract it from the original number (13 – 12)
  • And I have the remainder! (1)

So, 4 goes into 13 three times with a remainder of 1.

So, MOD(13,3) returns 1 as when you divide 13 by 3 will give a quotient of 4 and leaves a remainder of 1.

Let’s look at a few examples to know how to get remainder in Excel using the MOD formula.

Follow the step-by-step tutorial on How to get Remainder in Excel using MOD Formula and make sure to download Excel Workbook to follow along:

Download excel workbookMOD-FORMULA.xlsx

 

STEP 1: We need to enter the MOD function:

=MOD(

How to get Remainder in Excel using MOD Formula

 

STEP 2: The MOD arguments:

number1

What is the first number that we plan to divide?

Reference the cell that contains the first number:

=MOD(C9,

How to get Remainder in Excel using MOD Formula

number2

What is the divisor?

Reference the cell that contains the second number:

=MOD(C9, D9)

How to get Remainder in Excel using MOD Formula

STEP 3: Do the same for the rest of the cells by dragging the MOD formula all the way down using the left mouse button.

Now you are able to get the remainders of all the division operations!

How to get Remainder in Excel using MOD Formula

Now that you have learned how to use the MOD function to find the remainder. Let’s move on to some practical uses of this function.

 

Use MOD function to Highlight Cells

You can use the MOD function to conditionally format the cells. Say, you want to highlight every 4th row in your data.

Let’s see the data table below:

How to get Remainder in Excel using MOD Formula

 

Let’s follow the step-by-step tutorial below to highlight every 4th row of this data table:

STEP 1: Select the entire data table.

How to get Remainder in Excel using MOD Formula

STEP 2: Go to Home > Conditional Formatting > New Rule.

How to get Remainder in Excel using MOD Formula

STEP 2: In the New Formatting dialog box, select Use a formula to determine which cells to format from the list.

How to get Remainder in Excel using MOD Formula

STEP 4: Type the formula

=MOD(ROW(),4)=0

under Format values where this formula is true text box, and then click on Format button to apply custom formatting to these rows.

How to get Remainder in Excel using MOD Formula

Using this formula you can add formatting to every 4th row of the data. You can change the number to 2,3,5… as per your need.

STEP 5: In the Format Cells dialog box, Go to Fill Tab, and select an appropriate color. Click OK.

How to get Remainder in Excel using MOD Formula

STEP 6: Here you can see a preview of how your formatted data will look like. If you are satisfied with it, click OK.

How to get Remainder in Excel using MOD Formula

 

Your conditionally formatted data table using the MOD formula is now ready!

How to get Remainder in Excel using MOD Formula

To highlight, the nth column you can simply replace the ROW formula with the COLUMN formula.

This conditional formatting is dynamic, i.e. once you add or delete more rows the formatting will update automatically.

As you have seen, you can use the MOD function to highlight the nth row or column of your data. You can also use it to sum every nth row/column.

 

Use MOD function to Calculate cells

In the data below, you have sales data for different products & different regions.

How to get Remainder in Excel using MOD Formula

You can use the MOD function, to sum up, every alternate row in the data to get the total sales of the North and South regions separately.

 

The formula for the sum of every even row (North Region Sales) :

=SUMPRODUCT(- -(MOD(ROW(range),2)=0),range)

The formula for the sum of every odd row (South Region Sales) :

=SUMPRODUCT(- -(MOD(ROW(range),2)=1),range)

Here,

  • The ROW function returns the row number of the cell.
  • MOD function when divides an even number leaves remainder as 0 and when divides an odd number leaves remainder as 1.
  • Double dashes (- -) outside the MOD function converts the FALSE and TRUE into 0s and 1s.
  • Lastly, SUMPRODUCT function multiplies the sum of the product of range containing 0s and 1s and range containing sales amount.

 

The formula for calculating total sales in North Region i.e., the summation of all even rows (Row 2, Row 4, Row 6 & Row 8) will be:

How to get Remainder in Excel using MOD Formula

 

The formula for calculating total sales in South Region i.e., the summation of all odd rows (Row 3, Row 5, Row 7 & Row 9)  will be:

How to get Remainder in Excel using MOD Formula

 

Conclusion

In this tutorial, you have learned not only how to get remainder in Excel but also how to use the MOD function to highlight and calculate cells.

If you like this Excel tip, please share it
How to get Remainder in Excel using MOD Formula | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  How to Use RegexReplace in Excel

Steps To Follow

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...