openclaw/1kalin/afrexai-spreadsheet-engineering

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)

Named LAMBDA (define in Name Manager / named ranges):
FISCAL_QUARTER = LAMBDA(date, "FY"&IF(MONTH(date)>=4,YEAR(date),YEAR(date)-1)&" Q"&ROUNDUP(MOD(MONTH(date)+8,12)/3,0))

MAP with LAMBDA:
=MAP(A2:A100, LAMBDA(x, PROPER(TRIM(x))))

10 Formula Rules

1. NEVER hardcode values — Use named ranges or a Config sheet
2. Wrap external lookups in IFERROR — =IFERROR(XLOOKUP(...), "Not Found")
3. Use LET for formulas >100 chars — Readable, debuggable, faster
4. Prefer XLOOKUP over VLOOKUP — More flexible, no column counting
5. One formula per cell — Don't nest 5+ functions. Break into helper columns.
6. Comment complex formulas — Use cell notes or a documentation column
7. Test with edge cases — Empty cells, zeros, dates before 1900, text in number fields
8. Avoid INDIRECT for performance — It's volatile (recalculates every time)
9. Use structured references in tables — =SUM(Table1[Amount]) not =SUM(D:D)
10. Keep formulas auditable — Someone else (or future you) must understand them

---

Phase 4: Data Validation & Quality

Input Validation Checklist

| Data Type | Validation | Implementation | |---|---|---| | Date | Date range | Data validation: between START and END | | Currency | Number ≥ 0 | Data validation: decimal ≥ 0, format $#,##0.00 | | Percentage | 0-100 or 0-1 | Data validation: decimal between 0 and 1 | | Category | Dropdown list | Data validation: list from Reference sheet | | Email | Contains @ | Custom: =ISNUMBER(FIND("@",A2)) | | Phone | Length check | Custom: =AND(LEN(A2)>=10, LEN(A2)<=15) | | Required field | Not blank | Custom: =LEN(TRIM(A2))>0 | | ID/Code | Unique + format | Custom: =AND(COUNTIF(A:A,A2)=1, LEN(A2)=8) |

Data Cleaning Pipeline

Step 1: Remove whitespace
=TRIM(CLEAN(A2))

Step 2: Standardize case
=PROPER(A2) or =UPPER(A2)

Step 3: Remove duplicates
Use Remove Duplicates tool or UNIQUE()

Step 4: Fix dates
=DATEVALUE(TEXT(A2,"YYYY-MM-DD"))

Step 5: Validate
=IF(AND(A2>0, A2<1000000, ISNUMBER(A2)), "✅", "❌ Check")

Conditional Formatting Rules (Priority Order)

1. 🔴 Errors — Any cell with #REF!, #N/A, #VALUE! → Red background
2. 🟡 Warnings — Values outside expected range → Yellow background
3. 🟢 Positive — On-target metrics → Green text
4. 📊 Data bars — Numeric ranges → Proportional bars
5. 🎯 Icons — Status indicators → Traffic light icon sets

---

Phase 5: Financial Modeling

Model Architecture

📊 Financial Model
├── 📋 Cover          — Model name, version, date, author
├── ⚙️ Assumptions    — ALL inputs here (blue cells), scenarios
├── 📊 Revenue        — Revenue build-up by product/segment
├── 📊 COGS           — Cost of goods/services
├── 📊 OpEx           — Operating expenses by category
├── 📊 P&L            — Income statement (auto-calculated)
├── 📊 Balance_Sheet  — Assets, liabilities, equity
├── 📊 Cash_Flow      — Operating, investing, financing
├── 📈 DCF            — Discounted cash flow valuation
├── 📈 Scenarios      — Bull/Base/Bear cases
├── 📊 KPIs           — Key metrics dashboard
└── 📊 Charts         — Visualizations

Revenue Model Patterns

saas_revenue:
  mrr_start: "=PREVIOUS_MONTH_MRR"
  new_mrr: "=NEW_CUSTOMERS * ARPU"
  expansion_mrr: "=EXISTING * EXPANSION_RATE / 12"
  contraction_mrr: "=EXISTING * CONTRACTION_RATE / 12"
  churn_mrr: "=EXISTING * CHURN_RATE / 12"
  mrr_end: "=MRR_START + NEW + EXPANSION - CONTRACTION - CHURN"
  arr: "=MRR_END * 12"

unit_economics:
  cac: "=TOTAL_SALES_MARKETING / NEW_CUSTOMERS"
  ltv: "=ARPU / MONTHLY_CHURN_RATE"
  ltv_cac_ratio: "=LTV / CAC  # Target: >3.0"
  cac_payback_months: "=CAC / ARPU  # Target: <12"

Scenario Analysis Template

=SWITCH(SCENARIO_SELECTOR,
  "Bull", Assumptions!B2 * 1.3,
  "Base", Assumptions!B2,
  "Bear", Assumptions!B2 * 0.7,
  Assumptions!B2)

Or with CHOOSE:
=CHOOSE(SCENARIO_INDEX, BEAR_VALUE, BASE_VALUE, BULL_VALUE)

Sensitivity Analysis (Data Table)

Two-variable data table:
- Row input: Growth Rate (10%, 15%, 20%, 25%, 30%)
- Column input: Churn Rate (2%, 3%, 5%, 7%, 10%)
- Output cell: NPV or IRR
- Select range → Data → What-If Analysis → Data Table

Common Financial Formulas

NPV: =NPV(DISCOUNT_RATE, CF1:CF10) + INITIAL_INVESTMENT
IRR: =IRR(CF_RANGE, guess)
XIRR: =XIRR(CF_VALUES, CF_DATES)  — irregular cash flows
PMT: =PMT(RATE/12, NPER*12, -PV)  — loan payment
Compound growth: =FV * (1 + RATE)^YEARS
CAGR: =(END_VALUE/START_VALUE)^(1/YEARS) - 1
Break-even units: =FIXED_COSTS / (PRICE - VARIABLE_COST)

---

Phase 6: Dashboard Design

Dashboard Layout

┌─────────────────────────────────────────────────┐
│  📊 Dashboard Title              Period: [Dropdown] │
│  Last Updated: [Auto]            Filter: [Dropdown] │
├──────────┬──────────┬──────────┬──────────────────┤
│  KPI 1   │  KPI 2   │  KPI 3   │  KPI 4           │
│  $1.2M   │  45%     │  128     │  $47             │
│  ▲ 12%   │  ▼ -3%   │  ▲ 8%   │  ● Flat          │
├──────────┴──────────┴──────────┴──────────────────┤
│                                                     │
│  [Primary Chart — Revenue Trend]                   │
│                                                     │
├─────────────────────┬───────────────────────────────┤
│  [Secondary Chart]  │  [Table / Top Items]          │
│  [Category Split]   │  [Ranked List]                │
└─────────────────────┴───────────────────────────────┘

KPI Card Formula Pattern

Current value:  =SUMIFS(Data!E:E, Data!A:A, ">="&PERIOD_START, Data!A:A, "<="&PERIOD_END)
Previous value: =SUMIFS(Data!E:E, Data!A:A, ">="&PREV_START, Data!A:A, "<="&PREV_END)
Change %:       =(CURRENT - PREVIOUS) / ABS(PREVIOUS)
Indicator:      =IF(CHANGE>0.05, "▲", IF(CHANGE<-0.05, "▼", "●"))
Display:        =INDICATOR & " " & TEXT(ABS(CHANGE), "0.0%")

Chart Selection Guide

| Data Pattern | Best Chart | Avoid | |---|---|---| | Trend over time | Line chart | Pie chart | | Part of whole | Stacked bar or donut | 3D pie | | Comparison | Horizontal bar | Radar chart | | Distribution | Histogram | Line chart | | Relationship | Scatter plot | Bar chart | | KPI vs target | Bullet chart or gauge | Complex chart | | Geographic | Heat map or filled map | Bar chart |

7 Chart Rules

1. Title = Insight, not description. "Revenue grew 23% in Q3" not "Q3 Revenue Chart"
2. Start Y-axis at zero for bar charts. Line charts can truncate with clear labeling.
3. Max 5-7 data series per chart. Use "Other" category for the rest.
4. Remove chartjunk — No 3D effects, gradient fills, excessive gridlines.
5. Use consistent colors — Same category = same color across all charts.
6. Label directly on chart where possible. Minimize legend lookups.
7. Sort meaningfully — By value (largest→smallest) or chronologically. Never alphabetically unless it's the only logical order.

Interactive Dashboard Controls

Filter by dropdown:
1. Config sheet: Data validation dropdown for Region, Period, Category
2. Dashboard formulas use dropdown value:
   =SUMIFS(Data!E:E, Data!C:C, CONFIG_REGION, Data!A:A, ">="&CONFIG_START)

Sparklines (in-cell mini charts):
=SPARKLINE(B2:M2, {"charttype","line"; "color","#2563eb"; "linewidth",2})

---

Phase 7: Data Import & Integration

Import Method Selection

| Source | Method | Refresh | |---|---|---| | CSV/Excel file | Manual import / Power Query | Manual | | Google Sheets (other) | IMPORTRANGE | Auto (varies) | | Web page table | IMPORTHTML / Power Query | Auto / manual | | API / JSON | IMPORTDATA / Apps Script / Power Query | Scheduled | | Database | Power Query / ODBC | Scheduled | | Another sheet (same workbook) | Direct reference | Real-time |

Google Sheets Import Functions

From another spreadsheet:
=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:D100")

From web page (table):
=IMPORTHTML("url", "table", 1)

From CSV:
=IMPORTDATA("csv_url")

From XML/RSS:
=IMPORTXML("url", "//item/title")

Excel Power Query Patterns

1. Data → Get Data → From [Source]
2. Transform in Power Query Editor
3. Close & Load (to table or connection only)

Essential transforms:
- Remove columns → Right-click header → Remove
- Filter rows → Click filter arrow
- Split column → Transform → Split Column
- Unpivot → Select ID columns → Unpivot Other Columns
- Merge queries → Home → Merge (= VLOOKUP but better)
- Append queries → Home → Append (= UNION)

IMPORTRANGE Best Practices

Rules:
1. Authorize on first use (one-time popup)
2. Use named ranges in source spreadsheet
3. Wrap in IFERROR for graceful failures
4. Minimize imported range — don't import entire sheets
5. Cache results if auto-refresh causes slowness

Pattern:
=IFERROR(
  IMPORTRANGE(SOURCE_URL, "Data!A1:D"&SOURCE_ROW_COUNT),
  "⚠️ Connection failed — check source spreadsheet access"
)

---

Phase 8: Automation & Scripts

Google Apps Script Essentials

// Auto-populate timestamp on edit
function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  if (sheet.getName() === "Data" && e.range.getColumn() >= 2) {
    sheet.getRange(e.range.getRow(), 1).setValue(new Date());
  }
}

// Email report on schedule (set up trigger)
function sendWeeklyReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dashboard = ss.getSheetByName("Dashboard");
  const kpi1 = dashboard.getRange("B2").getDisplayValue();
  const kpi2 = dashboard.getRange("C2").getDisplayValue();
  
  MailApp.sendEmail({
    to: "[email protected]",
    subject: `Weekly Report — ${Utilities.formatDate(new Date(), "GMT", "MMM dd")}`,
    htmlBody: `<h2>Weekly KPIs</h2><p>Revenue: ${kpi1}</p><p>Growth: ${kpi2}</p>`
  });
}

// Auto-archive rows older than 90 days
function archiveOldRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const data = ss.getSheetByName("Data");
  const archive = ss.getSheetByName("Archive");
  const cutoff = new Date();
  cutoff.setDate(cutoff.getDate() - 90);
  
  const rows = data.getDataRange().getValues();
  for (let i = rows.length - 1; i >= 1; i--) {
    if (rows[i][0] < cutoff) {
      archive.appendRow(rows[i]);
      data.deleteRow(i + 1);
    }
  }
}

Excel VBA Essentials

' Auto-format new entries
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Application.EnableEvents = False
        Target.Offset(0, 5).Value = Now
        Application.EnableEvents = True
    End If
End Sub

' Refresh all Power Query connections
Sub RefreshAllData()
    ThisWorkbook.RefreshAll
    MsgBox "All data refreshed at " & Now
End Sub

Automation Decision Guide

| Task | Google Sheets | Excel | |---|---|---| | On-edit timestamp | Apps Script onEdit | VBA Worksheet_Change | | Scheduled email | Apps Script + trigger | Power Automate | | Data refresh | Apps Script + trigger | Power Query + schedule | | PDF export | Apps Script | VBA + SaveAs | | Cross-system sync | Apps Script + API | Power Automate / VBA | | Custom functions | Apps Script CUSTOM_FUNCTION | VBA UDF or LAMBDA |

---

Phase 9: Performance Optimization

Performance Killers (Ranked)

| Issue | Impact | Fix | |---|---|---| | INDIRECT/OFFSET (volatile) | 🔴 Critical | Replace with INDEX/XLOOKUP | | Whole-column references (A:A) | 🔴 Critical | Use bounded ranges (A2:A1000) | | ARRAYFORMULA on huge ranges | 🟡 High | Limit range or use QUERY | | Excessive conditional formatting | 🟡 High | Reduce rules, use bounded ranges | | Too many IMPORTRANGE | 🟡 High | Consolidate, cache locally | | Unused sheets with formulas | 🟢 Medium | Delete or clear unused sheets | | Complex nested IFs | 🟢 Medium | Replace with SWITCH/IFS/XLOOKUP | | Heavy formatting (images, shapes) | 🟢 Medium | Minimize decorative elements |

Google Sheets Performance Rules

1. Keep workbook under 5M cells (ideal: <500K)
2. Limit IMPORTRANGE to <10 per workbook
3. Use QUERY instead of multiple SUMIFS when possible
4. Put ARRAYFORMULA results on a dedicated calc sheet
5. Avoid NOW()/TODAY() in frequently-recalculated areas

Excel Performance Rules

1. Use tables (Ctrl+T) for structured data — better performance than raw ranges
2. Power Query > formulas for data transformation
3. XLOOKUP > VLOOKUP > INDEX/MATCH for speed
4. Turn off auto-calculation during bulk edits: Application.Calculation = xlManual
5. Use Power Pivot for >100K rows instead of formulas

---

Phase 10: Collaboration & Governance

Access Control Strategy

| Role | Permissions | Implementation | |---|---|---| | Owner | Full control | Original creator | | Editor | Edit data, not structure | Share with edit, protect structure sheets | | Analyst | Edit inputs, view outputs | Protect all except input cells | | Viewer | View only | Share as viewer | | Commenter | View + comment | Share as commenter |

Sheet Protection Pattern

1. Protect entire workbook structure (prevent sheet add/delete/rename)
2. Protect each sheet
3. UNLOCK only input cells (blue-coded)
4. Set password for admin overrides
5. Document which cells are editable in README

Version Control

Naming: YYYY-MM-DD_ModelName_vX.Y
  X = major change (new section, restructure)
  Y = minor change (formula fix, data update)

Changelog (on README sheet):
| Date | Version | Author | Change |
|------|---------|--------|--------|
| 2025-03-15 | 2.1 | Jane | Added Q2 actuals |
| 2025-03-01 | 2.0 | John | Restructured revenue model |

Collaboration Rules

1. Never edit someone else's model without telling them
2. Use named versions before major changes (Google Sheets: File → Version history → Name current version)
3. Comment on cells — don't explain in chat, explain in the sheet
4. One editor at a time for complex formula areas — use "editing" flag cell
5. Weekly review — Check for broken references, stale data, unused sheets

---

Phase 11: Common Templates

Budget Tracker Template

Columns: Month | Category | Subcategory | Budgeted | Actual | Variance | % Variance
KPIs: Total Budget | Total Spent | Remaining | Burn Rate | Projected Year-End
Charts: Budget vs Actual (bar), Spend by Category (donut), Monthly Trend (line)
Formulas:
  Variance: =Actual - Budgeted
  % Variance: =IF(Budgeted=0, "", (Actual-Budgeted)/ABS(Budgeted))
  Burn Rate: =SUMIFS(Actual, Month, "<="&TODAY()) / (MONTH(TODAY()) * Total_Budget / 12)

Project Tracker Template

Columns: Task | Owner | Status | Priority | Start | Due | Days Left | % Complete | Notes
Status: 🔴 Blocked | 🟡 In Progress | 🟢 Complete | ⚪ Not Started
Formulas:
  Days Left: =IF(Status="🟢 Complete", "✅", MAX(0, Due-TODAY()))
  Overdue flag: =IF(AND(Status<>"🟢 Complete", Due<TODAY()), "⚠️ OVERDUE", "")
  Completion %: =COUNTIF(Status, "🟢 Complete") / COUNTA(Status)
Dashboard: Gantt-style with conditional formatting date bars

Sales Pipeline Template

Columns: Deal | Company | Stage | Amount | Probability | Weighted | Owner | Close Date | Days in Stage | Next Action
Stages: Prospect (10%) | Qualified (25%) | Proposal (50%) | Negotiation (75%) | Closed Won (100%) | Lost (0%)
Formulas:
  Weighted: =Amount * Probability
  Pipeline: =SUMIFS(Weighted, Stage, "<>"&"Lost", Stage, "<>"&"Closed Won")
  Velocity: =AVERAGE(Days_to_Close_for_Won_Deals)
Dashboard: Pipeline by stage (funnel), Forecast vs quota, Win rate trend

OKR Tracker Template

Columns: Objective | Key Result | Metric | Start | Current | Target | Score | Status
Score: =MIN(1, (Current - Start) / (Target - Start))
Status: =IF(Score>=0.7, "🟢", IF(Score>=0.4, "🟡", "🔴"))
Overall: =AVERAGE(Score) across all KRs per Objective

---

Phase 12: Quality & Maintenance

Spreadsheet Quality Rubric (0-100)

| Dimension | Weight | Scoring | |---|---|---| | Architecture | 15% | Clear sheet structure, data flow direction, README | | Formula Quality | 20% | Named ranges, error handling, no hardcoding | | Data Validation | 15% | Input constraints, dropdowns, type checking | | Visual Design | 10% | Consistent formatting, color coding, readability | | Documentation | 15% | Cell notes, README, changelog, instructions | | Performance | 10% | No volatile functions, bounded ranges, fast recalc | | Error Handling | 10% | IFERROR wrappers, validation checks, no broken refs | | Maintainability | 5% | Protected structure, clear ownership, versioned |

Monthly Maintenance Checklist

• [ ] Check for #REF! and #N/A errors across all sheets
• [ ] Verify data source connections are refreshing
• [ ] Review and update assumptions (Config sheet)
• [ ] Remove unused sheets and named ranges
• [ ] Check file size — if growing, archive old data
• [ ] Test all dropdowns and validation rules
• [ ] Update README with any changes made
• [ ] Create named version snapshot

10 Spreadsheet Killers

| Mistake | Impact | Fix | |---|---|---| | Hardcoded numbers in formulas | Can't audit or update | Named ranges + Config sheet | | No error handling | #N/A cascades break everything | IFERROR on all lookups | | Whole-column references | Slow, crashes on large data | Bounded ranges | | Circular references | Unpredictable results | Redesign calculation flow | | No documentation | "What does this formula do?" | README + cell notes | | No data validation | Garbage in = garbage out | Dropdowns + constraints | | One mega-sheet | Unmaintainable, slow | Split by function | | No backup/versions | One mistake = lost work | Named versions + exports | | Copy-paste instead of formulas | Stale data, inconsistencies | Use references/IMPORTRANGE | | Manual processes that should be automated | Error-prone, time-wasting | Scripts or scheduled refreshes |

---

Edge Cases

Migrating Excel ↔ Google Sheets

• XLOOKUP works in both (Excel 365 + Google Sheets)
• QUERY is Google Sheets only — replace with Power Query in Excel
• ARRAYFORMULA is Google Sheets — Excel uses Ctrl+Shift+Enter or dynamic arrays
• Apps Script → no Excel equivalent. Use VBA or Power Automate.
• Power Query / Power Pivot → no Google Sheets equivalent. Use QUERY or BigQuery connector.
• Test all formulas after migration. Named ranges may break.

Multi-Currency Spreadsheets

=Amount * XLOOKUP(Currency, FX_Rates!A:A, FX_Rates!B:B)
Or with GOOGLEFINANCE:
=Amount * GOOGLEFINANCE("CURRENCY:GBPUSD")

Large Dataset Workarounds (>100K rows)

1. Split data across multiple sheets by time period
2. Use pivot tables / QUERY instead of row-level formulas
3. Import summarized data, not raw transactions
4. Consider BigQuery + Connected Sheets (Google) or Power Pivot (Excel)
5. If you need >500K rows, graduate to a database

---

Natural Language Commands

When working with spreadsheets, you can ask:

• "Audit this spreadsheet for quality issues"
• "Design a financial model for [business type]"
• "Create a dashboard layout for [metrics]"
• "Write the formulas for [calculation]"
• "Optimize this spreadsheet for performance"
• "Build a data validation system for [input type]"
• "Create an Apps Script to [automate task]"
• "Design a template for [use case]"
• "Review this formula and suggest improvements"
• "Help me migrate this from Excel to Google Sheets"
• "Set up a scenario analysis for [model]"
• "Build a KPI tracker for [department]"

---

⚡ Level Up — AfrexAI Context Packs

This skill covers spreadsheet engineering methodology. For industry-specific financial models, dashboards, and templates:

• 💰 SaaS Context Pack — MRR/ARR models, SaaS metrics dashboards, cohort analysis templates
• 🏦 Fintech Context Pack — Financial modeling, risk calculators, compliance trackers
• 🏭 Manufacturing Context Pack — Production trackers, inventory models, cost analysis
• 🏗️ Construction Context Pack — Project budgets, bid calculators, resource planning

$47 per pack — Complete AI agent context for your industry.

Browse all packs: AfrexAI Storefront →

---

🔗 More Free Skills by AfrexAI

• afrexai-data-storytelling — Data visualization & dashboard design methodology
• afrexai-personal-finance — Complete personal finance operating system
• afrexai-product-analytics — Product metrics & analytics engineering
• afrexai-fpa-engine — Financial planning & analysis
• afrexai-automation-strategy — Workflow automation methodology

---

Built by AfrexAI — AI agents that compound capital and code.