Returns the k-th largest value from a data set. LARGE finds the nth largest value when data is sorted in descending order. Use LARGE to find top values like 1st largest, 2nd largest, etc. Essential for finding top performers, identifying outliers, ranking analysis, and top-N analysis.
Master the fundamentals of Excel LARGE function
The LARGE function returns the k-th largest value from a data set. LARGE sorts the data in descending order and returns the value at position k. k=1 returns the largest value (same as MAX), k=2 returns the 2nd largest, etc. LARGE ignores text values, logical values, and empty cells. If k is greater than the number of values or ≤ 0, LARGE returns #NUM!. Essential for finding top performers, identifying top values, ranking analysis, top-N analysis, and finding outliers or highest values.
Returns value at position k
Values sorted descending
Only numeric values considered
Can return multiple values
Function-specific parameters
Function-specific return type
Find top values or performers
Rank values from largest
Analyze top N values
Identify highest outliers
Exact matching required
Returns numeric position
Handles missing text gracefully
=LARGE(array, k)Array or range of data from which you want to find the k-th largest value.
Position from the largest value to return (1 = largest, 2 = 2nd largest, etc.).
The k-th largest value
Description: Returns the k-th largest value from a data set
Find largest value
Returns the largest value. LARGE(range, 1) is equivalent to MAX(range).
Use LARGE function in VBA
' Basic LARGE in VBA
Range("C1").Value = Application.WorksheetFunction.Large(Range("A1:A10"), 1)
' Returns: Largest value
' Find largest value
Sub FindLargest()
Dim largeValue As Double
largeValue = Application.WorksheetFunction.Large(Range("A1:A10"), 1)
Range("B1").Value = largeValue
End Sub
' Find top 5 values
Sub FindTop5()
Dim i As Integer
Dim topValue As Double
For i = 1 To 5
topValue = Application.WorksheetFunction.Large(Range("A1:A100"), i)
Range("B" & i).Value = topValue
Next i
End Sub
' Compare LARGE with MAX
Sub CompareLargeMax()
Dim largeValue As Double
Dim maxValue As Double
largeValue = Application.WorksheetFunction.Large(Range("A1:A10"), 1)
maxValue = Application.WorksheetFunction.Max(Range("A1:A10"))
Range("B1").Value = "LARGE(1): " & largeValue
Range("B2").Value = "MAX: " & maxValue
' Should be equal
End Sub
' Find values in top percentile
Sub FindTopPercentile()
Dim dataRange As Range
Dim totalCount As Long
Dim percentile As Double
Dim topCount As Long
Dim i As Long
Set dataRange = Range("A1:A100")
percentile = 0.1 ' Top 10%
totalCount = Application.WorksheetFunction.Count(dataRange)
topCount = Application.WorksheetFunction.RoundUp(totalCount * percentile, 0)
' Output top values
Range("B1").Value = "Top " & (percentile * 100) & "%"
For i = 1 To topCount
Range("B" & (i + 1)).Value = Application.WorksheetFunction.Large(dataRange, i)
Next i
End SubFind top values or performers
Rank values from largest
Analyze top N values
Identify highest outliers
LARGE returns #NUM!
Check: k must be 1 ≤ k ≤ COUNT(range)Solution: This occurs when k is greater than the number of numeric values, or k ≤ 0. Ensure k is between 1 and the count of values. Use COUNT(range) to verify available values.
Uncertainty about when to use LARGE vs MAX
LARGE(range, 1) = MAX(range)Solution: MAX finds largest only. LARGE(range, 1) equals MAX(range), but LARGE can find 2nd, 3rd, etc. Use MAX for largest only; use LARGE when you need k-th largest.
Text values not considered
LARGE only works with numbersSolution: This is expected. LARGE only considers numeric values. Text values are ignored. If you need to rank text, convert to numbers first or use a different approach.
Need array of top N values
LARGE(range, {1,2,3}) returns top 3Solution: Use array formula: LARGE(range, {1,2,3,...,N}). Enter as array formula or use in newer Excel with automatic array expansion.