4 Ways to Change a Range of Cells from Negative to Positive in Excel (+ video tutorial)

We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)

A subscriber recently asked me how he could change a range of cells from negative to positive in Excel. He received a file that included a group of negative values (sold inventory), but he needed to convert the entire range to positive for a project he was working on. This is what we discussed:

4 ways to change a range of negative cells to positive

Watch the tutorial

1.     Use the Absolute Function: =ABS()

The Absolute function in Excel returns the positive value of a given cell. Applying the Absolute function to a range of cells will convert the negative numbers to positive, while not affecting any values that were already positive. To use the Absolute function:

• In a blank area of your spreadsheet that is large enough to be the same size as the original range of cells, type =ABS and reference the first cell in your original range of numbers
• Then, copy and paste this so that you create a new range that mirrors your existing range
• Finally, copy the new values and paste over the original range by using Paste Special / Values
• Once done, you can delete where you calculated the ABS function

2.     Multiply Group of Negative Values by -1

You can also make negative values positive by multiplying them by -1.

• First, type -1 in a free cell in your spreadsheet
• Then, in a blank area of your spreadsheet that is large enough to be the same size as the original range of cells, type a formula to reference the first cell in the range with the negative values and multiply by the cell with the -1
• Press F4 to make the cell with the -1 an absolute reference and press Enter
• Copy the formula and paste it in an area that mirrors your original range
• Copy these results and, using Paste Special / Values, paste over the original range of negative cells
• Once done, delete the cells with the calculation

3.     Multiply Group of Negative Values by -1 using Paste Special / Multiply Operation

An easier way to change all the negative values to positive by multiplying by -1 is by mass multiplying using Paste Special.

• First, type -1 in a free cell in your spreadsheet and copy it
• Then select all the cells in the range that contains the negative values
• Right-click to select Paste Special
• In the Operation section, select Multiply and press OK

4.     Use Find & Replace to Change Negative Numbers to Positive

The fourth way you can change your negative numbers to positive will work if your cells do not contain any formulas. That is, if your cells contain only the negative values, but no calculations.

• Highlight to select the range of cells with the negative values, press Ctrl+H to bring up Find & Replace
• In the Find what field, type a negative sign (-)
• In the Replace with field, type nothing. I like to backspace and delete to make double sure there’s nothing there.
• Press Replace All (or “Replace” if you want to review each instance).

---