Challenge 2: Extracting Grocery Transactions with FILTER — Solution Walkthrough

Formula:
  =FILTER(A2:C20, B2:B20="Groceries", "No groceries this period")

Walkthrough:
  1. A2:C20 is the full range being filtered (Date, Category, Amount).
  2. B2:B20="Groceries" is the condition — only rows where column B
     (Category) reads exactly "Groceries" pass through.
  3. If no rows match at all, the third argument's text,
     "No groceries this period", is shown instead of the #CALC!
     error FILTER would otherwise display.

What happens when a new "Groceries" row is added to the source data:

The formula's spilled result updates AUTOMATICALLY the next time
Excel recalculates — which happens continuously as you edit the
sheet, with no manual step required. The moment the new row is typed
into the Date/Category/Amount source range (assuming it falls inside
A2:C20, or the range is later extended to include it), FILTER
re-evaluates the condition against the now-larger data set and the
new matching row appears in the spill range on its own.

Notes:
- This is a genuinely different behaviour from Excel Fundamentals
  Chapter 9's PivotTables, which do NOT update automatically and
  require an explicit Refresh after the source data changes — FILTER,
  like every dynamic array formula, recalculates live, the same way
  an ordinary formula like =SUM(A1:A10) always has.
- If the new row falls OUTSIDE the fixed A2:C20 range entirely (e.g.
  added at row 25), the formula would need its range argument
  manually extended — building the source data as an Excel Table
  (Chapter 7) and referencing it with a structured reference instead
  of a fixed A2:C20 address avoids this exact limitation.