Challenge 2: Building the Dashboard — Solution Walkthrough

Dashboard sheet, columns A-D, one row per category:

  A: Category      (typed once per category, e.g. Groceries)
  B: Monthly Limit — =XLOOKUP(A2, Budget[Category], Budget[MonthlyLimit], "No budget set")
  C: Actual Spend   — pulled from a PivotTable (see below)
  D: Status         — =IF(C2>B2, "Over Budget", "OK")

Getting the Actual Spend figures (Step 5):

  1. Click inside the Transactions table, Insert > PivotTable, place
     it on a new worksheet (e.g. "PivotData").
  2. Drag Category into Rows, Amount into Values (defaults to Sum).
  3. Either read the PivotTable's totals directly, or reference its
     cells from column C on the Dashboard (e.g. C2 pointing at the
     matching row of the PivotTable's own output).

Conditional formatting rule (Step 7):

  1. Select A2:D(last row) on the Dashboard.
  2. Home > Conditional Formatting > New Rule > "Use a formula to
     determine which cells to format."
  3. Formula: =$C2>$B2
  4. Set the format (e.g. red fill) and click OK.

Expected result:

Any row where Actual Spend (column C) exceeds Monthly Limit (column
B) is highlighted, and its Status column reads "Over Budget"; every
other row stays unformatted and reads "OK."

Explanation:

The conditional formatting formula =$C2>$B2 locks the COLUMNS (C and
B) but leaves the ROW relative, exactly the mixed-reference pattern
from Chapter 3 and its reuse in Chapter 6 — this is what lets the
same one rule, entered once for row 2, correctly check EVERY row's
own Actual vs. its own Limit once applied across the whole A2:D
selection, rather than every row comparing against row 2's numbers
specifically.