To turn a negative number into a positive one in Excel, use the ABS function: it returns the number’s absolute value. For example, if A2 contains -18.75, enter =ABS(A2) in another cell to return 18.75. ABS also leaves positive numbers unchanged and returns 0 for zero, so its result is nonnegative—not always strictly positive.
What the ABS function does
ABS removes a number’s negative sign while keeping its magnitude. In mathematics, absolute value describes a number’s distance from zero, without regard to which side of zero it lies on. Thus, -12 and 12 have the same absolute value, 12. Excel applies that same idea with ABS.
This is useful when the sign is not relevant to the question you are asking. For example, you might want to compare the size of two differences, report how far a measurement is from a target, or display the magnitude of a variance without showing whether it was above or below the target. In each case, the sign may be less important than the amount.
That change in meaning matters. A negative balance, temperature change, or budget variance may communicate a loss, direction, or shortfall. ABS does not correct the underlying data; it simply returns a value without the negative sign. Keep the original signed value if its direction is meaningful, and use the absolute result only for the calculation or display that needs it.
ABS syntax and a basic example
ABS has a single argument, written as ABS(number). The argument can be a number typed into the formula, a cell reference, or a calculation that produces a number. In a worksheet formula, the function name follows an equals sign.
With cell A2 containing -18.75, enter =ABS(A2) in B2. The result is 18.75. If A2 contains 18.75 instead, the result remains 18.75; if A2 contains 0, the result is 0. Those three cases show why “nonnegative” is more precise than “positive.” A positive number is greater than zero, while a nonnegative number can also be zero.
ABS is available in Microsoft Excel for Microsoft 365 on Windows, as well as Excel for Mac and Excel for the web. The function syntax is the same in these versions. You can type the formula directly into a cell or use Excel’s formula suggestions while entering it.
Apply ABS to a column of values
To convert a list of signed values, place the result formula beside the first value and fill it down. For example, assume A2:A5 contains -12, 8, 0, and -3. In B2, enter =ABS(A2), then copy or fill the formula through B5. The results are 12, 8, 0, and 3, respectively. Each row refers to the value beside it because the formula uses a relative cell reference.
In Excel for Microsoft 365 on Windows, you can copy B2, select B3:B5, and press Ctrl+D to fill down. You can also drag the fill handle at the lower-right corner of B2 through the remaining rows. Excel for Mac has a Fill Down command as well; the mouse fill handle works across desktop versions. Excel for the web supports entering and copying the formula too.
Keep the source values in their own column if you may need to inspect or recover their original signs. If you replace the signed data with absolute values, you lose information about which entries were negative. A separate result column makes the transformation visible and leaves the original figures available for later checks.
Common uses and worked examples
One common use is measuring the size of a difference. Suppose A2 contains the actual result and B2 contains a target. If actual is 92 and target is 100, the subtraction gives a negative difference, but the size of the gap is 8. With actual in A2 and target in B2, enter =ABS(A2-B2) in C2. The result is 8. If the actual result were 108, the formula would also return 8, because the distance from the target is the same.
This is helpful for a distance or error magnitude, but it hides whether the actual result was below or above the target. If that direction matters, retain the signed difference as well. For example, a negative signed variance can indicate a shortfall, while a positive one can indicate an excess. Reporting only the absolute variance would remove that distinction.
Another use is comparing magnitudes. If A2 contains -14 and B2 contains 9, ABS applied separately gives magnitudes of 14 and 9. You can then make a comparison using those results or refer to ABS expressions in another calculation. Be clear about whether you are comparing signed values or magnitudes: -14 is less than 9 as a signed number, but its magnitude is larger.
ABS can also be part of a larger formula. For instance, a worksheet might calculate signed changes in one column and their absolute sizes in another. Keeping those two outputs separate makes it easier for someone reviewing the sheet to understand both the direction and the scale of each change.
ABS versus other ways to handle negative numbers
ABS is usually the clearest choice when the goal is “remove the negative sign, but otherwise preserve the number.” It handles both negative and positive inputs without requiring a condition. An IF formula can express the same rule, but is longer and usually adds no benefit for this straightforward task.
With the value in A2, the alternative formula =IF(A2<0,-A2,A2) returns 18 for -18, 18 for 18, and 0 for 0. Here, IF checks whether A2 is below zero and negates it only in that case. For ordinary numeric inputs, ABS states the intent more directly and is easier to maintain.
Do not confuse ABS with MAX. MAX finds the largest value in a set; it does not turn every negative value into a positive one. With values -20 and 5 in A2:A3, =MAX(A2:A3) returns 5, not 20. Likewise, SIGN reports whether a number is negative, zero, or positive as -1, 0, or 1; it does not return the number’s magnitude.
If you want to extract only the values that are already positive from a list, that is a filtering task, not an absolute-value task. With numbers in A2:A10, the formula =FILTER(A2:A10,A2:A10>0) returns the entries greater than zero and excludes zero and negative entries. FILTER is available in Microsoft 365 and Excel 2021 or later. Unlike ABS, it does not change negative values into positive ones.
When a nonnegative result is not enough
Because zero stays zero, ABS cannot guarantee a strictly positive result. If a process requires an output greater than zero, decide what zero should mean before changing the formula. It might be an error, a valid absence of change, or a value that should be replaced by a defined minimum. The right handling depends on the worksheet’s purpose; there is no universal replacement value.
Also consider units and interpretation. Applying ABS to a temperature difference can give the size of the change, but applying it to a signed bank transaction could make a withdrawal look like an amount received. A value with its sign removed may be numerically correct for a magnitude calculation yet misleading in a report. Label columns clearly—for example, “Absolute variance” rather than simply “Variance”—so readers know what the result represents.
Formatting is another separate issue. A custom number format can sometimes display a negative value without a minus sign, but that does not make the stored number positive. Calculations using that cell still use its actual negative value. If other formulas need a nonnegative number, use ABS in a calculation or create a separate result cell rather than relying on appearance alone.
Common problems and troubleshooting
- The result is still zero: ABS of zero is zero. It does not add a minimum amount or force a positive result.
- The result has no minus sign, but later calculations behave unexpectedly: Check whether the cell contains a genuinely negative value formatted to hide its sign. Formatting changes appearance, not the stored number.
- The formula returns an error: Check that the referenced cell contains a usable number or a calculation that returns a number. Text that is not recognized as numeric can cause a value error.
- The result seems to lose important meaning: Keep the signed source value and use ABS in a separate column. The absolute value intentionally removes direction.
- A copied formula points to the wrong cell: Review the references after filling down. A reference such as A2 normally adjusts to A3, A4, and so on as it is copied to later rows.
For a quick check, test the formula with one negative value, one positive value, and zero. If those cases return the expected magnitude, unchanged magnitude, and zero, respectively, the function is behaving as intended. If the source data is imported, check for text values, stray spaces, or other nonnumeric entries before relying on results across a large range.
Frequently Asked Questions
What Excel function turns a negative number positive?
Use ABS. If A2 contains -7, =ABS(A2) returns 7. The function also leaves positive numbers unchanged.
Does ABS always return a positive number?
No. ABS returns a nonnegative number. It returns zero when the input is zero, so its result is not strictly positive in every case.
How do I make only negative values positive in Excel?
Use ABS on each value. Negative entries become positive, while positive entries and zero remain unchanged. Fill the formula down a neighboring column to apply it to a list.
Can ABS change the original cell?
No. A formula such as =ABS(A2) returns a result in the cell where you enter the formula; it does not rewrite A2. Keeping the result in a separate column preserves the original signed value.
What is the difference between ABS and MAX?
ABS returns the magnitude of a number without its sign. MAX returns the largest value among its arguments. MAX does not convert negative values to positive ones.
Why does my ABS formula return an error?
Check the referenced input for text or other content Excel cannot use as a number. Also confirm that the formula refers to the intended cell and that the source value is not itself an error.
Related
- Conditional Formatting: Highlight Duplicates in Excel
- Data Validation Testing: Methods and Examples
- Data Validation Studio: Excel Rules, Setup, and Limits
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel HLOOKUP: Return a Column NumberHLOOKUP does not return the number of the column where it finds a match; it returns a value…
- Excel INDIRECT Function: Reference Another SheetYes. In Excel, INDIRECT can turn a text string containing a worksheet reference into a usable reference. For…
- Excel ISBLANK Function Example: Formulas and TipsIn Excel, use =ISBLANK(A2) to test whether cell A2 is genuinely empty; it returns TRUE if the cell…
- Ignore Divide by Zero Errors in Excel with IFERRORTo hide a divide-by-zero result in Excel, wrap the division in IFERROR, such as =IFERROR(B2/C2,""). This displays a…
- Excel: How to Hide Columns to the Right of Your DataTo hide every column to the right of your data in Excel for Microsoft 365 on Windows, select…
- How to Sort Alphabetically in Excel Without Mixing RowsTo sort alphabetically in Excel for Microsoft 365 on Windows, select a cell in the column, then choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.