LARGE and SMALL in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the LARGE function do in Excel?

The LARGE function returns the nth largest value from a list or range of numbers. For example, =LARGE(A1:A10, 2) gives the 2nd largest number in cells A1 to A10.

beginner

What does the SMALL function do in Excel?

The SMALL function returns the nth smallest value from a list or range of numbers. For example, =SMALL(A1:A10, 3) gives the 3rd smallest number in cells A1 to A10.

beginner

How do you find the largest number in a range using LARGE?

Use =LARGE(range, 1). The number 1 means the largest value. For example, =LARGE(B2:B20, 1) returns the biggest number in B2 to B20.

beginner

How do you find the smallest number in a range using SMALL?

Use =SMALL(range, 1). The number 1 means the smallest value. For example, =SMALL(C1:C15, 1) returns the smallest number in C1 to C15.

intermediate

Can LARGE and SMALL functions handle ties (same numbers)?

Yes. If there are duplicate numbers, LARGE and SMALL count them separately. For example, if the largest number appears twice, =LARGE(range, 1) and =LARGE(range, 2) can both return that same number.

What does =LARGE(A1:A5, 3) return?

A The 3rd largest number in A1:A5

B The 3rd smallest number in A1:A5

C The largest number in A1:A5

D The smallest number in A1:A5

LARGE returns the nth largest number. Here, n=3 means the 3rd largest.

Which formula finds the smallest number in a range?

A <code>=LARGE(range, 1)</code>

B <code>=MIN(range)</code>

C <code>=MAX(range)</code>

D <code>=SMALL(range, 1)</code>

SMALL(range, 1) returns the smallest number. MIN also works but is different from SMALL which can find other smallest values.

If you want the 2nd smallest number in B1:B10, which formula do you use?

A <code>=MIN(B1:B10)</code>

B <code>=LARGE(B1:B10, 2)</code>

C <code>=SMALL(B1:B10, 2)</code>

D <code>=MAX(B1:B10)</code>

SMALL(range, 2) returns the 2nd smallest number.

What happens if you use =LARGE(A1:A5, 6) but the range has only 5 numbers?

A Returns an error

B Returns 0

C Returns the smallest number

D Returns the largest number

If n is larger than the number of values, LARGE returns a #NUM! error.

Which function can find the 4th largest value in a list?

A <code>=SMALL(range, 4)</code>

B <code>=LARGE(range, 4)</code>

C <code>=MAX(range)</code>

D <code>=MIN(range)</code>

LARGE(range, 4) returns the 4th largest value.

Explain how to use the LARGE function to find the top 3 highest scores in a list.

Think about changing the second number in LARGE to get different top values.

Describe the difference between LARGE and SMALL functions and when you might use each.

One finds high values, the other finds low values.