fx LookupLab
Free download · CC BY 4.0

Excel Practice Workbook
30 Exercises with an Answer Key

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 KB
30 exercises6 function familiesExcel 365 / 2021+ (dynamic arrays)Answer key included

What's inside

6 Lookup

VLOOKUP exact & approximate, XLOOKUP with a not-found default, INDEX-MATCH reverse lookup, multi-condition lookup.

→ VLOOKUP lab · XLOOKUP

5 Text

Extract an email prefix, TRIM messy spaces, TEXTJOIN ignoring blanks, SUBSTITUTE, TEXT date formatting.

→ TEXT lab · TRIM

4 Date

YEAR/MONTH/DAY from a serial, days between dates, first day of the month, dynamic TODAY().

→ YEAR lab · TODAY

5 Logic

Nested IF grading, IFS, SWITCH equal-value dispatch, IFNA error capture, WEEKDAY weekend test.

→ IF lab · IFS

5 Statistical

SUMIF by department, COUNTIF, AVERAGEIFS multi-condition, MEDIAN vs average, LARGE k-th value.

→ SUMIF lab · MEDIAN

5 Dynamic arrays

UNIQUE, FILTER, SORT, XMATCH, and UNIQUE+SORT composition. Requires Excel 365 / 2021+.

→ UNIQUE lab · FILTER

How to use it

  1. Open the workbook in Excel. Each family is its own worksheet; exercises are numbered.
  2. Write your formula in the yellow “你的答案 / your answer” cell. The green cell next to it computes the correct reference result automatically.
  3. Compare your result to the green cell. The 参考公式 column shows the worked formula; the 常见错误 note flags the most common beginner mistake.
  4. Want instant feedback while you type? Each exercise links to a live, in-browser lab at formulalab.dev — edit a cell and watch the result update, no Excel needed.

Why these exercises

Most 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.

License — CC BY 4.0. Free to use in teaching, training, courses and articles. Please keep attribution: “Exercises by LookupLab (formulalab.dev)” with a link back to this page. That attribution link is the only thing we ask in return.