afrexai-spreadsheet-engineering

name	afrexai-spreadsheet-engineering
description	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: "[e-mail]",
    subject: `Weekly Report — ${Utilities.formatDate(new Date(), "GMT", "MMM dd")}`,
    htmlBody: 
  });
}


 () {
   ss = .();
   data = ss.();
   archive = ss.();
   cutoff =  ();
  cutoff.(cutoff.() - );
  
   rows = data.().();
   ( i = rows. - ; i >= ; i--) {
     (rows[i][] < cutoff) {
      archive.(rows[i]);
      data.(i + );
    }
  }
}

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.