Sorting & Filtering with Formulas: Top-N Lists, Dynamic Rankings & Extracts (No FILTER Needed)

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Rank Fernhill Outdoor's twelve sales reps by Q3 sales, see what RANK.EQ does with ties, turn the rank into a unique 1–12 rank with a running COUNTIF and break ties by a second criterion.
• Build a self-updating Top-5 sales list: LARGE returns the k-th largest value, INDEX/MATCH the rep. Then you see the duplicate-name bug with ties and fix it with a unique-rank helper.
• List Fernhill's five slowest-selling products with SMALL, build an ascending unique rank for the names and skip the new products that have not sold yet.
• Build a formula "filter" for one region: a helper column numbers the matching orders 1, 2, 3 …, INDEX/MATCH pulls them into a report block, IFERROR keeps the empty rows blank and a formula rule highlights the matching rows in the source.
• Extract all open orders above a minimum amount: the helper numbers rows that pass both conditions, a COUNTIFS shows how many there are, INDEX/MATCH pulls them out and a SUMIFS control total proves nothing was lost.
• Turn a September order log with repeated customers into a list of distinct customers: flag the first occurrence, number the distinct values with a running count, extract them with INDEX/MATCH and add a total per customer.
• Sort Fernhill's product list A–Z without the Sort button: a COUNTIF "
• Build the sales-challenge leaderboard: a unique rank on the total points, a Top-N list whose length is set in one cell, the names from the rank, and a bar chart of the top five.