Skip to Content

How to Get the Absolute Value in Google Sheets

Sometimes in spreadsheet programs like Google Sheets or Microsoft Excel, you need to get the absolute value of a number.

Fortunately this a relatively easy thing to do. There are a few different ways to do this in Google Sheets.

In this tutorial, I will show a couple of different ways to get the absolute value of a number in Google Sheets.

What is Absolute Value?

The absolute value of a number is the magnitude of a number, regardless of whether the number is positive or negative.

In other words, it is its distance from zero.

For example, the absolute value of -10 and 10 are both equal to 10. Because both of them are an equal distance from zero.

ABS Function

The best way to return the absolute value for values in your spreadsheet is to use the ABS function.

The ABS function will return the absolute value of a number.

The syntax of this function is:

=ABS(value)

  • value – this is the number for which to return the absolute value. It can be a cell reference or a value put into the formula itself. You can also place math operations inside the formula to find the absolute value of the returned value.

Here is an example of this can be used in a spreadsheet:

ABS function

You can see in the example above, with only one argument, this function is very easy to use to convert any number in your sheet into its absolute value.

Convert Negative Numbers to Positive Numbers

The ABS function is definitely the preferred method of converting negative numbers to positive numbers, particularly if you a large dataset that you need to perform this operation on.

There are definitely other ways to convert negative numbers to positive numbers in Google Sheets though.

One simple method is just to add a minus sign (-) before the cell reference of the cell containing your negative value.

Here is an example of this is done:

Negative to Positive

You can see in the example above, in column B, I have a formula with cell references in column A. I have placed a minus sign (-) before each cell reference. This converts the number into its opposite value. So negative numbers convert to positive numbers.

The drawback with this method is that it is more prone to mistakes than the previous method. If you do this on a positive number it will convert it to a negative, so you need to be careful with this option.

Closing Thoughts

Finding the absolute value of a number is a relatively easy task in Google Sheets. I have shown two different methods for this.

Try using both methods in your own sheets so that you feel comfortable with using them.

More Google Sheets Tutorials:
How to Convert Formulas to Values