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:

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

When NOT to Use a Spreadsheet

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

• One direction of flow — Data flows left→right or top→bottom. Never circular.
• Inputs separate from calculations — NEVER hardcode numbers in formulas. Use named ranges.
• One fact in one place — If a value is used in 3 places, define it once and reference it.
• Color code by purpose — Blue = input, Black = formula, Green = linked from other sheet, Red = warning.
• Freeze panes on every data sheet — Header row and label columns always visible.
• Protect formula cells — Lock everything except input cells. Prevent accidental overwrites.
• 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

Phase 3: Formula Engineering

Formula Complexity Levels

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

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

Phase 4: Data Validation & Quality

Input Validation Checklist

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)

• 🔴 Errors — Any cell with #REF!, #N/A, #VALUE! → Red background
• 🟡 Warnings — Values outside expected range → Yellow background
• 🟢 Positive — On-target metrics → Green text
• 📊 Data bars — Numeric ranges → Proportional bars
• 🎯 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, NPER12, -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

Filter by dropdown:
Config sheet: Data validation dropdown for Region, Period, Category
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})

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")

1. Data → Get Data → From [Source]
Transform in Power Query Editor
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)

Rules:
Authorize on first use (one-time popup)
Use named ranges in source spreadsheet
Wrap in IFERROR for graceful failures
Minimize imported range — don't import entire sheets
Cache results if auto-refresh causes slowness
Pattern:
=IFERROR(
  IMPORTRANGE(SOURCE_URL, "Data!A1:D"&SOURCE_ROW_COUNT),
  "⚠️ Connection failed — check source spreadsheet access"
)

// 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: "team@company.com",
    subject: Weekly Report — ${Utilities.formatDate(new Date(), "GMT", "MMM dd")},
    htmlBody: 
Weekly KPIs
Revenue: ${kpi1}
Growth: ${kpi2}
  });
}
// 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);
    }
  }
}

' 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

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

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

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

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)

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