Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchUse Excel’s LARGE and SMALL functions to return the highest, lowest, or another ranked numeric value without sorting or filtering the source list. For a single maximum or minimum, MAX and MIN are simpler. Microsoft defines LARGE as returning the k-th largest value and documents corresponding ranked examples for both functions (LARGE; SMALL).
How to find the highest or lowest value without filtering
Suppose the numeric values are in B2:B20. Enter a formula in a separate, empty cell so the result appears without changing the order of the original data.
| What you want | Formula |
|---|---|
| Highest value | =LARGE(B2:B20,1) |
| Second-highest value | =LARGE(B2:B20,2) |
| Lowest value | =SMALL(B2:B20,1) |
| Third-lowest value | =SMALL(B2:B20,3) |
The second argument, k, is the rank: LARGE counts from the top, while SMALL counts from the bottom. Microsoft’s examples include LARGE(A2:A7,3) for the third-largest value and SMALL(A2:A7,2) for the second-smallest value (LARGE function; SMALL function).
When MAX and MIN are the better choice
If you only need one endpoint, =MAX(B2:B20) returns the largest value and =MIN(B2:B20) returns the smallest. These functions are more direct than setting k to 1 in LARGE or SMALL. Microsoft documents MAX as returning the largest value in a set and gives examples for finding the smallest and largest values in a range (MAX function; Find the smallest or largest number in a range).
Recommended Free Tools
Why use LARGE and SMALL instead of the filter button?
These functions are useful when you want a ranked result in a separate cell and want the list to stay in its existing order. They also make it straightforward to get the runner-up, third-place value, or another rank without repeatedly changing a sort or filter.
Filtering or sorting is still the better tool if you want to inspect the complete rows associated with the values, rearrange the records, or see which people or products match a result. Microsoft describes sorting as arranging values in ascending or descending order and points to AutoFilter or conditional formatting as options for finding top or bottom values (Sort data in a range or table).
Rank #2
- Used Book in Good Condition
What the formulas return—and what they do not
LARGE and SMALL return a value, not the full record it came from. If you need the name, product, or other details associated with that value, you will need a separate lookup step. Also, ranks count data points, not distinct values: if two entries tie, the same number can appear at more than one rank.
Quick Recap
Best Value
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Fix an invalid rank or unexpected result
- Check that k is valid. It must be a positive position no greater than the number of data points. Microsoft says
LARGEreturns#NUM!if the array is empty,kis zero or less, orkexceeds the number of data points (LARGE function). - Check what is included in the range. Make sure the formula refers to the numeric values you intend to rank, rather than headers or unrelated cells.
- For MAX with mixed data, check Microsoft’s handling notes. When values are in a referenced range,
MAXuses numbers and ignores text, logical values, and empty cells. Text or logical values supplied directly as arguments can behave differently (MAX function).
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
