What Is the Excel MOD Function? (Plus Uses and Examples)

By Indeed Editorial Team

Published 30 March 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

There are many mathematical functions in Excel that you can use to perform calculations and display the resulting answers. The MOD function is one type of mathematical Excel function that provides a specific set of uses. Learning about this function may help you develop your knowledge of the functionalities of Excel. In this article, we discuss what the Excel MOD function is, describe how to use it, list its uses and give examples of using it.

What is the Excel MOD function?

The Excel MOD function is a built-in function that calculates the remainder after a division operation. This function requires you to reference two numeral inputs in different cells within Excel or enter numbers directly into the function. It can then display the resulting remainder in a different cell after dividing one number by the other. For example, if you enter '10' in cell A1 and '15' in cell A2 and write the statement '=MOD(A2, A1)', the resulting output is '5'. This is because 15/10 = 1 (with 5 remaining).

How to use the MOD function in Excel

Here's a list of steps you can follow to use the MOD function in Excel:

1. Enter numbers in at least two cells

To reference cells when using the MOD function, you can write at least two numbers within multiple cells in Excel. The MOD functions divide these numbers to display the resulting remainder, so this information is important to include for the function to work correctly. For example, you may enter '2' in cell A1 and '4' in cell B1.

2. Click the cell you want to enter the function

Next, you can click an empty cell that you want the resulting value of your MOD function to appear. This is the cell that shows the resulting remainder of a division operation after you create a MOD function. For example, if you want to show the remainder on cell C1, you can click on that cell before entering the function.

3. Access the function dialogue box

In Excel, the function dialogue box appears above the spreadsheet after the 'fx' symbol. You can also right-click on a cell and click 'insert function'. This is the area where you write the MOD function.

Related: What Computer Skills Are Employers Seeking (Plus Examples)

4. Write a MOD function

Next, you can write the function using the correct format. Each MOD function begins with the statement '=MOD', followed by a parenthetical statement. Inside the parenthesis, you can write the two values you want to divide to find the remainder. For example, you can write '=MOD(A1,B1)' to divide the number in cell A1 by the number in cell B1 and display the remainder.

Uses for the MOD function

Here's a description of various uses for the MOD function:

Displaying the remainder after a division operation using specific numbers

The MOD function can calculate and display the remainder after a division operation using discrete numbers. You can enter numbers directly into the function statement to use this functionality. For example, you can write '=MOD(5,2)' to receive the '1' as an output.

Displaying the remainder after a division operation using several pairs of numbers

The MOD function can calculate and display the remainder of several numbers at once. If you enter pairs of numbers into separate cells, the MOD function can then perform a series of division operations to produce the remainder. For example, if you enter '1' in cell A1, '2' in cell B1 and '3' in cell C1, you can then create a MOD function that uses these cells for its inputs. In this example, you can write '=MOD(A1, B1)', '=MOD(A2, B2)' and '=MOD(A3, C3)' in various cells to calculate and display multiple remainders.

Related: What Is a Computer Scientist? Duties, Skills and Education

Calculating decimal remainders

The MOD function can display a decimal remainder for division operations that result in decimal answers. For example, if you use the statement '=MOD(7.75,0.26)' you can calculate the decimal remainder after a division operation. The result would be '0.21'.

Returning times from dates

The MOD function is used to return time from a date. To do this, you enter a date and time in a cell then divide that date and time by 1. For example, if you write '10/01/2020 05:10:26' in cell A2 you can write '=MOD(A2,1)' in another cell to receive an output of '05:10:26'.

Counting or summing whole numbers only

You can use the SUMPRODUCT and MOD functions to count or sum whole number remainders in a dataset. For example, if you have a list of numbers that have both whole numbers and decimal remainders after division operations, you can use these functions to count the number of whole number decimals. To do this, you can use the MOD function to calculate remainders then use the SUMPRODUCT function to add all the remainders that are whole numbers.

Related: What Does a Computer Programmer Do? (With Career Steps)

Highlight multiples of a specific value

You can use the MOD function and conditional formatting to highlight values that are multiples of a specific value. To do this, you can divide the number of items in the cells by the value that you want to use, then apply conditional formatting to display cells containing the remainder. For example, if you have a list of numbers with decimal remainders, you can find all the remainders that are multiples of 0.5 and apply conditional formatting to them.

Related: How to Become Computer Literate and Improve Your Skills

Sorting data based on remainders

The MOD function can sort data based on remainders. If you have a list of numbers with remains, you can use the MOD function to calculate remainders then use the COUNTA and COUNTBLANK functions to display their values in a specific order.

Calculating percentages

The MOD function is useful to calculate percentages. If you have a list of numbers with decimal remainders, you can divide these numbers by 100 to arrive at the percentage of remainders that are whole numbers. This is beneficial if you need percentile answers for your Excel spreadsheet.

Related: What Can You Do With a Mathematics Degree? (Plus Salaries)

Counting cells that contain odd numbers

If you want to find the number of cells that contain odd numbers in a specific range, you can use the MOD function and the COUNTIF function. To do this, you use MOD to calculate remainders, then use COUNTIF to count any remainders that aren't divisible by two. This is because all odd numbers aren't divisible by two.

Displaying a remainder if the function meets specific conditional criteria

You can use the MOD function to calculate and display a remainder if the function meets specific criteria by combining an IF function with a MOD function. For example, you can nest a MOD function within an IF function that specifies to only calculate the remainder if the number in cell A1 is greater than 10. If the number in cell A1 is greater than 10, the MOD function displays the remainder. If the number is less than 10, the cell with the conditional MOD function remains blank.

Examples of using the function

Here are several examples of using the MOD function with tables to show the examples in the Excel format:

Example of calculating whole number remainders

Here's an example of using a MOD function, '=MOD(B1,A1)', in cell C1 to calculate a whole number remainder:

ABC**120855### Example of calculating decimal remainders

Here's an example of using a MOD function, '=MOD(A1,B1)', in cell C1 to calculate a decimal remainder:

ABC**19.50.2**0.1### Example of printing the output of the function in 3 separate cells

Here's an example of using a MOD function, '=MOD(A1,B1)', in cells C1, C2 and C3 to calculate a remainder and map it in different cells:

ABC15**21
2

1**3

1### Example of calculating time from a datetime

Here's an example of using a MOD function, '=MOD(A1,1)', in cell C1 to calculate time:

ABC101/02/22 08:20:33
08:20:33### Example of returning the remainder with two pairs of numbers

Here's an example of using MOD functions,'=MOD(A1,B1)' and '=MOD(A2,B2)', in cells C1 and C2 to calculate remainders of two pairs of numbers:

ABC11**10213103Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.

Explore more articles