<!-- skill: Excel | version: 1.0.0 | description: Advanced Excel — formulas, Power Query, PivotTables, automation | category: Python & Data -->

## Instructions
- Structure workbooks: one `README` sheet, `Data` (raw, untouched), `Calc` (formulas), `Out` (results/charts). Never mix raw data and formulas.
- Tables (`Ctrl+T`) with structured references (`=[@Amount]*[@Qty]`) instead of bare ranges — formulas survive new rows.
- XLOOKUP over VLOOKUP always (exact match default, leftward lookup, built-in not-found arg). INDEX+MATCH only for legacy files.
- Power Query for ALL imports/cleaning (CSV, web, folders): repeatable, auditable, refreshable. Never hand-clean twice.
- PivotTables for summaries + slicers for filtering; GETPIVOTDATA for dashboard cells that must not break.
- Protect: lock formula cells + sheet protection (not security, just accident-proofing); data validation dropdowns on input cells.
- Names win: named ranges (`Rates.VAT`, `FY_Start`) + `LET`/`LAMBDA` so formulas read like sentences; no magic numbers buried in cells.
- Dates are serials: never compare date-text with `=`; use real date cells, `EOMONTH`/`EDATE` for period math, `TEXT(date,"yyyy-mm")` only for display keys.
- Errors are UI: wrap user-facing formulas with `IFERROR(...,"—")`, flag `#N/A` from XLOOKUP with the `if_not_found` arg instead of hiding all errors.
- Conditional formatting sparingly: data bars for magnitudes, red fill for overdue/breaches, never rainbow sheets — every format must mean something documented in a legend.
- Calculation hygiene: `Manual` calc mode for monster workbooks while building, back to `Automatic` before handoff; `FORMULATEXT` audit column on critical sheets.
## Examples
- Monthly P&L: Power Query folder combine → Pivot by month/category → dashboard with slicers + sparklines.
- Invoice tracker: Table + XLOOKUP client details + SUMIFS aging buckets + conditional-format overdue.
- Headcount plan: LAMBDA `COST(role, months)` reused across scenarios; data table (`What-If`) for best/base/worst.
## Workflows
1. Define outputs first (what decisions this workbook drives).
2. Import via Power Query, clean steps recorded, load to Table.
3. Calc layer with named ranges + LET/LAMBDA for repeated logic.
4. Pivot/dashboard layer, print setup (fit-to-page, headers) if it goes to paper.
5. Document: README sheet with refresh instructions + version date.
6. Handoff test: a colleague refreshes + filters without asking you anything; fix every question they ask by documenting it.
## Templates
```excel
=XLOOKUP(A2, Clients[ID], Clients[Name], "Unknown")
=LET(m, MONTH(Tbl[Date]), SUMIFS(Tbl[Amount], m, 3))
=TEXTJOIN(", ", TRUE, FILTER(Tbl[Item], Tbl[Qty]=0, "none"))
=IFERROR(XLOOKUP(A2, Rates[Code], Rates[Rate], "missing"), "—")
=LAMBDA(cost, months, cost * months)(B2, C2)
```
```vba
' Refresh everything + stamp version (Developer > Macros)
Sub RefreshAll()
    ThisWorkbook.RefreshAll
    Sheets("README").Range("B1").Value = "Refreshed " & Now
End Sub
```
- Power Query M for unpivot: select id columns → Transform > Unpivot Other Columns.
- Pivot layout: Tabular + Repeat All Item Labels for export-friendly tables; turn off Grand Totals when they mislead (averages of averages).
- Print template: Page Setup fit-to-1-wide, row-1 repeat, footer `&F &D &P/&N`, print titles locked before sharing.
## Tools
- SARAH can't click Excel — it writes CSVs, reconciliation scripts (Python/pandas), and step checklists; heavy lifting via openpyxl (`pip install openpyxl`) for .xlsx generation.
- Validate CSV outputs open cleanly (UTF-8 BOM for Excel: `\ufeff` prefix).
- `pip install openpyxl` for scripted `.xlsx` builds; Power Query for everything refreshable inside Excel itself.
## Advanced
- LAMBDA library: build `fx_GST(amount)`, `fx_AgingDays(due)` once in Name Manager, reuse everywhere — version them in the README.
- Data model: load related tables to the Data Model + `RELATED()`/DAX measures (`SUMX`, `CALCULATE`) when VLOOKUP forests collapse; Power Pivot handles millions of rows.
- Reconciliation pattern: import system A + system B via Power Query → merge on key → anti-join rows for breaks → conditional-format breaks → break report sheet.
- Versioning: filename `Report_2026-10-13_v03.xlsx`, changelog table on README (date/author/change); never email "final_FINAL".
- Troubleshooting: XLOOKUP returns wrong match → duplicates in lookup array (remove or use exact+first/last mode); Pivot shows stale data → Refresh All + check source range expanded (use Tables); numbers stored as text → green triangles, `VALUE()` or Text-to-Columns; file ballooned → unused formatting (Clear Formats beyond last cell), embedded images (Compress), or full-column references (`A:A` → Table columns).
## Documentation
- Excel help: https://support.microsoft.com/excel
- Power Query M: https://learn.microsoft.com/power-query
- ExcelJet formula reference: https://exceljet.net/formulas

## Templates
```excel
=XLOOKUP(A2, Clients[ID], Clients[Name], "Unknown")
=LET(m, MONTH(Tbl[Date]), SUMIFS(Tbl[Amount], m, 3))
=TEXTJOIN(", ", TRUE, FILTER(Tbl[Item], Tbl[Qty]=0, "none"))
```
```vba
' Refresh everything + stamp version (Developer > Macros)
Sub RefreshAll()
    ThisWorkbook.RefreshAll
    Sheets("README").Range("B1").Value = "Refreshed " & Now
End Sub
```
- Power Query M for unpivot: select id columns → Transform > Unpivot Other Columns.

## Tools
- SARAH can't click Excel — it writes CSVs, reconciliation scripts (Python/pandas), and step checklists; heavy lifting via openpyxl (`pip install openpyxl`) for .xlsx generation.
- Validate CSV outputs open cleanly (UTF-8 BOM for Excel: `\ufeff` prefix).

## Documentation
- Excel help: https://support.microsoft.com/excel
- Power Query M: https://learn.microsoft.com/power-query