Spreadsheet Engineering — AfrexAI

> Build bulletproof spreadsheets: financial models, dashboards, data systems, and automation. Platform-agnostic methodology for Google Sheets, Excel, and LibreOffice.

Quick Health Check

Score your spreadsheet /16:

| Signal | Healthy | Sick | |---|---|---| | Named ranges for all key inputs | ✅ Uses named ranges | ❌ Raw cell references everywhere | | Inputs separated from calculations | ✅ Clear input section | ❌ Hardcoded values in formulas | | No circular references | ✅ Clean dependency chain | ❌ Iterative calculation warnings | | Documentation/comments exist | ✅ README sheet + cell notes | ❌ "What does this formula do?" | | Error handling in formulas | ✅ IFERROR/IFNA wrapping | ❌ #REF! #N/A scattered everywhere | | Consistent formatting | ✅ Style guide followed | ❌ Random fonts, colors, sizes | | Version history/backup | ✅ Named versions + changelog | ❌ "Final_v3_REAL_final.xlsx" | | Data validation on inputs | ✅ Dropdowns + range constraints | ❌ Free-text in structured fields |

Score: 0-4 🔴 rebuild | 5-8 🟡 refactor | 9-12 🟢 optimize | 13-16 🔵 production-grade

---

Phase 1: Architecture & Planning

Spreadsheet Strategy Brief

spreadsheet_brief:
  name: "[Descriptive Name]"
  purpose: "[What decision does this support?]"
  owner: "[Who maintains this]"
  audience: "[Who uses this — technical level]"
  update_frequency: "[Real-time / Daily / Weekly / Monthly / Ad-hoc]"
  data_sources:
    - source: "[Where data comes from]"
      method: "[Manual / Import / API / IMPORTRANGE / Power Query]"
      refresh: "[How often]"
  outputs:
    - "[Dashboard / Report / Export / Decision support]"
  complexity_tier: "[Simple / Standard / Complex / Enterprise]"
  platform: "[Google Sheets / Excel / Both]"
  kill_criteria:
    - "If >50 users need simultaneous editing → move to database"
    - "If >100K rows → move to database or BI tool"
    - "If requires audit trail → move to proper system"

Complexity Tier Guide

| Tier | Rows | Sheets | Users | Formulas | Example | |---|---|---|---|---|---| | Simple | <1K | 1-3 | 1-3 | Basic | Budget tracker, checklist | | Standard | 1K-10K | 3-8 | 3-10 | Intermediate | Financial model, project tracker | | Complex | 10K-50K | 8-15 | 10-30 | Advanced | Multi-dept dashboard, CRM | | Enterprise | 50K+ | 15+ | 30+ | Expert | Data warehouse substitute (🚩 migrate) |

When NOT to Use a Spreadsheet

| Scenario | Better Tool | |---|---| | >100K rows of data | Database (PostgreSQL, SQLite) | | >10 concurrent editors | Web app or Airtable | | Complex relational data (3+ entity types) | Database + app | | Needs audit trail / compliance | Purpose-built system | | Real-time data processing | ETL pipeline + BI tool | | Version-controlled code logic | Actual code (Python, JS) |

Rule: Spreadsheets are prototyping tools that become production systems by accident. Know when to graduate.

---

Phase 2: Sheet Architecture

Recommended Structure

📊 Workbook
├── 📋 README          — Purpose, instructions, changelog
├── 📊 Dashboard       — Charts, KPIs, summary (output only)
├── ⚙️ Config          — Settings, parameters, dropdowns
├── 📥 Data_Input      — Raw data entry or imports
├── 🔧 Calculations    — All formulas and transformations
├── 📈 Analysis        — Pivot tables, scenarios, what-if
├── 📤 Output          — Formatted reports for export/print
└── 🗄️ Reference       — Lookup tables, constants, mappings

7 Architecture Rules

1. One direction of flow — Data flows left→right or top→bottom. Never circular.
2. Inputs separate from calculations — NEVER hardcode numbers in formulas. Use named ranges.
3. One fact in one place — If a value is used in 3 places, define it once and reference it.
4. Color code by purpose — Blue = input, Black = formula, Green = linked from other sheet, Red = warning.
5. Freeze panes on every data sheet — Header row and label columns always visible.
6. Protect formula cells — Lock everything except input cells. Prevent accidental overwrites.
7. README sheet is mandatory — Every workbook starts with purpose, instructions, and changelog.

Naming Conventions

Sheets:    PascalCase — Dashboard, Raw_Data, Config
Named Ranges: SCREAMING_SNAKE — TAX_RATE, START_DATE, REVENUE_TARGET
Tabs:      Prefix with emoji or number for sort order — 01_Dashboard, 02_Config
Files:     YYYY-MM-DD_Description_vX.xlsx

Color Coding Standard

| Color | Meaning | When to Use | |---|---|---| | 🔵 Light blue background | User input cell | Editable fields | | ⬛ Black text | Formula/calculated | Auto-populated cells | | 🟢 Green text | Linked from other sheet | Cross-sheet references | | 🔴 Red text/background | Warning/error | Validation failures, negative values | | 🟡 Yellow background | Assumption | Key assumptions that drive the model | | ⬜ Grey background | Reference/locked | Constants, lookup tables |

---

Phase 3: Formula Engineering

Formula Complexity Levels

| Level | Techniques | Example | |---|---|---| | L1 Basic | SUM, AVERAGE, COUNT, IF, CONCATENATE | =SUM(B2:B100) | | L2 Intermediate | VLOOKUP/XLOOKUP, SUMIFS, INDEX/MATCH, TEXT | =XLOOKUP(A2,Ref!A:A,Ref!B:B) | | L3 Advanced | ARRAYFORMULA, QUERY, INDIRECT, nested IFs | =QUERY(Data!A:F,"SELECT A,SUM(F) GROUP BY A") | | L4 Expert | LAMBDA, MAP/REDUCE, LET, dynamic arrays, MAKEARRAY | =LET(data,A2:A100,filtered,FILTER(data,data>0),SORT(filtered)) |

Essential Formula Patterns

Lookup — Always Prefer XLOOKUP/INDEX-MATCH Over VLOOKUP

❌ VLOOKUP (fragile — breaks when columns inserted):
=VLOOKUP(A2, Data!A:D, 4, FALSE)

✅ XLOOKUP (Excel 365 / Google Sheets):
=XLOOKUP(A2, Data!A:A, Data!D:D, "Not Found")

✅ INDEX/MATCH (universal — works everywhere):
=INDEX(Data!D:D, MATCH(A2, Data!A:A, 0))

Multi-Criteria Lookup

=XLOOKUP(1, (Data!A:A=B2)*(Data!B:B=C2), Data!D:D, "Not Found")

Or INDEX/MATCH array (Ctrl+Shift+Enter in older Excel):
=INDEX(Data!D:D, MATCH(1, (Data!A:A=B2)*(Data!B:B=C2), 0))

Conditional Aggregation

Single condition:
=SUMIF(Category, "Sales", Amount)

Multiple conditions:
=SUMIFS(Amount, Category, "Sales", Region, "US", Date, ">="&DATE(2025,1,1))

Count with conditions:
=COUNTIFS(Status, "Active", Score, ">80")

Average with conditions:
=AVERAGEIFS(Score, Department, "Engineering", Status, "Active")

Date Calculations

Working days between dates:
=NETWORKDAYS(Start, End, Holidays)

Add working days:
=WORKDAY(Start, 10, Holidays)

Month-end date:
=EOMONTH(A2, 0)

Quarter from date:
=ROUNDUP(MONTH(A2)/3, 0)

Fiscal year (Apr-Mar):
=IF(MONTH(A2)>=4, YEAR(A2), YEAR(A2)-1)

Text Manipulation

Extract domain from email:
=MID(A2, FIND("@",A2)+1, LEN(A2))

Proper case with exceptions:
=PROPER(SUBSTITUTE(LOWER(A2)," llc"," LLC"))

Clean messy data:
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

Dynamic Arrays (Excel 365 / Google Sheets)

FILTER:
=FILTER(Data, Data[Status]="Active", Data[Amount]>1000)

SORT:
=SORT(FILTER(Data, Data[Region]="US"), 3, -1)

UNIQUE:
=UNIQUE(Data[Category])

SEQUENCE:
=SEQUENCE(12, 1, DATE(2025,1,1), 30)  — 12 monthly dates

Google Sheets QUERY (Power Feature)

Basic aggregation:
=QUERY(Data!A:F, "SELECT A, SUM(F) WHERE B='Active' GROUP BY A ORDER BY SUM(F) DESC LABEL SUM(F) 'Total Revenue'")

Date filtering:
=QUERY(Data!A:F, "SELECT A, B, F WHERE C >= date '"&TEXT(B1,"yyyy-MM-dd")&"' ORDER BY F DESC LIMIT 10")

Pivot-style:
=QUERY(Data!A:F, "SELECT A, SUM(F) GROUP BY A PIVOT B")

LET for Readable Complex Formulas

=LET(
  revenue, SUMIFS(Sales!D:D, Sales!A:A, A2),
  costs, SUMIFS(Costs!D:D, Costs!A:A, A2),
  margin, (revenue - costs) / revenue,
  IF(revenue=0, "No Data",
    IF(margin > 0.3, "✅ Healthy",
      IF(margin > 0.1, "⚠️ Watch", "🔴 Critical")))
)

LAMBDA (Custom Functions)

...