Find Top Bottom Values Excel Formula

To find the top or bottom values in Excel, use the LARGE function for the largest values and the SMALL function for the smallest values. For example, =LARGE(A1:A100, 1) returns the largest number in that range, while =SMALL(A1:A100, 1) returns the smallest. This article explains how to use these functions, how to rank data, and how to handle ties, blanks, and errors.

Formula

The core formulas are:

• LARGE(array, k) – returns the k-th largest value in a range.
• SMALL(array, k) – returns the k-th smallest value in a range.

Where:

• array – the range of cells containing numeric values (e.g., A1:A100).
• k – the position from the top or bottom (1 = first, 2 = second, etc.).

For example, to get the 3rd highest score in B2:B50, use =LARGE(B2:B50, 3). To get the 2nd lowest, use =SMALL(B2:B50, 2).

Worked Example

Suppose you have a list of 10 sales figures in cells A1:A10: 120, 85, 200, 45, 310, 75, 150, 90, 260, 110.

1. To find the highest value: =LARGE(A1:A10, 1) → 310.
2. To find the lowest value: =SMALL(A1:A10, 1) → 45.
3. To find the 5th highest: =LARGE(A1:A10, 5) → 120 (after sorting: 310, 260, 200, 150, 120).
4. To find the 3rd lowest: =SMALL(A1:A10, 3) → 85 (after sorting: 45, 75, 85).

How to Choose Between LARGE and SMALL

Use LARGE when you need the highest values (top 5, top 10, etc.). Use SMALL when you need the lowest values (bottom 5, lowest 3, etc.). Both functions ignore text and logical values (TRUE/FALSE) in the range, but they do include zeroes. If you want to exclude zeroes, you need an array formula like =LARGE(IF(A1:A100>0, A1:A100), 1) entered with Ctrl+Shift+Enter (in older Excel) or with the new dynamic array behavior in Excel 365.

Common Mistakes and How to Avoid Them

• Using the wrong k value – k must be a positive integer. If k is 0 or negative, Excel returns a #NUM! error. If k is larger than the number of values, you get #NUM! as well.
• Including blank cells or text – LARGE and SMALL ignore text and blanks, but if your range contains errors (like #DIV/0!), the formula will return that error. Clean your data first.
• Confusing top/bottom with ranking – LARGE and SMALL return the value itself, not the position. To get the rank, use the RANK function.
• Not handling duplicates – If two cells have the same value, LARGE and SMALL treat them as separate entries. For example, if 100 appears twice, LARGE(…,2) will still return 100.

Alternatives and Related Functions

Excel offers several other ways to work with top/bottom values:

• RANK – returns the rank of a number in a list (e.g., =RANK(A1, A1:A100, 0) gives descending rank).
• MAX and MIN – return the single largest or smallest value, equivalent to LARGE(…,1) and SMALL(…,1).
• PERCENTILE and QUARTILE – useful for finding values at a certain percentile (e.g., 90th percentile).
• Conditional Formatting – highlight the top 10 items visually using the built-in rule (Home → Conditional Formatting → Top/Bottom Rules).
• Sorting and Filtering – the simplest way to see top/bottom values is to sort the column, but that changes your data order.

How to Extract Multiple Top or Bottom Values at Once

If you need a list of the top 5 values, you can enter the formula in a column and copy it down, changing k each time. For example, in B1 enter =LARGE($A$1:$A$100, ROW()) and copy down to B5. ROW() returns the row number, so it gives 1, 2, 3, etc. In Excel 365, you can use the SORT function: =SORT(A1:A100, -1) sorts descending, and then take the first 5 with =TAKE(SORT(A1:A100,-1),5). Similarly, =TAKE(SORT(A1:A100,1),5) gives the bottom 5.

Limitations and Performance Tips

LARGE and SMALL work on up to 1,048,576 rows (the Excel row limit). For very large datasets, array formulas (like the IF-based exclusion of zeroes) can slow down the workbook. In such cases, consider using a helper column with a simple formula, or use Power Query to filter top/bottom rows. Also, remember that LARGE and SMALL are volatile only if their arguments are volatile (e.g., OFFSET, INDIRECT); they are generally recalculated only when the referenced cells change.

Frequently Asked Questions

How do I find the top 5 values excluding zeros?

Use an array formula: =LARGE(IF(A1:A100>0, A1:A100), ROW(1:1)) entered with Ctrl+Shift+Enter in older Excel, or just press Enter in Excel 365. Copy down for 5 rows. This ignores zero and negative values.

Why does LARGE return #NUM!?

This happens when k is less than 1 or greater than the number of numeric values in the range. Check that your k value is correct and that the range contains numbers.

Can I use LARGE with text or dates?

LARGE and SMALL work only with numbers. Dates are stored as numbers in Excel, so they work. Text is ignored. If you need the latest date, use MAX or LARGE on a date range.

How do I get the position of the top value?

Use =MATCH(MAX(A1:A100), A1:A100, 0) to get the row number (relative to the range start). Or use =RANK(A1, A1:A100, 0) to get the rank of a specific cell.

What’s the difference between LARGE and RANK?

LARGE returns the value at a given position (e.g., the 2nd largest number). RANK returns the position of a given value (e.g., the rank of 150 in the list). They are inverse operations.

Does LARGE work with filtered data?

No, LARGE ignores hidden rows only if you use the SUBTOTAL function. For filtered data, use =SUBTOTAL(4, A1:A100) for the largest visible value (4 = MAX) or =SUBTOTAL(5, A1:A100) for the smallest visible value.

Related

• Start an Online Thrift Store: Build a Repeatable Selling Process
• Little Nightmares 3 Co-Op: Online Only, Cross-Play?
• Free Xbox Live Games: What’s Actually Free

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2