1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Excel MOD Function

Find the Remainder when Dividing Numbers in Excel


Excel MOD Function

Excel MOD Function

© Ted French

MOD Function Overview

The MOD function, short for modulo or modulus can be used to divide numbers in Excel.

Unlike regular division, however, the MOD function only gives you the remainder as an answer.

Uses for this function in Excel include combining it with conditional formatting to produce alternate row and column shading, which makes it easier to read large blocks of data.

MOD Function Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the MOD function is:

= MOD ( Number , Divisor )

Number - (required) the number being divided

Divisor - (required) the number by which you want to divide the Number argument

The Number argument can be a number entered directly into the function or a cell reference to the location of the data in a worksheet.

Note: the MOD function will return the #DIV/0! error value for the following conditions:

  • if a zero "0" is entered for the Divisor argument
  • if a cell reference to a blank cell is entered for the Divisor argument

Example: Using Excel's MOD Function

  1. Enter the following data into the cells indicated:
    D1: 5
    D2: 2
  2. Click on cell E1 - the location where the results will be displayed

  3. Click on the Formulas tab of the ribbon

  4. Choose Math & Trig from the ribbon to open the function drop down list

  5. Click on MOD in the list to bring up the function's dialog box

  6. In the dialog box, click on the Number line

  7. Click on cell D1 on the worksheet

  8. In the dialog box, click on the Divisor line

  9. Click on cell D2 on the spreadsheet

  10. Click OK in the dialog box

  11. The answer 1 should appear in cell E1 since 5 / 2 leaves a remainder of 1

  12. When you click on cell E1 the complete function = MOD ( D1 , D2 ) appears in the formula bar above the worksheet

Since the MOD function only returns the remainder, the integer portion of the division operation (2) is not displayed. To show the integer as part of the answer, you can use the QUOTIENT function.

©2014 About.com. All rights reserved.