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: SignalHealthySickNamed ranges for all key inputs✅ Uses named ranges❌ Raw cell references everywhereInputs separated from calculations✅ Clear input section❌ Hardcoded values in formulasNo circular references✅ Clean dependency chain❌ Iterative calculation warningsDocumentation/comments exist✅ README sheet + cell notes❌ "What does this formula do?"Error handling in formulas✅ IFERROR/IFNA wrapping❌ #REF! #N/A scattered everywhereConsistent formatting✅ Style guide followed❌ Random fonts, colors, sizesVersion 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

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

TierRowsSheetsUsersFormulasExampleSimple<1K1-31-3BasicBudget tracker, checklistStandard1K-10K3-83-10IntermediateFinancial model, project trackerComplex10K-50K8-1510-30AdvancedMulti-dept dashboard, CRMEnterprise50K+15+30+ExpertData warehouse substitute (🚩 migrate)

When NOT to Use a Spreadsheet

ScenarioBetter Tool>100K rows of dataDatabase (PostgreSQL, SQLite)>10 concurrent editorsWeb app or AirtableComplex relational data (3+ entity types)Database + appNeeds audit trail / compliancePurpose-built systemReal-time data processingETL pipeline + BI toolVersion-controlled code logicActual code (Python, JS) Rule: Spreadsheets are prototyping tools that become production systems by accident. Know when to graduate.

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

ColorMeaningWhen to Use🔵 Light blue backgroundUser input cellEditable fields⬛ Black textFormula/calculatedAuto-populated cells🟢 Green textLinked from other sheetCross-sheet references🔴 Red text/backgroundWarning/errorValidation failures, negative values🟡 Yellow backgroundAssumptionKey assumptions that drive the model⬜ Grey backgroundReference/lockedConstants, lookup tables

Formula Complexity Levels

LevelTechniquesExampleL1 BasicSUM, AVERAGE, COUNT, IF, CONCATENATE=SUM(B2:B100)L2 IntermediateVLOOKUP/XLOOKUP, SUMIFS, INDEX/MATCH, TEXT=XLOOKUP(A2,Ref!A:A,Ref!B:B)L3 AdvancedARRAYFORMULA, QUERY, INDIRECT, nested IFs=QUERY(Data!A:F,"SELECT A,SUM(F) GROUP BY A")L4 ExpertLAMBDA, 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

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

Input Validation Checklist

Data TypeValidationImplementationDateDate rangeData validation: between START and ENDCurrencyNumber ≥ 0Data validation: decimal ≥ 0, format $#,##0.00Percentage0-100 or 0-1Data validation: decimal between 0 and 1CategoryDropdown listData validation: list from Reference sheetEmailContains @Custom: =ISNUMBER(FIND("@",A2))PhoneLength checkCustom: =AND(LEN(A2)>=10, LEN(A2)<=15)Required fieldNot blankCustom: =LEN(TRIM(A2))>0ID/CodeUnique + formatCustom: =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)

🔴 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

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)

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 PatternBest ChartAvoidTrend over timeLine chartPie chartPart of wholeStacked bar or donut3D pieComparisonHorizontal barRadar chartDistributionHistogramLine chartRelationshipScatter plotBar chartKPI vs targetBullet chart or gaugeComplex chartGeographicHeat map or filled mapBar chart

7 Chart Rules

Title = Insight, not description. "Revenue grew 23% in Q3" not "Q3 Revenue Chart" Start Y-axis at zero for bar charts. Line charts can truncate with clear labeling. Max 5-7 data series per chart. Use "Other" category for the rest. Remove chartjunk — No 3D effects, gradient fills, excessive gridlines. Use consistent colors — Same category = same color across all charts. Label directly on chart where possible. Minimize legend lookups. 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})

Import Method Selection

SourceMethodRefreshCSV/Excel fileManual import / Power QueryManualGoogle Sheets (other)IMPORTRANGEAuto (varies)Web page tableIMPORTHTML / Power QueryAuto / manualAPI / JSONIMPORTDATA / Apps Script / Power QueryScheduledDatabasePower Query / ODBCScheduledAnother sheet (same workbook)Direct referenceReal-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" )

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: "team@company.com", 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

TaskGoogle SheetsExcelOn-edit timestampApps Script onEditVBA Worksheet_ChangeScheduled emailApps Script + triggerPower AutomateData refreshApps Script + triggerPower Query + schedulePDF exportApps ScriptVBA + SaveAsCross-system syncApps Script + APIPower Automate / VBACustom functionsApps Script CUSTOM_FUNCTIONVBA UDF or LAMBDA

Performance Killers (Ranked)

IssueImpactFixINDIRECT/OFFSET (volatile)🔴 CriticalReplace with INDEX/XLOOKUPWhole-column references (A:A)🔴 CriticalUse bounded ranges (A2:A1000)ARRAYFORMULA on huge ranges🟡 HighLimit range or use QUERYExcessive conditional formatting🟡 HighReduce rules, use bounded rangesToo many IMPORTRANGE🟡 HighConsolidate, cache locallyUnused sheets with formulas🟢 MediumDelete or clear unused sheetsComplex nested IFs🟢 MediumReplace with SWITCH/IFS/XLOOKUPHeavy formatting (images, shapes)🟢 MediumMinimize decorative elements

Google Sheets Performance Rules

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

Excel Performance Rules

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

Access Control Strategy

RolePermissionsImplementationOwnerFull controlOriginal creatorEditorEdit data, not structureShare with edit, protect structure sheetsAnalystEdit inputs, view outputsProtect all except input cellsViewerView onlyShare as viewerCommenterView + commentShare 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

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

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

Spreadsheet Quality Rubric (0-100)

DimensionWeightScoringArchitecture15%Clear sheet structure, data flow direction, READMEFormula Quality20%Named ranges, error handling, no hardcodingData Validation15%Input constraints, dropdowns, type checkingVisual Design10%Consistent formatting, color coding, readabilityDocumentation15%Cell notes, README, changelog, instructionsPerformance10%No volatile functions, bounded ranges, fast recalcError Handling10%IFERROR wrappers, validation checks, no broken refsMaintainability5%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

MistakeImpactFixHardcoded numbers in formulasCan't audit or updateNamed ranges + Config sheetNo error handling#N/A cascades break everythingIFERROR on all lookupsWhole-column referencesSlow, crashes on large dataBounded rangesCircular referencesUnpredictable resultsRedesign calculation flowNo documentation"What does this formula do?"README + cell notesNo data validationGarbage in = garbage outDropdowns + constraintsOne mega-sheetUnmaintainable, slowSplit by functionNo backup/versionsOne mistake = lost workNamed versions + exportsCopy-paste instead of formulasStale data, inconsistenciesUse references/IMPORTRANGEManual processes that should be automatedError-prone, time-wastingScripts or scheduled refreshes

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)

Split data across multiple sheets by time period Use pivot tables / QUERY instead of row-level formulas Import summarized data, not raw transactions Consider BigQuery + Connected Sheets (Google) or Power Pivot (Excel) 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.