This post is going to show you all the ways you can change negative numbers into positive numbers in Google Sheets.

Perhaps you have a price list with negative numbers in it. Since negative prices are obviously an error, you may need to fix them by changing the number sign to positive.

There are many situations where negative numbers can pop up in the wrong place, and they can pose serious challenges to your analysis.

Fortunately, there are various tricks you can use to manage to convert the number sign.

In this post, you will see several easy methods with which you can change negative numbers to positive numbers in Google Sheets.

• Using Find and Replace to change the number sign.
• Using multiplication to change the number sign.
• Using the ABS function to change the number sign.
• Using the UMINUS function to change the number sign.
• Using an Add-On to change the number sign.
• Using an Apps Script to change the number sign.

Get your copy of the example workbook with the above link to follow along.

Convert Negative to Positive with Find and Replace

Sometimes, clever use of an in-built feature might be all you need to solve a problem. It turns out you can remove any minus signs using the Find and replace feature.

From the sample data, cells B2, B3, and B7 in the Price column have negative prices. We want to rectify this using the Find and replace feature.

You can access the Find and replace feature in the Edit menu tab options or by using the keyboard shortcut Ctrl + H.

Whatever route you choose, you will end up with this dialogue box on your spreadsheet screen.

There are a few menus you need to set up for the Find and replace to take action. Follow the steps below to remove negative numbers from the column.

1. Insert the dash - into the Find option.
2. Leave the Replace with box blank.
3. Choose Specific range in the Search option, and use the grid or table icon to select the range which you want to replace values from.
4. Press the Replace all button.

Selecting the range of cells before opening the dialogue box will automatically activate the options from step 3.

The negative sign in front of the numbers will be removed. The notification tells you how many replacements were made.

In this case, because no values were entered in the Replace with option, the minus signs were simply removed.

Click on the Done button to exit the dialogue box.

Now that the negative sign has been removed, the numbers are now left-aligned. This usually means something has changed in terms of the character’s data type.

When trying to sum the numbers, you can see there’s something wrong.

After carrying out the Find and replace operation, the data type of the cells involved has changed to text.

That is why they appear left-aligned, by default text strings are left-aligned. The SUM operation adds only the numeric values in the range and ignores any strings.

This is one reason why you shouldn’t alter the alignment of your cells. It will help you easily spot numbers that are actually a text data type.

In this example, you can convert the values back into numbers by performing another find and replace to replace the $ with a blank value.

Convert Negative to Positive by Multiplication

This method will use a basic multiplication principle in mathematics. The multiplication of two negative numbers returns a positive number.

When you multiply cells by -1, you can convert negative numbers to positive.

= MULTIPLY ( B2, -1 )

Copy and paste the above formula into an empty cell, and you get the result as displayed above.

= B2 * -1

You can also use the more simple syntax like the above formula.

You will see that there are still some negative numbers in the result. That is because the multiplication of a negative and positive number returns a negative value.

All the former positive numbers are turned into negative numbers with this method. Make sure you take note of this.

Change Negative to Positive with the UMINUS Function

The UMINUS function converts negative numbers to positive numbers and positive numbers to negative numbers.

It produces the same result as the multiplication method.

= UMINUS ( value )

• value refers to the content or cell whose sign you want to change.

= UMINUS ( B2 )

Copy and paste the above formula into an empty cell.

Notice that the results are similar to those from the multiplication method. Consequently, they suffer the same drawbacks.

Convert Negative to Positive with the ABS Function

The ABS function returns the absolute value of a number. An absolute value is a concept in mathematics whereby only the magnitude of a number is taken into consideration irrespective of its sign.

The absolute value of a number would refer to the distance of that number from the zero origin point on a number line.

= ABS ( value )

The value argument in the ABS function refers to the cell or number whose absolute value you want to return.

= ABS ( B2 )

Since the ABS function only returns absolute values, all signs are ignored in the result.

This method doesn’t suffer the challenge of turning positive numbers into negative numbers or changing numbers into a string data type as in the previous methods.

Convert Negative Numbers to Positive with the Power Tools Add-on

The Power Tools add-ons is a customized program that you can install in Google Sheets.

It contains quick access to commands that help to easily complete simple tasks such as sorting, merging, combining, splitting, converting, and so on.

If you don’t have the Power Tools add-on, you can install it by clicking on the Extensions menu, then Add-ons, and then Get add-ons.

You will be taken to the Google Workspace Marketplace. Type Power Tools into the search tab and install. It will ask for permission from your google account.

The Power Tools add-on will appear in the Extensions menu option after you install it.

1. Go to the Extensions menu.
2. Select Power Tools from the options.
3. Select Start from the submenu options.

The Power Tools menu will open to the left side of your spreadsheet when you click on Start. Select the cells you want to convert and click on Convert.

The Convert text to numbers and Convert text to dates options are checked by default. Make sure you uncheck them and check the Convert number sign option.

Then click on Run.

It may take a few seconds, after which the signs will be removed from the numbers.

The Power Tools method produces results similar to when you use the ABS function. But it has the advantage that it doesn’t create a new array of numbers. You don’t have redundant columns to clean up.

Convert Negative Number to Positive with an Apps Script

You can use an Apps script to convert negative numbers to positive numbers.

function convertAbsolute() {
  var selectedRange = SpreadsheetApp.getActive().getActiveRange();
  var selectedValues = selectedRange.getValues();
  var selectedColumns = selectedRange.getWidth();
  var selectedRows = selectedRange.getHeight();

  for (i = 0; i < selectedRows; i++) {
    for (j = 0; j < selectedColumns; j++) {
      selectedRange.getCell(i + 1, j + 1).setValue(Math.abs(selectedValues[i][j]));
    };
  };
};

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Convert')
    .addItem('Convert to Absolute Value', 'convertAbsolute')
    .addToUi();
};

Copy and paste the above script to your script editor. Click on Save then refresh your spreadsheet.

The script will create a new menu item named Convert that appears with all the other standard menus.

Select the cells you want to work with and then go to the Convert menu and choose Convert to Absolute Value from the options.

The script will return the absolute value of the numbers. It’s just like using the ABS function to do the conversion, without the hassle of creating a new column and inserting a formula.

Conclusions

There are times when negative numbers need to be removed from your dataset. Fortunately, you have many options to resolve the issue without too much effort.

The ABS function, find and replace, Power Tools add-on, and App Script will all eliminate all the negative numbers in your data while leaving positive numbers unchanged.

Other methods like multiplication and the UMINUS function can be used but might have the adverse effect of converting your positive numbers into negative numbers.

What methods for converting negative numbers to positive numbers are you using? If we left anything out, let us know in the comments below!