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 needed to convert the entire range to positive for a project he was working on. This is what we discussed:
How to Convert Negative Numbers to Positive in Excel: 4 Ways!
Watch: Change a Range of Cells from Negative to Positive in Excel (video 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 change 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 original 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 (you should see the $ signs like in $A$1) 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 the Paste Special, Multiply Operation
An easier way to change negative values to positive in Excel is by multiplying by -1 in mass. You can do this by using Paste Special.
- First, type -1 in a 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).
Leave a Reply