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 Excel Tips Excel Absolute Value
 

Excel Absolute Value

Karthikeyan Subburaman
Article byKarthikeyan Subburaman
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated August 16, 2023

Absolute Value in Excel

 

 

Absolute Value in Excel

Absolute Value in Excel is the positive form of any negative value, whether it is an integer number or a decimal number. It can be calculated using the ABS function, available under Math and Trig in the Insert function. To use the ABS function, go to the Insert function option from the Formula menu tab in Excel and select ABS function, or else we can directly select the ABS function by going into the edit mode of any cell. Then select those cells that contain negative values as per syntax. We will see; it would get converted into a positive absolute value.

Watch our Demo Courses and Videos

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

Absolute Formula in Excel

Below is the Absolute Formula in Excel :

Absolute Formula 1

The Absolute Value Formula in Excel has one argument:

  • Number – which is used to get the absolute value of the number.

Methods of Absolute Functions in Excel

In Microsoft Excel, the ABS function comes under the category of Math and Trigonometric, where we can find the Math and Trigonometric in the Formula menu; we will see how to use the ABS function by following the below steps.

  • First, go to the Formulas option.

Absolute Value in Excel step 1

  • In the formula menu, we can see the Math & Trig option.
  • Click on the Math & Trig option so that we will get the list of functions which is shown in the below screenshot.

Absolute Value in Excel step 2

  • Click on the first function called ABS.
  • We will get the ABS function dialogue box as shown below.

Absolute Value in Excel step 4

  • Select the negative number on the cell, as shown in the below screenshot.
  • Click OK
  • The given negative number will be returned as an absolute positive number.

Absolute Value in Excel step 5

How to Use Absolute Value in Excel?

Absolute Value in Excel is very simple and easy to use. Let’s understand the working of Excel Absolute Value with some examples.

You can download this Absolute Value Excel Template here – Absolute Value Excel Template

Excel Absolute Value – Example #1

In this example, we will see how to use the ABS function by following the steps below.

Consider the below example, which has some random positive and negative numbers.

A.V. Example 1-1

Now we will use the ABS function, which will return a positive number by following the procedure below.

  • Click on the Output column.
  • Use the ABS formula as =ABS(A2), as shown in the below screenshot.

A.V. Example 1-2

  • Press ENTER; we will get the output as follows.

A.V. Example 1-3

  • In the above screenshot, the first row contains a positive number, so there is no difference, and the ABS function returns the absolute value of the number.
  • Drag down the formula for all the cell
  • We will get the below output as follows.

A.V. Example 1-4

  • As we see in the second row, we have a negative number; using the ABS function, we have converted a negative number to a positive number, and the positive number remains unchanged.
  • At last, we can see the VALUE error because the ABS function will throw a VALUE error If the supplied argument is non-numeric.

Excel Absolute Value – Example #2

Using Addition in ABS function

In this example, we will see how the ABS function works using SUM. Consider the below example, which has product-wise sales figure for three months.

A.V. Example 2-1

The above screenshot shows that the grand total has been calculated using the SUM formula.

Let us use the ABS function to check for the final grand value by following the steps below.

  • First, click on cell C2.
  • Insert the ABS function formula as =ABS(B2) shown below.

A.V. Example 2-2

  • Click ENTER.
  • The absolute function checks for the number, and it will return the absolute number.
  • We will get the output as follows, as shown in the below screenshot.

A.V. Example 2-3

  • Drag down the formula for all the cells so that we will get the final output as follows.

Absolute Value Example 2-4

  • As we can see in the above screenshot, the ABS function returned the absolute number and converted the negative number to positive numbers.
  • We can notice that the grand total value has a huge difference after using the ABS function because SUM will add the positive numbers and subtract the negative numbers and give the end result.
  • Here ABS function converted all negative numbers to positive numbers, and we got the grand total with a positive number.
  • Do the same operation and apply the ABS function to the E and H column, and we will get the following result.

Absolute Value Example 2-5

In the above result, we can see the Grand total difference after applying the ABS function.

Excel Absolute Value – Example#3

Using Subtraction in the ABS function

In this example, we will see how the ABS function works in subtraction. Let’s consider the below example, which shows the month-wise sales data of each employee and the difference.

Absolute Value Example 3-1

We can notice that we got the difference minus sign figure for the salesperson MAXWELL and the salesperson SMITH in the above screenshot. We have used the subtraction formula as Jan sales value – Feb sales value, as shown in the below screenshot.

Absolute Value Example 3-2

Here we have used the formulation as =B4-C4 as we cannot subtract a lower value with a higher value. While doing sales analysis, this report will go wrong because of the negative figure, and also we can see that the Average also seems to be wrong due to the negative figure. To overcome these errors, we can use the ABS function by following the below steps.

  • Click on the D column cell named Difference.
  • Using the ABS function as =ABS (B3-C3) will check for the number and return the absolute value shown in the below screenshot.

Absolute Value Example 3-3

  • Click ENTER, and we will get the output as shown below.

Absolute Value Example 3-4

  • Drag down the formula for all the cells to get the final output as shown.

Absolute Value Example 3-5

  • This is the correct way of reporting that the ABS function converted the negative number to a positive number, which gives the exact report.

Absolute Value Example 3-6

In the above comparison result, we can see that before applying the ABS function, we got some different figures, which is an incorrect way of subtraction.

After applying the ABS function, we can observe that it returned the absolute value. Notably, there is a noticeable change in the Grand total. Initially, it was 735, but after applying the ABS function, the Grand total became 825.

We can notice the same difference in the Average, where it was 183.7 before and after applying the ABS function; we got an average of 206.25, the exact average of the sales report.

Things to Remember

  • The ABS function in Excel normally returns the absolute values. Which converts negative numbers to positive numbers
  • While using the ABS function, we can see that positive numbers are unaffected.
  • ABS function in Excel will throw a #VALUE! Error if the supplied argument is non-numeric.
  • While using the ABS function, ensure all arguments are numeric values.

Recommended Articles

This has been a guide to Absolute Value in Excel. Here we discuss how to use the Absolute Value in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. ABS Function in Excel
  2. Excel Absolute Reference
  3. Relative Reference in Excel
  4. Statistics in Excel

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 Absolute Value Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Absolute Value Excel Template

EDUCBA

डाउनलोड Absolute Value Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW