How to Make the Value Always Positive in Excel
Two options in Excel turn negative numbers into positive numbers: the ABS function and a custom cell format.
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 absolute value. To hide the negative sign in a cell without actually adjusting its value, use a custom format instead.
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.
The ABS function can only target a single cell, so a formula like "=ABS(C1:C6)" won't work. To find the absolute value of several cells combined, first add them together using the SUM function or AutoSum.
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.
The ABS function returns an absolute value, which is always positive: 5 remains 5, but -5 turns into 5.
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.
As written, this format rounds numbers to the nearest integer. To display decimal values, change the format to #.#;#.#;0 and add an additional # after each period to designate the maximum number of decimal places. For example, to display the value -14.3386 as 14.339, use the format #.###;#.###;0
- The first semicolon in the format separates formatting for positive and negative numbers. This method works by using the same formatting code on both sides of the semicolon, producing the same result no matter if the cell has a positive or negative value.
- The second semicolon and the 0 instructs Excel how to display the number 0. Without this section, a cell with a value of 0 would appear blank.
Click a cell to see its true value in the formula bar. Equations that reference the cell always use the true value.