# Send Spreadsheet Engineering to your agent Hand the extracted package to your coding agent with a concrete install brief instead of figuring it out manually. ## Fast path - Download the package from Yavira. - Extract it into a folder your agent can access. - Paste one of the prompts below and point your agent at the extracted folder. ## Suggested prompts ### New install ```text I downloaded a skill package from Yavira. Read SKILL.md from the extracted folder and install it by following the included instructions. Then review README.md for any prerequisites, environment setup, or post-install checks. Tell me what you changed and call out any manual steps you could not complete. ``` ### Upgrade existing ```text I downloaded an updated skill package from Yavira. Read SKILL.md from the extracted folder, compare it with my current installation, and upgrade it while preserving any custom configuration unless the package docs explicitly say otherwise. Then review README.md for any prerequisites, environment setup, or post-install checks. Summarize what changed and any follow-up checks I should run. ``` ## Machine-readable fields ```json { "schemaVersion": "1.0", "item": { "slug": "afrexai-spreadsheet-engineering", "name": "Spreadsheet Engineering", "source": "tencent", "type": "skill", "category": "效率提升", "sourceUrl": "https://clawhub.ai/1kalin/afrexai-spreadsheet-engineering", "canonicalUrl": "https://clawhub.ai/1kalin/afrexai-spreadsheet-engineering", "targetPlatform": "OpenClaw" }, "install": { "downloadUrl": "/downloads/afrexai-spreadsheet-engineering", "sourceDownloadUrl": "https://wry-manatee-359.convex.site/api/v1/download?slug=afrexai-spreadsheet-engineering", "sourcePlatform": "tencent", "targetPlatform": "OpenClaw", "packageFormat": "ZIP package", "primaryDoc": "SKILL.md", "includedAssets": [ "README.md", "SKILL.md" ], "downloadMode": "redirect", "sourceHealth": { "source": "tencent", "status": "healthy", "reason": "direct_download_ok", "recommendedAction": "download", "checkedAt": "2026-04-30T16:55:25.780Z", "expiresAt": "2026-05-07T16:55:25.780Z", "httpStatus": 200, "finalUrl": "https://wry-manatee-359.convex.site/api/v1/download?slug=network", "contentType": "application/zip", "probeMethod": "head", "details": { "probeUrl": "https://wry-manatee-359.convex.site/api/v1/download?slug=network", "contentDisposition": "attachment; filename=\"network-1.0.0.zip\"", "redirectLocation": null, "bodySnippet": null }, "scope": "source", "summary": "Source download looks usable.", "detail": "Yavira can redirect you to the upstream package for this source.", "primaryActionLabel": "Download for OpenClaw", "primaryActionHref": "/downloads/afrexai-spreadsheet-engineering" }, "validation": { "installChecklist": [ "Use the Yavira download entry.", "Review SKILL.md after the package is downloaded.", "Confirm the extracted package contains the expected setup assets." ], "postInstallChecks": [ "Confirm the extracted package includes the expected docs or setup files.", "Validate the skill or prompts are available in your target agent workspace.", "Capture any manual follow-up steps the agent could not complete." ] } }, "links": { "detailUrl": "https://openagent3.xyz/skills/afrexai-spreadsheet-engineering", "downloadUrl": "https://openagent3.xyz/downloads/afrexai-spreadsheet-engineering", "agentUrl": "https://openagent3.xyz/skills/afrexai-spreadsheet-engineering/agent", "manifestUrl": "https://openagent3.xyz/skills/afrexai-spreadsheet-engineering/agent.json", "briefUrl": "https://openagent3.xyz/skills/afrexai-spreadsheet-engineering/agent.md" } } ``` ## Documentation ### 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: \`

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); } } } ### 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"&"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. ## Trust - Source: tencent - Verification: Indexed source record - Publisher: 1kalin - Version: 1.0.0 ## Source health - Status: healthy - Source download looks usable. - Yavira can redirect you to the upstream package for this source. - Health scope: source - Reason: direct_download_ok - Checked at: 2026-04-30T16:55:25.780Z - Expires at: 2026-05-07T16:55:25.780Z - Recommended action: Download for OpenClaw ## Links - [Detail page](https://openagent3.xyz/skills/afrexai-spreadsheet-engineering) - [Send to Agent page](https://openagent3.xyz/skills/afrexai-spreadsheet-engineering/agent) - [JSON manifest](https://openagent3.xyz/skills/afrexai-spreadsheet-engineering/agent.json) - [Markdown brief](https://openagent3.xyz/skills/afrexai-spreadsheet-engineering/agent.md) - [Download page](https://openagent3.xyz/downloads/afrexai-spreadsheet-engineering)