If you need to convert negative numbers to positives, or positives to negatives, we have provided different methods that will allow you to do both below.
There are a number of ways to change a positive number to a negative number in Google sheets. You can use the ABS Function, Find and Replace, Multiply by -1, the UMINUS function, an Add-On, or Apps Script.
Now let's take a look at each method in turn.
Method 1: ABS Function
The easiest way to convert a negative number to positive is by using the ABS function. This function returns the absolute value of a number, meaning it will always return a positive number regardless of whether the original number was negative or positive.
To use the ABS function, simply enter the following formula into a cell:
=ABS(number)
Replace "number" with the cell reference or value that you want to convert. For example:
=ABS(-2342) would return 2342
=ABS(B2) would return the absolute value of whatever is in cell B2
Note: The absolute value of positive numbers is just the number itself. e.g ABS(5) = 5 and ABS(100) =100
It's a good tool to have in your repertoire, especially when you are frequently converting numbers.
Method 2: Multiply by -1
Another way to convert negative numbers to positive is to multiply them by -1. This will change the sign of the number, effectively making a negative number positive (and vice versa).
To do this, simply enter the following formula into a cell:
=number*-1
Replace "number" with the cell reference or value that you want to convert. For example:
=-1*-5 would return 5
=-1*B2 would return the opposite of whatever is in cell B2
Multiplying by minus one will also convert positive numbers to negative ones, e.g. -1*3 = 3. When we multiply zero by minus one it will remain as zero.
Method 3: Find and Replace
You can also use the Find and Replace tool to convert negative numbers to positive ones. To do this, first select the cells that you want to convert. Then, go to Edit > Find and replace which will open up a dialog box.
In the "Find" field, type a minus sign (-). Then, in the "Replace with" field, leave it blank. This will remove all minus signs from your selected cells, and by doing so convert any negative numbers to positive ones.
Click "Replace all" to make the changes, and then close the dialog box or press done.
Method 4: UMINUS Function
The uminus function in Google Sheets is used to change the sign of a number. For example, if you have a number that is positive, the uminus function will make it negative and when applied to a number that is negative, it will make the number positive. The Uminus function can only be used on numbers, if a text value is input, it will return a VALUE error.
To use the UMINUS function, enter the following formula into a cell:
=UMINUS(number)
Replace "number" with the value that you want to convert. For example:
=UMINUS(-5) would return 5
The Uminus function is great for quickly changing the sign of a number without having to re-type the entire value. This function can be especially useful when working with large numbers or when you need to alternate between positive and negative values on a daily basis.
Method 5: Add-On
If you need to convert a large number of cells from negative to positive (or vice versa), you may find it easier to use an Add-On. There are several Add-Ons available that can perform this task, but in this example we will use the "Power Tools" Add-On.
To use this Add-On, first select the cells that you want to change. Then, go to Add-ons > Get add-ons and search for "Power Tools". Install the Add-On and it will request certain permissions.
Once the Add-On is installed, go to Add-ons > Change Sign > Change Sign.
You will this be selected with several options to convert, text to numbers, text to dates and convert number sign.
Select convert negative numbers to positive or reverse all number signs depending on which result you would like and press run.
And there you have it! These are some of the most effective ways to convert negative numbers to positive in Google And there you have it! These are some of the most effective ways to convert negative and positive numbers in Google Sheets. We have looked at absolute values, finding and replacing the negative sign and a few other ways to ensure all the values in the spreadsheet can be converted. Converting negative numbers to positive should now be easy! If you have any questions, feel free to leave a comment below..
N.B. If you're comfortable with coding, you can also use Apps Script to convert negative numbers to positive ones. However, with a number of other readily available methods to do it, this may be unnecessary depending on your use case. It really depends whether you want to invest the time into writing a custom script.
