In this article, we will demonstrate 3 easy methods to obtain the remainder in Excel using the following dataset that contains numbers for division.
Method 1 – Using the MOD Function
We can carry out the modulo operation in Excel using the MOD function. The MOD function returns the remainder of a division operation as a result, unlike ordinary division.
The syntax of the MOD function is:
The divisor is the number by which the number argument is divided. The number argument can be either a cell reference or a number put directly into the function.
Steps:
- In cell D5, insert the following formula:
=MOD(B5,C5)B5 represents the Dividend and C5 indicates Divisor.
- Press Enter or Tab to get the remainder of the division in D5.
- AutoFill the rest of the cells by dragging the formula cell down to D9.
We have the rest of the remainders in column D.
Read More: [Fixed!] Excel MOD Function Not Working (3 Issues with Solutions)
Method 2 – Using the Formula Tab
Here, we will access the Formulas tab to apply the MOD function, which can be found under the category of Math & Trig functions.
Steps:
- Go to the Formulas tab and click the Math & Trig dropdown icon.
- From the context menu that opens, scroll down to select the MOD option.
The Functions Arguments dialog box will pop up.
- Click in the Number box and select cell B5 as the number.
- Click the Divider box and click on cell C5.
- Click OK.
We have the remainder in D5.
- Repeat this process for the other cells one by one to return all the desired remainders.
Read More: How to Get Remainder in Decimal in Excel (3 Suitable Ways)
Method 3 – Using Excel VBA
In Excel VBA, the Mod operator returns the remaining portion of a division. In this method, we will create a UserForm to design a division calculator that returns a remainder as an output. We can customize our UserForm as we wish and insert a command button to execute the division. We will use a simple VBA code to perform the division process.
Steps:
- Go to the Developer tab and click Visual Basic (or press F11) to open the Visual Basic window.
- Click Insert and then UserForm to create UserForm1.
- Customize your UserForm as you wish.
- Right-click on the customized command button.
- Select View Code to display a module box.
- In the module box, enter the following VBA code:
Private Sub CommandButton1_Click()
Dividend = UserForm1.TextBox1.Value
Divisor = UserForm1.TextBox2.Value
If Dividend & Divisor <> "" Then
Remainder = Dividend Mod Divisor
MsgBox "The Desired Remainder is " & Remainder, , "Get Remainder"
End If
End Sub
- Click the green Run button.
The input form will be displayed.
- Enter dividend and divisor numbers in the input boxes.
- Click Calculate.
We receive the remainder as output.
- Calculate each row one by one to get all the values.
Read More: Excel VBA: How to Divide Without Remainder (2 Easy Ways)
Download Practice Workbook
