Sorting & Filtering with Formulas: Top-N Lists, Dynamic Rankings & Extracts (No FILTER Needed)
Rank with ties and tie-breakers, build top and bottom lists with LARGE and SMALL, extract matching rows with a k-th match helper, pull distinct lists and sort text with COUNTIF "<" ranks, then finish with a dynamic leaderboard and chart: 8 hands-on labs with classic formulas that work in every Excel version.
The first lesson is free — no account needed.
About this course
A course on sorting and filtering with formulas for anyone whose Excel has no SORT, FILTER or UNIQUE, or whose reports must work in every version, built on one fictional outdoor retailer. You rank sales reps with RANK.EQ, see what it does with ties and turn it into a unique rank with a running COUNTIF tie-breaker, build a top 5 list with LARGE and INDEX/MATCH and fix the duplicate names that ties cause, list the slowest sellers with SMALL while skipping zeros, extract the matching rows for one region with a k-th match helper, pull rows that meet two conditions with a control total, extract a distinct customer list with totals, sort text A–Z with COUNTIF "<" ranks and a two-key sort and finish with a dynamic top-N leaderboard and bar chart. The first lab is free.
Lessons
- RANK.EQ, Ties and a Unique Rank with a COUNTIF Tie-BreakerFree
- A Top-5 List with LARGE + INDEX/MATCH (and Why Ties Break It) Pro
- Bottom-N with SMALL: Slow Sellers, Ties and Skipping Zeros Pro
- Extract Matching Rows: a k-th Match Helper with a Running COUNTIF Pro
- Multi-Criteria Extracts: AND Conditions, a Match Count and a Control Total Pro
- Distinct Lists: First-Occurrence Flags, a Running Count and Totals per Customer Pro
- Sorting Text with Formulas: COUNTIF "<" Ranks and a Two-Key Sort Pro
- Capstone: A Dynamic Top-N Leaderboard with a Bar Chart Pro