ABS and MOD in Excel - Step-by-Step Guide

Introduction

ABS and MOD are two simple Excel functions that help you work with numbers. ABS gives you the positive value of a number, even if it is negative. MOD gives you the remainder when you divide one number by another. These functions are useful for everyday math tasks like checking distances or splitting things evenly.

When you want to find the distance between two numbers without worrying about negative signs, like temperature changes.

When you need to split items into groups and want to know how many are left over, like dividing candies among friends.

When you want to check if a number is even or odd by using the remainder after dividing by 2.

When you want to clean up data by converting all negative numbers to positive values.

When you want to cycle through a list repeatedly by using the remainder to wrap around.

Steps

Step 1: Click

- a blank cell where you want the result

The cell is selected and ready for input

Step 2: Type

- the formula bar

You start entering the formula

💡 Start with =ABS(number) to get the absolute value

Step 3: Enter

- the formula =ABS(-5)

The cell shows 5, the positive value of -5

Step 4: Click

- another blank cell

The cell is selected for the next formula

Step 5: Type

- the formula bar

You start entering the MOD formula

💡 Use =MOD(number, divisor) to find the remainder

Step 6: Enter

- the formula =MOD(10, 3)

The cell shows 1, which is the remainder of 10 divided by 3

Before vs After

Before

Cell A1 contains -7, Cell A2 is empty

After

Cell A2 contains 7 after entering =ABS(A1)

Settings Reference

ABS function

📍 Formula bar or Insert Function dialog

Returns the positive value of the number

Default: No default, you must provide a number

MOD function

📍 Formula bar or Insert Function dialog

Returns the remainder after division

Default: No default, you must provide both number and divisor

Common Mistakes

Using MOD with zero as divisor like =MOD(5,0)

Division by zero is not allowed and causes an error

Always use a divisor not equal to zero, for example =MOD(5,2)

Expecting ABS to change the original cell value

ABS only returns the positive value in the cell where the formula is entered

Use ABS in a new cell to see the positive value without changing original data

Summary

ABS returns the positive value of any number, removing negative signs.

MOD returns the remainder after dividing one number by another.

Use ABS and MOD formulas in cells by typing =ABS(number) or =MOD(number, divisor).