Math masters will describe an absolute value as to how far a number is from zero. That makes sense, because the number “5” is just as far from zero as -5. Both numbers are five away from being zero. Therefore, the absolute value of both 5 and -5 is… 5! Occasionally, you’ll need this function in Microsoft Excel. Let’s learn how to find absolute value in Excel.
How to Find Absolute Value in Excel
We’re going to use the =ABS() function in Excel to convert a cell to its absolute value.
Before we try it in a spreadsheet, try to simply type an absolute value formula in Excel’s formula bar. Click in a cell, and then try this out:
=ABS(-10)
Notice in the example screenshot below that Excel returns the absolute value of -10, which is 10.
This is a simple example, but it shows you how the formula works. Now, let’s apply absolute value to a cell with the function. We’ll type =ABS(F2) to find the absolute value of the cell F2.
Use the absolute value function instead of re-typing data.
When to Use Absolute Value in Excel
As you can see, it’s easy to apply the absolute value function in Microsoft Excel. So, what are some scenarios that you might find this useful in?
It’s important to make your spreadsheet as readable as possible. Let’s say that we have a column that’s labeled “Cost to remove.” Maybe you’re coaching a client on how much cost they need to take out of their business to improve profitability. Let’s look at an example below.
When your client sees this spreadsheet, it might be confusing. After all, if the column says “cost to remove” and you’re giving them a negative number, does that mean they actually need to add more cost? Certainly not, but without clear labeling, it’s easy to draw the wrong conclusion.
Let’s apply =ABS() to the column:
Now, the analysis is much more clear. The savings targets are clear for our business owner without any confusion.
Instead of constantly flipping signs in Microsoft Excel, use absolute value and the =ABS function to automate the work for you.
Spell Check in Excel