# How to find the second largest value in Excel

Use LARGE with a rank of 2, `=LARGE(B2:B7,2)`, and change the 2 for any nth largest.

**Excel:** `=LARGE(B2:B7,2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Runner | Longest run (km) |  | Second longest |
| Ana | 42.2 |  |  |
| Ben | 38.5 |  |  |
| Cara | 41.6 |  |  |
| Dev | 35 |  |  |
| Eli | 40.1 |  |  |
| Fay | 33.7 |  |  |

Formula in E1: `=LARGE(B2:B7,2)`

Result: 41.6

## How it works

LARGE returns the value at a given rank, counting from the top: rank 1 is the maximum, rank 2 the next one down. Here that is Cara's 41.6 km. SMALL does the same from the bottom.

## Other ways

- `=MAXIFS(B2:B7,B2:B7,"<"&MAX(B2:B7))`: The largest value below the maximum. Use it when a tie for first place should not count as second.

## Common mistakes

- Ties count as separate ranks: if two runners both ran 42.2, LARGE(…,2) is also 42.2. Use the MAXIFS version for the second-highest distinct value.
- If the rank is larger than the number of values, LARGE returns #NUM!.

## Questions

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

Use `=SMALL(B2:B7,2)`.

**How do I return the name of the second place?**

Look it up: `=XLOOKUP(LARGE(B2:B7,2),B2:B7,A2:A7)`.

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/second-largest-value