Bird
Raised Fist0
Excelspreadsheet~5 mins

LARGE and SMALL in Excel - Step-by-Step Guide

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Introduction
The LARGE and SMALL functions help you find the biggest or smallest numbers in a list. They are useful when you want to quickly see top or bottom values without sorting your data.
When you want to find the highest sales number in a list of sales.
When you need to know the third smallest test score in a class.
When you want to highlight the top 5 expenses from a budget.
When you want to find the lowest temperature recorded in a week.
When you want to compare the largest and smallest values in a data set.
Steps
Step 1: Click
- cell where you want the result
The cell is selected and ready for input
Step 2: Type
- the selected cell
The formula is entered in the cell
💡 Start typing =LARGE( or =SMALL( to begin the formula
Step 3: Select
- the range of numbers you want to analyze
The range appears inside the formula parentheses
Step 4: Type
- after the range in the formula
The formula now includes the position number
💡 For example, type ,1 to find the largest or smallest number
Step 5: Press
- Enter key
The cell shows the largest or smallest number based on your formula
Before vs After
Before
Column A has numbers: 10, 20, 30, 40, 50 in cells A1 to A5
After
Cell B1 with formula =LARGE(A1:A5, 1) shows 50; Cell B2 with formula =SMALL(A1:A5, 2) shows 20
Settings Reference
Array argument
📍 Inside the formula parentheses
Defines the list of numbers to find the largest or smallest value from
Default: No default, you must specify
k argument
📍 Inside the formula parentheses after the array
Specifies which largest or smallest value to return (1 = largest/smallest, 2 = second largest/smallest, etc.)
Default: No default, you must specify
Common Mistakes
Using a k value larger than the number of items in the range
Excel returns a #NUM! error because it cannot find that position
Make sure k is less than or equal to the number of values in the range
Not using a range of numbers as the array argument
The formula will return an error or unexpected result
Always select a range or array of numeric values for the first argument
Summary
LARGE finds the nth largest number in a list; SMALL finds the nth smallest.
You must provide a range of numbers and the position (k) you want.
Be careful that k is within the range size to avoid errors.

Practice

(1/5)
1. What does the Excel function LARGE(range, k) do?
easy
A. Returns the smallest number in the range
B. Returns the k-th largest number in the range
C. Returns the sum of the largest k numbers
D. Returns the average of the range

Solution

  1. Step 1: Understand the function purpose

    The LARGE function is designed to find the k-th largest value in a list or range of numbers.

  2. Step 2: Compare options with function behavior

    Only Returns the k-th largest number in the range correctly describes that it returns the k-th largest number, not the smallest, sum, or average.

  3. Final Answer:

    Returns the k-th largest number in the range -> Option B

  4. Quick Check:

    LARGE(range, k) = k-th largest number [OK]
Hint: LARGE picks biggest; SMALL picks smallest number [OK]
Common Mistakes:
  • Confusing LARGE with SUM or AVERAGE
  • Thinking LARGE returns smallest values
  • Mixing up k-th largest with total count
2. Which of these is the correct syntax to find the 3rd smallest number in cells A1 to A10?
easy
A. =SMALL(A1:A10, 3)
B. =SMALL(3, A1:A10)
C. =SMALL(A1:A10)
D. =SMALL(A1:A10, "3")

Solution

  1. Step 1: Recall SMALL function syntax

    The correct syntax is SMALL(range, k) where range is the data and k is the position.

  2. Step 2: Check each option

    =SMALL(A1:A10, 3) matches the correct syntax. =SMALL(3, A1:A10) reverses arguments, =SMALL(A1:A10) misses k, =SMALL(A1:A10, "3") uses a string instead of a number.

  3. Final Answer:

    =SMALL(A1:A10, 3) -> Option A

  4. Quick Check:

    SMALL(range, k) correct syntax [OK]
Hint: Use SMALL(range, k) with number k, not string [OK]
Common Mistakes:
  • Swapping arguments order
  • Omitting the k value
  • Using quotes around k
3. Given the values in cells A1 to A5: 10, 25, 15, 30, 20, what is the result of =LARGE(A1:A5, 2)?
medium
A. 30
B. 20
C. 15
D. 25

Solution

  1. Step 1: List values and sort descending

    The values are 10, 25, 15, 30, 20. Sorted largest to smallest: 30, 25, 20, 15, 10.

  2. Step 2: Identify 2nd largest value

    The 2nd largest value is 25.

  3. Final Answer:

    25 -> Option D

  4. Quick Check:

    LARGE(..., 2) = 2nd largest = 25 [OK]
Hint: Sort values mentally to find k-th largest [OK]
Common Mistakes:
  • Choosing the largest instead of 2nd largest
  • Confusing LARGE with SMALL
  • Miscounting the position k
4. You wrote =SMALL(A1:A5, 0) to find the smallest number. What is wrong and how to fix it?
medium
A. k cannot be zero; change 0 to 1
B. Range is wrong; use A1:A6 instead
C. Use LARGE instead of SMALL
D. Add quotes around 0 like "0"

Solution

  1. Step 1: Understand k parameter rules

    In SMALL(range, k), k must be a positive integer starting at 1 to pick the smallest, 2nd smallest, etc.

  2. Step 2: Identify the error and fix

    Using 0 for k is invalid. Change it to 1 to get the smallest value.

  3. Final Answer:

    k cannot be zero; change 0 to 1 -> Option A

  4. Quick Check:

    k ≥ 1 for SMALL function [OK]
Hint: k must be 1 or more, never zero [OK]
Common Mistakes:
  • Using zero or negative k values
  • Changing range unnecessarily
  • Confusing SMALL with LARGE
5. You have sales data in B2:B11. To find the 3rd highest sale excluding zeros, which formula works correctly?
hard
A. =LARGE(B2:B11, 3)
B. =SMALL(B2:B11, 3)
C. =LARGE(IF(B2:B11>0, B2:B11), 3) (entered as array formula)
D. =LARGE(B2:B11, 4)

Solution

  1. Step 1: Understand the problem requirement

    We want the 3rd largest sale but only counting values greater than zero (exclude zeros).

  2. Step 2: Analyze each option

    =LARGE(B2:B11, 3) includes zeros, so might pick zero if it is among top 3. =LARGE(IF(B2:B11>0, B2:B11), 3) (entered as array formula) uses IF to filter out zeros and requires array formula entry, which is correct. =SMALL(B2:B11, 3) uses SMALL, wrong direction. =LARGE(B2:B11, 4) picks 4th largest, not 3rd.

  3. Final Answer:

    =LARGE(IF(B2:B11>0, B2:B11), 3) -> Option C

  4. Quick Check:

    Filter zeros with IF, then LARGE(..., 3) [OK]
Hint: Use IF to exclude zeros inside LARGE with array formula [OK]
Common Mistakes:
  • Not excluding zeros, getting wrong rank
  • Using SMALL instead of LARGE
  • Wrong k value for position
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard