Stop reading about formulas — write them. Thirty graded exercises across six function families, each with real sample data, a worked answer formula, the computed result, and the mistake beginners make most. Open it in Excel and fill the yellow cells.
⬇ Download the workbook .xlsx · 14 KBVLOOKUP exact & approximate, XLOOKUP with a not-found default, INDEX-MATCH reverse lookup, multi-condition lookup.
→ VLOOKUP lab · XLOOKUPExtract an email prefix, TRIM messy spaces, TEXTJOIN ignoring blanks, SUBSTITUTE, TEXT date formatting.
→ TEXT lab · TRIMYEAR/MONTH/DAY from a serial, days between dates, first day of the month, dynamic TODAY().
→ YEAR lab · TODAYNested IF grading, IFS, SWITCH equal-value dispatch, IFNA error capture, WEEKDAY weekend test.
→ IF lab · IFSSUMIF by department, COUNTIF, AVERAGEIFS multi-condition, MEDIAN vs average, LARGE k-th value.
→ SUMIF lab · MEDIANUNIQUE, FILTER, SORT, XMATCH, and UNIQUE+SORT composition. Requires Excel 365 / 2021+.
→ UNIQUE lab · FILTERMost Excel tutorials show you a formula once and move on. Retention comes from writing the formula against realistic data and seeing where it breaks. Every exercise here pairs a small office task (look up a department, clean a name list, grade a score) with the exact failure mode learners hit — a missing FALSE in VLOOKUP, a #N/A that needs IFNA, an approximate match on unsorted data.
The reference formulas were validated against a spreadsheet calculation engine; the dynamic-array family targets Excel 365 / 2021+ semantics. For the reasoning behind each function — performance benchmarks, error galleries and edge cases — see the linked labs.