Sometimes in Excel, finding just the highest or the lowest value is not enough.
You may want to know:
- What is the 2nd highest sale?
- Who scored the 3rd lowest mark?
- What is the 5th largest expense?
- Which is the top 3rd order value?
MAX and MIN can only give you:
- The biggest
- Or the smallest
But what if you want the values in between?
That’s where the LARGE and SMALL functions help.
In simple words:
LARGE = finds the Kth largest value
SMALL = finds the Kth smallest value
(K means position: 1st, 2nd, 3rd, 4th….)
What Are LARGE & SMALL Functions?
- LARGE → returns the Nth largest value
- SMALL → returns the Nth smallest value
They work with:
- positive numbers
- negative numbers
- decimals
- zeros
They ignore:
- text
- blank cells
Syntax:
LARGE:
=LARGE(array, k)
SMALL:
=SMALL(array, k)
Arguments:
- array → the range of numbers
- k → the position you want (1 = largest/smallest, 2 = second, 3 = third…)
Examples:
| Order ID | Salesperson | Amount | Date |
| 101 | John | 1200 | 01-01-2024 |
| 102 | Sara | -300 | 03-01-2024 |
| 103 | Amit | 450 | 05-01-2024 |
| 104 | Neha | 2000 | 06-01-2024 |
| 105 | Riya | -150 | 08-01-2024 |
| 106 | Ali | 800 | 10-01-2024 |
| 107 | Priya | 3000 | 12-01-2024 |
| 108 | Karan | 100 | 15-01-2024 |
| 109 | Mehul | 0 | 18-01-2024 |
| 110 | Asha | 1700 | 20-01-2024 |
LARGE Examples (Top Values):
1. Find the 2nd Largest Sale:
=LARGE(C2:C11, 2)
Result → 2000
SMALL Examples (Top Values):
1. Find the Smallest Value:
=SMALL(C2:C11, 1)
Result → -300
2. Find the 2nd Smallest Value:
=SMALL(C2:C11, 2)
Result → -150
Difference Between MIN/MAX and SMALL/LARGE:
| Function | What It Finds |
| MAX | Only the highest value |
| MIN | Only the lowest value |
| LARGE | 1st, 2nd, 3rd… highest |
| SMALL | 1st, 2nd, 3rd… lowest |
When Should You Use LARGE?
Use LARGE when you want:
- Top 3 sales
- Highest performers
- Biggest expenses
- Top exam scores
- Highest profits
When Should You Use SMALL?
Use SMALL when you want:
- Lowest expenses
- Bottom scores
- Smallest payments
- Most negative values
- Weakest performers
Related Functions (Quick Guide):
| Function | Purpose |
| MAX | Highest value |
| MIN | Lowest value |
| LARGE | Kth largest |
| SMALL | Kth smallest |
| MAXIFS | Highest value with conditions |
| MINIFS | Lowest value with conditions |
In Simple Words:
- MAX/MIN → only top or bottom
- LARGE/SMALL → any position (2nd, 3rd, 4th…)
LARGE and SMALL help you understand your data in more detail — not just the extremes, but everything in between.
Discover more from Excellopedia
Subscribe to get the latest posts sent to your email.