EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Maths Function in Excel ABS Function in Excel
 

ABS Function in Excel

Jeevan A Y
Article byJeevan A Y
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated May 6, 2023

ABS in Excel

 

 

ABS Function (Table of Contents)

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

  • ABS in Excel
  • How to Use ABS Function in Excel?

ABS in Excel

The ABS Function in Excel converts any negative number into a positive value. It is one such function we can use in any mathematical operation with some expectation of getting a negative value. The values obtained from the ABS function are the absolute values just by removing the minus (-) sign from the calculated value; for this, we need to apply the ABS function in the value or put the formula inside the ABS.

ABS Formula in Excel:

Below is the ABS Formula.

ABS Formula

The Formula of the ABS function is very easy, and that includes only one parameter, i.e. number.

Number: This is the required parameter. The number you wish to get is the absolute number without its sign.

For this, the function we can give cell reference, we can enter the number directly, and we can enter the number in double quotes.

How to Use the ABS Function in Excel?

This ABS function is very simple and easy to use. Let us now see how to use the ABS Function with the help of some examples.

You can download this ABS Function in Excel Template here – ABS Function in Excel Template

Example #1

From the list of numbers, convert all the negative numbers to positive numbers using the ABS function.

Example 1-1

Apply the ABS function in Excel to get the absolute numbers without its sign.

ABS Example 1-2

The result will be:

ABS Example 1-3

If you look at the positive numbers, it returns the same value. There are no signs of positive numbers, so it returns the number as it is.

Example #2

From the above example, we have learned how to convert negative to positive numbers. Now take the same example, but the thing is if the number is negative, convert it to positive and if it is not negative, show it as a positive number.

This is done by using the IF condition. IF condition is checking whether the given number is less than zero or not (If (A<0,). If the number is less than zero

Example 2-1

Then it converts the negative number to a positive number by using the ABS function (ABS (A2))

ABS Example 2-2

And if the number is not negative.

ABS Example 2-3

Then the result will be shown as a “Positive Number”.

ABS Example 2-4

Example #3

From the below table, calculate the target vs actual report for the sales team. You are provided with the target and actual achieved number. You need to calculate the variance and calculate the variance percentage.

Example 3-1

In order to calculate the variance, we can use the formula as = Actual – Target.

ABS Example 3-2

This would give us the variance value.

ABS Example 3-3

In order to get the variance % value, we use the formula =Variance / Target *100.

ABS Example 3-4

This will be given us a variance % value.

ABS Example 3-5

The problem with this generic formula is that we always get negative numbers if the actual value is less than the targeted value, affecting our variance percentage. Therefore, to eliminate these negative numbers, we can use the ABS function to get the absolute values.

ABS Example 3-6

I just applied the ABS function before I do the calculation of Actual – Target. The result is converted to the absolute value by ABS.

ABS Example 3-7

One more interesting thing here is as soon as we get a positive variance value, the variance percentage is automatically converted to positive percentages.

Example #4

Without using ABS, we can convert it into a positive number also. These are smart tricks for using Excel.

  • Convert negative numbers by multiplying them with -1.
  • Use Paste Special as an option.

Convert Negative Numbers by Multiplying with -1.

The basis of mathematics lies in understanding the signs of multiplication and those signs of multiplication are as follows.

Example 4-1

Since we are trying to convert negative numbers to positive ones, we can use the last method, i.e. Minus * Minus = Plus

By using the above method, convert the below numbers to absolute numbers.

ABS Example 4-2

Multiply all these numbers by -1 to get the positive values.

ABS Example 4-3

The result will be :

ABS Example 4-4

Use Paste Special to Convert Negative Numbers to Positive Numbers.

Enter -1 in one cell and follow the below steps.

Convert Negative Numbers to Positive Numbers

Step 1: Copy the value -1, which we have entered in cell D3.

Copy the value -1

Step 2: Now select all the negative values.

ABS Example 4-7

Step 3: Open and paste the special dialogue box using ALT + E + S and select the Multiply option.

Select Multiply option Example 4-8

Step 4: Now press ok. All the negative values converted to positive values in the same range, and the result will be the same as the below image.

 ABS Example 4-9

Things to Remember

  • ABS can accept only numeric values. Anything other than the numeric value, the error will be #VALUE!
  • No changes will happen to positive numbers. Only negative values are converted to positive values.
  • VBA code to ABS function is WorksheetFucntion.ABS (-650).

Sub ABS_Example

Dim MyValue as Double

MyValue = Application.WorkSheetFunction.ABS(-650)

Msgbox MyValue

End Sub

Recommended Articles

This has been a guide to ABS Function. Here we discuss the ABS Formula and how to use the ABS Function along with practical examples and downloadable Excel templates. You can also go through our other suggested articles –

  1. MID Function in Excel
  2. POWER Function in Excel
  3. EVEN Excel Function
  4. FLOOR Excel Function

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download ABS Function in Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download ABS Function in Excel Template

EDUCBA

डाउनलोड ABS Function in Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW