In Excel, use the ABS function to return a number’s absolute value: it removes the number’s negative sign while leaving a positive number unchanged. For example, with a value in A2, enter =ABS(A2) in another cell; if A2 contains -12, the result is 12.
What the ABS Function Does
Absolute value describes a number’s distance from zero, without regard to direction. The distance is never negative: the absolute value of -12 is 12, the absolute value of 12 is also 12, and the absolute value of zero is zero. Excel’s ABS function applies that rule to a number or to a calculation that returns a number.
ABS changes the result, not the source cell. If A2 contains -12, entering the function in B2 displays 12 in B2 while A2 remains -12. This is useful when you need a positive magnitude for display, analysis, or a later calculation but still want to preserve the original signed data.
ABS is a worksheet function available in Microsoft 365 for Windows, as well as Excel for Mac and Excel for the web. Its syntax is simple: it takes one argument, the value whose absolute value you want. You do not need a special setting or add-in.
Basic Syntax and a First Example
Suppose A2 contains a number. In B2, enter =ABS(A2). If A2 is -4.75, B2 returns 4.75. If A2 is 4.75, B2 still returns 4.75. If A2 is 0, B2 returns 0. The function does not round the number; it preserves its numeric precision.
You can also give ABS a number directly instead of a cell reference. For instance, =ABS(-18) returns 18. In a working spreadsheet, a cell reference is usually more useful because the result updates when the source value changes. To apply the function to multiple rows, enter it beside the first value, then use the fill handle to copy the formula down. Excel adjusts relative references for each row.
For example, with source values in A2:A5 and results intended for B2:B5, enter =ABS(A2) in B2 and fill down through B5. The copied formulas refer to A3, A4, and A5 in their corresponding rows. This produces a positive magnitude for each original value without overwriting the data.
Use ABS on Differences and Calculations
ABS can wrap a calculation, not just a cell reference. This is particularly helpful when the direction of a difference does not matter. Suppose the measured value is in A2 and the target is in B2. The following setup returns the distance between them, whichever value is larger.
With the measured value in A2 and the target in B2, enter =ABS(A2-B2) in C2. If A2 is 98 and B2 is 100, the subtraction is -2 and C2 returns 2. If A2 is 102 and B2 is 100, the subtraction is 2 and C2 again returns 2.
This can be useful for checking the size of a variance, measuring an error, or finding how far a reading is from a target. But the function discards direction. In the example, the result alone cannot tell whether the measurement was below or above the target. Keep the original subtraction or another directional result as well when that information matters.
ABS also works around other numeric functions. With values in B2:B10, enter =ABS(SUM(B2:B10)) in C2 to return the magnitude of their net total. This is not the same as adding the magnitude of every entry: positive and negative values in the range can cancel inside SUM before ABS is applied. If you need each row’s magnitude, calculate ABS for each row first and then sum those results.
Common Uses in Real Worksheets
Absolute values show up in practical tasks where a size matters more than a plus or minus sign. Common examples include:
- Variances: Compare an actual figure with a target and report how large the gap is, while retaining a separate signed variance to show overage or shortfall.
- Measurement error: Find how far a reading is from a reference measurement when either direction represents an error.
- Change in balances: Display the size of a change independently of whether a balance increased or decreased.
- Mixed-sign data: Prepare positive magnitudes for a chart or a report while keeping the original observations unchanged.
- Tolerance checks: Determine whether a difference is within an allowed range, such as one hundredth of a unit.
For a tolerance check, suppose C2 contains the signed difference between an observed value and its target. With that difference in C2, enter =IF(ABS(C2)<=0.01,"Within tolerance","Check") in D2. A difference of -0.008 returns “Within tolerance,” because its distance from zero is less than 0.01; a difference of 0.02 returns “Check.” The threshold is illustrative, so replace it with the appropriate tolerance and units for your data.
In Excel, number formatting affects how a result appears, not the underlying result. If an absolute value needs a percentage, currency, or a particular number of decimal places, format the output cell accordingly using the options on the Home tab. For instance, ABS applied to a value stored as -5% returns the positive numeric equivalent, which can be displayed as 5% if the result cell uses percentage formatting.
ABS Versus Absolute Cell References
The word “absolute” has two different meanings in Excel, and this causes a frequent search-related confusion. The ABS function calculates the absolute value of a number. An absolute cell reference, such as $A$1, locks a cell address when a formula is copied. The dollar signs do not change a number’s sign or calculate its distance from zero.
These ideas can be used together. Suppose A1 contains -9 and you want copied formulas to keep referring to A1. With the value in A1, enter =ABS($A$1) in B1. The result is 9, and the reference remains fixed if you copy the formula to another cell. By contrast, =ABS(A1) also returns 9 in B1, but its relative reference can adjust when copied to a different row or column.
When copying formulas, choose relative, absolute, or mixed references based on which inputs should move. In Windows Excel, while editing a formula, select a cell reference and press F4 to cycle through reference styles. On a Mac, the equivalent shortcut is commonly Command+T while editing a formula; keyboard settings can affect function-key behavior. You can also type the dollar signs directly.
Common Problems and How to Avoid Them
If ABS returns #VALUE!, check whether the referenced cell contains text rather than a number. Text that looks numeric may have been imported with an apostrophe, spaces, or other characters, and Excel may not be able to use it as a number in the calculation. Inspect the source cell and clean or convert the imported data as needed. A genuinely nonnumeric entry such as a word cannot be given a numeric absolute value.
Another common mistake is using ABS when you need to preserve whether a value is positive or negative. For example, a signed difference can explain whether actual results exceeded a target or fell short. If you replace it with an absolute value too early, the direction is lost. Keep a signed calculation for analysis and use ABS in a separate result when you also need the gap’s size.
ABS does not automatically round a value. A result may contain decimal places that are not visible because of the cell’s number format. Increase the displayed decimal places to inspect it, or use a rounding function separately if the task requires a rounded value. Do not confuse hiding decimal places with changing the stored number: formatting alone generally changes only its display.
Dates and times are numeric values in Excel, so ABS can operate on their underlying serial numbers. However, applying it to a date is rarely a meaningful way to find the difference between two dates. For elapsed time, calculate the difference between the dates or times and then decide whether the direction matters. Applying ABS to a date serial itself may produce a number that is displayed with an unexpected date or number format.
Excel on Windows, Mac, and the Web
The ABS function’s basic syntax and behavior are the same in Microsoft 365 for Windows, Excel for Mac, and Excel for the web. You can enter it directly in a cell or select it from Excel’s function suggestions. There is no platform-specific version of the formula to substitute.
The main differences are in the surrounding interface and keyboard shortcuts. Ribbon labels and shortcut behavior can vary on Mac, and browser-based Excel uses a web interface rather than the Windows desktop ribbon. Those differences do not change the formula: a cell reference passed to ABS is handled in the same way. If you are following a Windows-specific shortcut for copying or editing a formula on another platform, check the corresponding shortcut for that version.
Frequently Asked Questions
What is the Excel formula for absolute value?
Use =ABS(number), replacing number with a numeric value, cell reference, or calculation. For a value in A2, =ABS(A2) returns its nonnegative magnitude.
How do I make negative numbers positive in Excel?
Put the first negative or positive value in A2 and enter =ABS(A2) in another cell. Fill the formula down to process additional rows. The source values remain unchanged unless you deliberately replace them.
Does ABS change positive numbers or zero?
No. ABS leaves positive numbers unchanged, and zero remains zero. It changes a negative number to its positive magnitude.
How do I get the absolute difference between two cells?
With the two values in A2 and B2, enter =ABS(A2-B2) in another cell. This gives the size of the gap but does not indicate which value is larger.
Is ABS the same as using dollar signs in a formula?
No. ABS calculates a number’s absolute value. Dollar signs in a reference such as $A$1 lock the row and column when a formula is copied; they do not alter the referenced number.
Choosing the Right Result
Use ABS when you need a nonnegative magnitude and the sign has no bearing on that particular result. Use a signed calculation when direction matters, and consider keeping both results when a report needs to show both the size and direction of a difference. That distinction makes formulas easier to interpret and helps prevent a positive-looking result from concealing an overage, shortfall, or other meaningful sign.
Related
- Conditional Formatting in Microsoft Project: What Works
- Data Validation and Verification in Excel Explained
- Power Query Replace Text: Steps, Options, and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Count Nonblank Cells: Formulas and ExamplesTo count nonblank cells in Excel, use =COUNTA(A2:A20), replacing the range with the cells you want to check.…
- Excel Formula for Hours Between Two TimesTo calculate hours between two times in Excel, subtract the start time from the end time and multiply…
- Excel Formula: Days Between Two Dates and Today’s DateTo calculate days between two dates in Excel, subtract the earlier date from the later one: with a…
- Concatenate Excel Text With a Space Between ValuesYes. To concatenate text from cells A2 and B2 with one space between them, enter =A2&" "&B2; if…
- Excel Power Query Left Function: How to Extract TextIn Excel Power Query, the equivalent of the worksheet LEFT function is Text.Start(text, count): it returns the first…
- 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…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.