Excel 2013 has two methods for forcing a value to display as positive, no matter if the value was originally positive or negative. To mathematically change the value of a cell to the positive, use the ABS function, which finds the original value's . To hide the negative sign in a cell without actually adjusting its value, use a custom format instead.
The ABS function returns an absolute value, which is always positive: 5 remains 5, but -5 turns into 5.
Absolute Value of an Existing Cell
To find the absolute value of another cell without overwriting that cell, use the ABS function in a blank cell. Start the formula with =ABS( and then click the cell you want to turn positive. Press Enter to finish the formula and see the result.
Absolute Value in a Formula
The ABS function also works in conjunction with other functions and math operations in a formula. To find the absolute value of an equation, enclose the entire equation in an ABS function. For example, if the formula "=SUM(A1:A4)B1+14" works out to -10, change the formula to "=ABS(SUM(A1:A4)B1+14)" to flip the result to 10.
Video of the Day
A cell's formatting affects how it looks, but not its stored value. Use a custom format when you want your cell to always display a positive number, but still use a cell's true, negative value in other calculations.
Select one or more cells, click Format in the Cells section of the Home tab and choose Format Cells.
Choose Custom on the Number tab and enter #;#;0 in the Type box. Click OK.
Click a cell to see its true value in the formula bar. Equations that reference the cell always use the true value.