# How to find the largest absolute value in Excel

Take MAX of the absolute values: `=MAX(ABS(B2:B6))` in Excel 365, or `=MAX(MAX(B2:B6),-MIN(B2:B6))` in any version.

**Excel:** `=MAX(ABS(B2:B6))` (Every current version)

**Google Sheets:** `=ARRAYFORMULA(MAX(ABS(B2:B6)))`

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Day | Price change |  | Largest move |
| Mon | 1.25 |  |  |
| Tue | -3.4 |  |  |
| Wed | 0.8 |  |  |
| Thu | 2.95 |  |  |
| Fri | -0.6 |  |  |

Formula in E1: `=MAX(ABS(B2:B6))`

Result: 3.40

## How it works

ABS turns every change into its size, so Tuesday's -3.40 becomes 3.40, and MAX returns the biggest. Plain MAX would return 2.95 and miss the larger drop.

## Other ways

- `=MAX(MAX(B2:B6),-MIN(B2:B6))`: Compares the biggest rise with the biggest fall; no array needed in any version.
- `=INDEX(B2:B6,MATCH(MAX(ABS(B2:B6)),ABS(B2:B6),0))`: Returns the move with its sign, so you can tell a rise from a fall. (Keeps the sign: -3.40.)

## Common mistakes

- In Excel 2019 and older, `=MAX(ABS(B2:B6))` needs Ctrl+Shift+Enter, or it uses only one cell.
- `=ABS(MAX(B2:B6))` is the absolute value of the largest number, not the largest absolute value.

## Questions

**How do I find the smallest absolute value?**

Use `=MIN(ABS(B2:B6))`, the move closest to zero.

**Which day had the biggest move?**

Use `=INDEX(A2:A6,MATCH(E1,ABS(B2:B6),0))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/largest-absolute-value