Excel practice · Data Tools

RANK, LARGE, SMALL, MEDIAN & MODE practice exercises

6 graded problems with realistic data, from easy to medium.

Five ways to ask "how does this compare to the rest of the set": a position, a specific-place value, or the group's middle or most common value. The 6 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.

New to the function? Read the RANK, LARGE, SMALL, MEDIAN & MODE guide first: syntax, a worked example and the mistakes people make.

In this set

  1. Rank a score on a leaderboard
  2. Rank race times, fastest first
  3. Find the 2nd-highest value in a list
  4. Find the 2nd-lowest value in a list
  5. Find the median instead of being misled by an outlier
  6. Find the most common rating

The exercises

1. Rank a score on a leaderboard Easy

Five players' scores are listed below. Find the rank of the player in B4 (1st place = the highest score).

ABC
1PlayerScore
2Mia82
3Omar95
4Priya71?
5Jin88
6Tasha60

Enter a formula in C4.

Show a hint

RANK(number, ref) ranks highest-first by default: =RANK(B4,B2:B6).

RANK is the formula behind every leaderboard, standings table, or sales ranking: one function, recalculates automatically as scores change.

2. Rank race times, fastest first Medium

In a race, the lowest time wins. Find the rank of the runner in B4 where 1st place is the fastest (lowest) time.

ABC
1RunnerTime (min)
2Alex24.1
3Beth22.8
4Cole25.6?
5Dana23
6Eli26.2

Enter a formula in C4.

Show a hint

RANK's optional 3rd argument flips the order; any nonzero value ranks lowest-first: =RANK(B4,B2:B6,1).

Whether "best" means highest or lowest depends entirely on what you're measuring: that 3rd argument is how RANK handles both without a different formula.

3. Find the 2nd-highest value in a list Easy

Find the 2nd-highest monthly sales total in the range below, without sorting the data.

AB
1MonthSales
2Jan4200
3Feb5100
4Mar3800
5Apr6400
6May4900
72nd highest?

Enter a formula in B7.

Show a hint

LARGE(range, k) returns the k-th largest value: =LARGE(B2:B6,2).

LARGE/SMALL answer "what's the Nth-best?" directly: no need to sort the sheet or build a helper column just to find a runner-up.

4. Find the 2nd-lowest value in a list Easy

Find the 2nd-lowest expense in the list below, without sorting the data.

AB
1CategoryExpense
2Rent1200
3Utilities180
4Supplies95
5Software240
6Travel410
72nd lowest?

Enter a formula in B7.

Show a hint

SMALL(range, k) returns the k-th smallest value: =SMALL(B2:B6,2).

SMALL is LARGE's mirror image: together they let you pull any rank, from the top or the bottom, out of a list.

5. Find the median instead of being misled by an outlier Medium

Five houses on a street just sold. One sale was a mansion that skews the average way up. Find the median sale price: a better sense of what's "typical" here than the average would give.

AB
1HouseSale price
21310000
32295000
43340000
541850000
65320000
7Median?

Enter a formula in B7.

Show a hint

MEDIAN(range) sorts the values and returns the middle one: =MEDIAN(B2:B6).

A single huge outlier (that mansion) drags AVERAGE way up, but barely moves MEDIAN; whenever a dataset might have outliers, MEDIAN is the more honest "typical value."

6. Find the most common rating Easy

Customers rated a product from 1 to 5 in the survey below. Find the rating that came up most often.

AB
1ReviewRating
214
325
433
544
654
762
8Most common rating?

Enter a formula in B8.

Show a hint

MODE(range) returns whichever value appears most often in the range: =MODE(B2:B7).

RANK, LARGE, SMALL, and MEDIAN all describe where a value sits in order; MODE answers a different question entirely: not where something ranks, but what shows up the most.

Solve these in a real grid

Type your formula, get graded instantly. Your first 10 exercises are free, no signup.

Start the RANK, LARGE, SMALL, MEDIAN & MODE exercises →

More data tools practice

All Excel practice exercises · Excel test prep