How to list the top N values
✓ Verified in LibreOffice 25.8.7.3Pull out the highest N numbers as a list — top 5 sales, biggest three costs — not just their total.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =LARGE($A$2:$A$8,ROW(A1)) | Fill down for as many as you want. Excel 365: =SORT(A2:A8,,-1) spills them all, or wrap in TAKE for the top N. |
| Google Sheets | =LARGE($A$2:$A$8,ROW(A1)) | Identical; =SORT(A2:A8,1,FALSE) also works. |
| LibreOffice Calc | =LARGE($A$2:$A$8,ROW(A1)) | Identical. |
How it works
LARGE returns the k-th largest value, so feeding it 1, 2, 3... gives the ranked top values: the three largest here are 95, 90, 85. Using ROW(A1) as the k makes it produce 1, 2, 3... as you fill down; the array form LARGE(range,{1;2;3}) spills them in one formula (verified above). SMALL does the same for the bottom N. On modern versions, SORT(range,,-1) sorts everything descending and TAKE(SORT(...),5) grabs just the top five — cleaner when N is large. To also show WHAT each top value belongs to, feed it into INDEX/MATCH.
Verified, not just documented
We ran =LARGE(A2:A8,{1;2;3}) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 95, 90, 85 — exactly the expected result. Every formula here is confirmed by actually executing it.