🏷️ The Scenario (one sheet)
You’re a sales analyst preparing a quick “actuals & issues” roll-up for the product team. You have transaction-level data: Date, Region, SalesRep, Product, Units, UnitPrice, Discount%, Returned (Yes/No)
📋 Sample Data
| Date | Region | SalesRep | Product | Units | UnitPrice | Disc% | Returned |
|---|---|---|---|---|---|---|---|
| 2025-10-01 | West | Amy | ProDesk | 3 | 450 | 0.05 | No |
| 2025-10-02 | East | Rahul | ProMouse | 10 | 25 | 0 | No |
| 2025-10-02 | West | Amy | ProDesk | 1 | 450 | 0.10 | Yes |
| 2025-10-03 | North | Rita | ProKey | 5 | 70 | 0.02 | No |
| 2025-10-04 | East | Rahul | ProMouse | 4 | 25 | 0.00 | No |
🔢 Quick totals (what we want)
- Total revenue (after discount, excluding returns)
- Unique products sold
- Top performing rep
- Clean list of SKUs and units for dispatch
- Small summary metric series created via LAMBDA for reuse
🚀 Top 10 functions used in this single workflow
1) XLOOKUP — flexible, modern lookup
Example: Pull unit price or category for ‘ProDesk’. XLOOKUP is robust against column order, supports not-found defaults, and exact or approximate matches.
2) FILTER — dynamic row filtering
Example: Create an array of valid transactions (exclude returns). Useful for feeding downstream formulas (SUM, MAP, etc.).
3) UNIQUE – list unique SKUs or reps
Example: Returns a clean list of products sold (no duplicates) for packing lists.
4) LET – name intermediate calculations
Example: name `net` and re-use it in SUM or other logic without recalculation. Great for clarity and performance.
5) LAMBDA – reusable custom metric
Example: Define `NetRevenue` once and call it: =NetRevenue(3,450,0.05,"No"). Use in LET, MAP, REDUCE for consistent logic.
6) MAP – apply a LAMBDA across rows (vectorized)
Example: returns a column of net revenues. Feed into SUM or REDUCE. It replaces the need for helper columns.
7) REDUCE – accumulate a single result (e.g., total revenue)
Example: combine MAP output into a single total revenue figure. REDUCE is useful when accumulation needs custom logic (taxes, thresholds).
8) SCAN – running totals / cumulative metrics
Example: show day-by-day running total for dashboard trend. Works with arrays without helper columns.
9) TEXTSPLIT – split composite text fields (e.g., “Region|City”)
Example: If incoming data has “West|Pune”, TEXTSPLIT turns that into separate columns – useful for quick normalization.
10) SEQUENCE (with VSTACK/HSTACK) – build dynamic arrays
Example: generate a week of dates for joining with sales totals. Pair with VSTACK/HSTACK to assemble multi-row outputs.
🧭 How these fit together (short flow)
- TEXTSPLIT to clean incoming fields.
- FILTER to select valid rows (exclude returns).
- MAP + LAMBDA to compute row-level net revenue.
- REDUCE to total revenue, SCAN for cumulative trend.
- UNIQUE to create dispatch lists, XLOOKUP to enrich with product metadata, SEQUENCE to create date scaffolding.
🛠️ Implementation snippet (compact)
⚠️ Common pitfalls
- Remember Excel arrays spill – reference entire spill ranges (e.g., A2#) when reusing results.
- Use exact match modes in XLOOKUP unless you intentionally want approximations.
- When saving LAMBDA names, test with sample inputs first.