Find the largest or smallest numbers in a range of data using LARGE and SMALL functions
How to use Excel's LARGE and SMALL functions to find the nth largest or smallest values in a data range without sorting.
You have a daily report that requires you to identify the top three salespersons and show their individual sales figures, plus the bottom three. Here are the main approaches:
Solution 1 — Sort the data manually. You can sort by values to find the largest or smallest numbers, but this gets inconvenient for daily reports. You need to sort every day, it's prone to errors, and it breaks any links to accounting systems pulling data automatically.
Solution 2 — Write a VBA module. Not a bad approach, but if a formula can do the same job, there's no reason to add the complexity of a macro.
Solution 3 — Use MAX and MIN. These find the single largest or smallest number, but cannot return the second or third largest/smallest.
=MAX(range of data)
=MIN(range of data)
Solution 4 — Use LARGE and SMALL (recommended). When data is populated, the report is done. No manual sorting or VBA required.
LARGE Function
=LARGE(range, 1) — returns the largest number
=LARGE(range, 2) — returns the second largest number
=LARGE(range, 3) — returns the third largest number
SMALL Function
=SMALL(range, 1) — returns the smallest number
=SMALL(range, 2) — returns the second smallest number
=SMALL(range, 3) — returns the third smallest number
To identify the names of the salespersons associated with the top or bottom values, combine LARGE and SMALL with INDEX and MATCH.