Use this skill when building, auditing, or optimizing spreadsheet models in Excel or Google Sheets. Triggers on formula writing, pivot table creation, dashboard design, data validation, conditional formatting, macro/VBA scripting, Apps Script automation, financial modeling, what-if analysis, XLOOKUP/INDEX-MATCH lookups, array formulas, and workbook architecture. Covers advanced Excel and Google Sheets for analysts, finance professionals, and operations teams.
Use this skill when building, auditing, or optimizing spreadsheet models in Excel or Google Sheets. Triggers on formula writing, pivot table creation, dashboard design, data validation, conditional formatting, macro/VBA scripting, Apps Script automation, financial modeling, what-if analysis, XLOOKUP/INDEX-MATCH lookups, array formulas, and workbook architecture. Covers advanced Excel and Google Sheets for analysts, finance professionals, and operations teams.
Separate inputs, calculations, and outputs - Every model should have a
clear flow: assumptions/inputs on one sheet, calculations on another, and
summary/output on a third. Never mix hardcoded inputs into formula cells.
One formula per row/column pattern - A column of formulas should use the
same formula copied down. If row 5 has a different formula than row 6 in the
same column, the model is fragile and hard to audit.
Name things - Use named ranges and structured table references instead of
raw cell addresses. =Revenue * Tax_Rate is auditable; =B7*$K$2 is not.
No magic numbers - Every literal value in a formula should either be a
named constant or live in a clearly labeled input cell. If you see *1.08
in a formula, extract Tax_Rate as a named input.
Design for the next person - Use consistent formatting, color-code input
cells (typically blue font on yellow background), and add cell comments for
non-obvious logic. Models outlive their creators.
Core concepts
Workbook architecture organizes a model into layers. The standard pattern is:
Inputs/Assumptions sheet (all editable parameters), Calculations sheet (pure
formulas referencing inputs), and Output/Dashboard sheet (charts, KPIs, summary
tables). Larger models add a Cover/TOC sheet and a Data sheet for raw imports.
Structured tables (Excel Tables / named ranges in Sheets) are the foundation
of maintainable formulas. A table auto-expands when data is added, supports
structured references like =SUM(Sales[Revenue]), and makes pivot tables
reliable. Always convert raw data ranges to tables before building on them.
Array formulas and dynamic arrays enable powerful multi-cell calculations.
Excel's FILTER, SORT, UNIQUE, and SEQUENCE functions (and their Google Sheets
equivalents) replace many complex INDEX-MATCH or helper-column patterns with
single formulas that spill results across multiple cells.
Pivot tables summarize large datasets without formulas. They support grouping,
calculated fields, slicers for interactivity, and can feed charts. The key skill
is choosing the right row/column/value/filter field layout for the question being
asked.
Common tasks
Write a lookup formula
Use XLOOKUP (Excel 365+) or INDEX-MATCH as the universal lookup pattern. Avoid
VLOOKUP for new work - it breaks when columns are inserted.
Apply Color Scale: Green (high) to Red (low) for positive metrics
Apply Data Bars for volume metrics
Use Icon Sets (arrows) for period-over-period change columns
Write a VBA macro (Excel)
Use VBA for repetitive tasks, custom functions, or workbook automation.
Basic macro structure:
Sub FormatReport()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Data")
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
ws.Range("A1:Z1").Font.Bold = True
ws.UsedRange.Columns.AutoFit
ws.Range("D2:D" & lastRow).NumberFormat = "$#,##0.00"
MsgBox "Report formatted: " & lastRow - 1 & " rows processed."
End Sub
Custom function (UDF):
Function WeightedAverage(values As Range, weights As Range) As Double
Dim i As Long
Dim sumProduct As Double
Dim sumWeights As Double
For i = 1 To values.Cells.Count
sumProduct = sumProduct + values.Cells(i).Value * weights.Cells(i).Value
sumWeights = sumWeights + weights.Cells(i).Value
Next i
If sumWeights = 0 Then
WeightedAverage = 0
Else
WeightedAverage = sumProduct / sumWeights
End If
End Function
VBA macros must be saved in .xlsm format. UDFs are volatile by default in
some contexts - avoid calling volatile functions inside them.
Write a Google Apps Script
Use Apps Script for automation in Google Sheets (email alerts, data imports, scheduled tasks).
Where Scenario_Selector is a data-validation drop-down cell containing 1, 2, or 3.
Data Tables recalculate every time the workbook recalculates. In large models,
set calculation to Manual (Ctrl+Shift+F9 to force recalc) to avoid slowdowns.
Gotchas
Pivot tables silently exclude blank rows - If any row in your source data has a blank value in the row or column field, that row is excluded from the pivot entirely with no warning. Clean blank values (replace with "Unknown" or 0) before building pivots that need complete coverage.
SUMIFS range size mismatch produces #VALUE! with no useful message - All criteria ranges in a SUMIFS must be the exact same dimensions as the sum range. A single range that is one row taller than the others throws #VALUE! with no indication of which range is mismatched. Build a helper formula to check range sizes when debugging.
Data Tables recalculate on every edit in large models - Excel recalculates all Data Tables whenever any cell in the workbook changes. In models with large Data Tables, this can make every keystroke take seconds. Set calculation mode to Manual (Formulas > Calculation Options > Manual) and use Ctrl+Alt+F9 to force recalc when needed.
OFFSET and INDIRECT break when used in table references - Both functions are volatile and recalculate on every change. Using them inside structured table references (Table[Column]) can cause unexpected reference errors when tables are resized. Prefer INDEX as a non-volatile alternative to OFFSET.
Apps Script 6-minute execution limit fails silently on large datasets - A script that times out after 6 minutes does not throw an error to the user - it just stops partway through the operation, leaving data in a partially modified state. For large datasets, implement batch processing with PropertiesService to store a continuation token and re-trigger the script.
Anti-patterns / common mistakes
Mistake
Why it's wrong
What to do instead
Hardcoded numbers in formulas
=B5*1.08 is unauditable - no one knows what 1.08 means in 6 months
Extract to a named input cell: =B5*Tax_Rate
Merging cells
Breaks sorting, filtering, formulas, and pivot table source ranges
Use "Center Across Selection" formatting or adjust column widths instead
One giant sheet
Mixing inputs, calculations, and outputs on one sheet makes auditing impossible
Separate into Input, Calc, and Output sheets with a clear flow
Circular references
Intentional circulars (iterative calc) are fragile and confuse other users
Restructure the logic to avoid circulars, or document heavily if truly required
VLOOKUP with column index
=VLOOKUP(A1,data,3,FALSE) breaks when columns are inserted
Use XLOOKUP or INDEX-MATCH which reference the return column directly
No error handling in formulas
#N/A and #DIV/0! errors cascade through dependent cells and break dashboards
Wrap in IFERROR or IFNA with meaningful defaults
Volatile functions everywhere
NOW(), INDIRECT(), OFFSET() recalculate on every edit, slowing the workbook
Use non-volatile alternatives (INDEX instead of OFFSET, static timestamps via VBA)
References
For detailed content on specific sub-domains, read the relevant file from references/:
references/formula-patterns.md - Advanced formula cookbook: array formulas, LAMBDA, LET, dynamic arrays, regex
references/vba-patterns.md - VBA and Apps Script patterns: loops, error handling, UserForms, API calls
references/financial-modeling.md - Financial model architecture: DCF, three-statement models, sensitivity tables
Only load a references file if the current task requires deep detail on that topic.
Companion check
On first activation of this skill in a conversation: check which companion skills are installed by running ls ~/.claude/skills/ ~/.agent/skills/ ~/.agents/skills/ .claude/skills/ .agent/skills/ .agents/skills/ 2>/dev/null. Compare the results against the recommended_skills field in this file's frontmatter. For any that are missing, mention them once and offer to install:
