How to Make the Value Always Positive in Excel

When you use Microsoft Excel to work with numbers, you may want to have Excel make a number always positive to find the absolute value of the number. An absolute value is always positive. In other cases, you might prefer to display financial data using all positive numbers, with negative amounts highlighted in red or set aside with something other than a minus sign. Use the ABS formula in Excel to take an absolute value and remove a sign, or use custom formatting to make negative numbers look positive.

In math, the absolute value of a number is its distance from zero, which is its value without a positive or negative sign. It's often represented with vertical bars, so |-5| = |5| = 5, for example. In Excel, you compute the absolute value using the formula function ABS.

With the formula =ABS(-5), Excel converts the negative to positive and gives the result 5. You can wrap a larger computation in the ABS function to make the result always positive.

Sometimes you may want to retain whether a number is positive or negative within your spreadsheet's logic, but you want to show the absolute difference. To do this, use a custom format.

In Excel's "Home" tab, click "Number Format," followed by "More Number Formats." In the "Format Cells" dialog box, click "Custom" in the "Category" box.

Type in a format for positive numbers, a format for negative numbers, a format for zero and a format for non-numeric text, all separated by semicolons. Consult Microsoft Excel's documentation to know the different format codes available to you. Omit a minus sign from the negative number format code to make the numbers appear positive.

Other spreadsheet programs support similar codes.

You often want to highlight positive and negative numbers differently. For financial purposes, for instance, you may want to highlight positive numbers in green or leave them unhighlighted while highlighting negatives in red.

You do this with format codes by beginning the code with a color code in square brackets, such as "[Red]" or "[Green]." Excel supports several color codes.

You can also use Excel's conditional formatting feature to color-code cells. Go to the "Home" tab, click "Conditional Formatting," and then click "Highlight Cell Rules." Click "Less Than," enter the number 0, and then choose a formatting rule for cells that have values less than zero. You can also use "Greater Than" or "Equal To" rules to set color codes for numbers greater than zero or equal to zero.