To find the largest absolute value in Excel for Microsoft 365, enter =MAX(ABS(A2:A10)) in a cell, replacing A2:A10 with your numeric range. The result is the greatest magnitude, so it is always zero or positive—even when the source value with the greatest magnitude is negative.
What maximum absolute value means
An absolute value measures how far a number is from zero, without regard to its sign. For example, the absolute value of -18 is 18, and the absolute value of 12 is 12. The maximum absolute value in a set is therefore the largest distance from zero among its numbers.
This is different from the ordinary maximum. Given -18, 12, and 9, the ordinary maximum is 12, but the maximum absolute value is 18 because |-18| is greater than |12| and |9|. This distinction matters in tasks such as finding the largest deviation, price change, temperature swing, or error, where direction may be negative or positive but size is what you want to compare.
The result of the formula is the magnitude, not necessarily the original cell value. In the example above, the formula returns 18, not -18. If you need to report which signed value produced that magnitude, use the lookup method below.
How the formula works
With the data in A2:A10 and the answer cell outside that range, =MAX(ABS(A2:A10)) applies ABS to the values and then asks MAX to select the largest result. ABS converts each negative number to its positive magnitude and leaves positive numbers and zero unchanged. MAX then returns the greatest number from that set.
In current Excel for Microsoft 365, enter the formula normally and press Enter. You do not need to select multiple output cells or press a special array-formula shortcut. Excel evaluates the array of absolute values as part of the calculation, while MAX produces one final result. The same formula is available in Excel for the web and current Excel for Mac; the formula syntax is the same.
Use a clearly bounded range that covers the data rather than referencing an entire column by default. A range such as A2:A500 is easier to review and can avoid unnecessary calculation work in a large workbook. If new rows are added regularly, an Excel Table can help keep the source data organized; update the formula’s range when the data grows unless the formula already refers to an expanding range.
Worked example with positive and negative values
Suppose A2:A6 contains -12, 7, -19, 4, and 0, in that order. With those numbers in A2:A6, enter =MAX(ABS(A2:A6)) in another cell. ABS evaluates the values as 12, 7, 19, 4, and 0, so the formula returns 19. The source value behind that result is -19.
Compare the result with the ordinary maximum. With the same values in A2:A6, =MAX(A2:A6) returns 7, because 7 is the numerically greatest value. That answer is correct for an ordinary maximum, but it does not answer which value has the greatest magnitude. The negative number -19 is less than 7 on the number line but farther from zero.
A useful way to check a result is to inspect the source values with their signs and compare their magnitudes. If a result seems unexpectedly small, confirm that the formula includes the negative values and that the source range is not missing rows. If it seems unexpectedly large, look for an outlier or a value stored in the range that was not intended to be part of the analysis.
Return the original signed value
Sometimes the goal is not just to report a magnitude. You may want the original value that created it, such as -19, so a report can show both the size and direction of the largest change. XLOOKUP can search for the maximum magnitude and return the corresponding cell value.
With the source values in A2:A10, enter =XLOOKUP(MAX(ABS(A2:A10)),ABS(A2:A10),A2:A10) in the result cell. The formula returns the original signed value whose absolute value is greatest. If the largest magnitude belongs to -19, the result is -19 rather than 19. XLOOKUP is available in Microsoft 365 and Excel 2021 or later.
If two or more values share the greatest magnitude, XLOOKUP returns the first matching value in the range, reading from top to bottom. For example, if the range contains -15 before 15, it returns -15; if 15 appears first, it returns 15. The magnitude is still 15 in either case. If the report needs to list every tied value, a single XLOOKUP result is not enough; use a separate tie-handling method or filter the data for all values whose absolute value equals the maximum.
Alternative formulas and when to use them
For a range containing ordinary numeric values, another way to calculate the maximum absolute value is to compare the largest positive value with the magnitude of the most negative value. With the numbers in A2:A10, use =MAX(MAX(A2:A10),-MIN(A2:A10)). MAX(A2:A10) finds the largest value, and -MIN(A2:A10) converts the smallest, potentially negative value into its positive magnitude. The outer MAX chooses the larger of those two candidates.
This alternative can be useful when you want to see the logic in two parts, or when you are working in a version without dynamic-array behavior. It assumes the range contains numeric data and that you want a nonnegative magnitude. For a clear, direct formula in Microsoft 365, =MAX(ABS(A2:A10)) is usually easier to read and maintain.
In older Excel versions that do not evaluate this array calculation with a normal Enter, the ABS-and-MAX approach may need to be entered as an array formula. On Windows, that generally means confirming it with Ctrl+Shift+Enter rather than Enter; Excel displays braces around the formula automatically. Do not type the braces yourself. Microsoft 365 and current Excel versions accept the formula with Enter, so the older keystroke is not needed there.
Choose the right range and check the data
The formula can only evaluate the cells you include. Before relying on the result, check that the range starts and ends at the intended rows, particularly if the worksheet has headers, totals, or notes mixed in with the measurements. Including a total or a prior result can produce a misleading maximum.
- Keep headers outside the numeric range. For example, if A1 is a heading and measurements begin in A2, use A2:A10 rather than A1:A10.
- Check that values that look like numbers are actually stored as numbers. Imported text can behave differently in calculations and may need to be converted before analysis.
- Decide whether zero is a valid observation. Zero does not change the maximum magnitude unless every value is zero or the range has no other numeric values.
- Remember that filtering or hiding rows does not make MAX automatically ignore those rows. The basic formula evaluates the referenced range, including filtered-out or hidden cells.
If the worksheet uses filters and the question is specifically about visible records, do not assume the basic formula gives a visible-rows-only answer. Define which rows count first, then use a method designed for that selection or place the relevant visible data in a separate range. This avoids accidentally reporting an extreme value from a record that the current view has hidden.
Common errors and troubleshooting
If the formula displays an error instead of a number, inspect the source range for error values such as #N/A or #VALUE!. An error in data used by an array calculation can prevent the result from being calculated as expected. Correct the source error or decide explicitly how your analysis should treat that record before calculating the maximum; silently suppressing errors can hide a data-quality problem.
If the result is 0, check whether the selected cells contain numeric values and whether the range is correct. A range of zeros has a maximum absolute value of zero. But cells that only appear numeric because they contain text may not be evaluated as intended, and a range that misses the actual observations can also lead to an unhelpful answer.
If Excel shows the formula characters rather than a result, the cell may be formatted as Text or the formula may have been entered with a leading apostrophe. Change the cell format to General, then edit and re-enter the formula. Also check that calculation is not set to manual if the value appears stale; in Excel for Windows, calculation options are available on the Formulas tab under Calculation Options.
Finally, distinguish a formula error from a surprising but valid answer. A maximum absolute value of 240 could be correct if one source entry is -240, even when the other values are between -20 and 20. Sorting or filtering the source range to inspect the largest positive and most negative values can help identify the observation driving the result.
Frequently Asked Questions
What is the Excel formula for the maximum absolute value?
With numeric values in A2:A10, use =MAX(ABS(A2:A10)). In Microsoft 365, enter it with Enter; the result is the greatest magnitude in the range.
Does MAX(ABS(range)) return the original negative number?
No. It returns the nonnegative magnitude. If you need the original signed value, use XLOOKUP with the absolute values as the lookup array, as shown above.
What is the difference between MAX and maximum absolute value?
MAX returns the numerically greatest value, so it may be a positive number even when a negative value is farther from zero. Maximum absolute value compares magnitudes and ignores direction when choosing the largest size.
Does the formula ignore hidden or filtered rows?
No. =MAX(ABS(A2:A10)) includes cells in the referenced range even if their rows are hidden or filtered out. Use a method that explicitly selects visible or relevant records when that is the intended calculation.
How do I handle a tie for the largest magnitude?
The magnitude formula returns the shared magnitude once. An XLOOKUP formula returns the first matching signed value in the source range. To report all tied values, identify every row whose absolute value equals the maximum rather than relying on a single lookup result.
Related
- Test Excel Data: Practical Checks for Accuracy and Errors
- Excel PivotTable Testing: Checks, Steps, and Common Fixes
- Text to Date in Excel: Convert Dates Stored as Text
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Change Email View Settings in Outlook: A Step-by-Step GuideTo change how email looks in Outlook, open the view or layout controls for your version, then adjust…
- Protect Range Not Showing in Google Sheets? Try These FixesIf Protect sheets and ranges is missing in Google Sheets, first check that you have editor access and…
- Insert a Line in Word Without Text: Simple MethodsTo insert a horizontal line in Word without typing text, place the cursor in an empty paragraph and…
- Out of Office in Outlook Desktop: Set Up Your ReplyTo set an out-of-office reply in Outlook desktop, open File > Automatic Replies in classic Outlook for Windows,…
- How to Edit the Address Block in a Word Mail MergeTo edit an address block in a Word mail merge, replace or reinsert the Address Block field from…
- Conditional Formatting Excel: Color an Entire RowYes. To color an entire row in Excel based on one cell, select the rows you want formatted,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.