Our royalty software expects that the imported unit cost will always be a positive number. If returns are being processed the royalty software will multiply the positive unit cost by -1 to reflect the effect on cost of goods sold.
Some sales import files have a negative unit cost for returns.
This lesson covers how to change a negative number to a positive number in Microsoft Excel.
Method 1. Absolute Value Formula
The absolute value formula ( =ABS(number) ) will change referenced values to a positive value.
To use this formula:
- Insert a new column
- Enter the formula =ABS(cell reference)
- Copy the formula to all cells in that column by moving the mouse over the lower left corner of the cell until cross-hairs are displayed. Click your mouse to copy the formula all the way down the column.
- Then select the column to copy it. While the copied column is still selected, use the Paste Special > Values Only command (right click your mouse to see this command) to replace the formulas with the resulting absolute values.
|-24.00||=ABS(-24.00)||Absolute value of -24.00||24.00|
|-4.95||=ABS(B2)||Absolute value of -4.95||4.95|
Method 2. Use Search and Replace to Remove the Negative Symbol
Another method; for numbers formatted as text, is to use the Search and Replace function to remove the negative sign; “-“, from all cells in the column.
To use this method:
- Select the column
- In Excel’s Editing control box select Find & Select > Replace
- In the Find what text box enter the negative sign.
- Leave the Replace with text box empty.
- Press the Replace All button.