Spreadsheet Modeling

Key principles

1. 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.

2. 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.

3. Name things - Use named ranges and structured table references instead of raw cell addresses. =Revenue * Tax_Rate is auditable; =B7*$K$2 is not.

4. 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.

5. 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.

XLOOKUP (Excel 365+ / Google Sheets):

=XLOOKUP(lookup_value, lookup_array, return_array, "Not found", 0)

INDEX-MATCH (all versions):

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Two-criteria lookup (INDEX-MATCH-MATCH):

=INDEX(data_range, MATCH(row_value, row_headers, 0), MATCH(col_value, col_headers, 0))

Always wrap lookups in IFERROR or use XLOOKUP's built-in if_not_found argument to handle missing values gracefully.

Build a conditional aggregation

Use SUMIFS/COUNTIFS/AVERAGEIFS for multi-criteria aggregation.

=SUMIFS(Sales[Amount], Sales[Region], "West", Sales[Date], ">="&DATE(2025,1,1))

Dynamic array alternative (Excel 365+):

=SUM(FILTER(Sales[Amount], (Sales[Region]="West") * (Sales[Date]>=DATE(2025,1,1))))

SUMIFS criteria ranges must all be the same size. Mismatched ranges produce a #VALUE! error with no helpful message.

Create a pivot table

Step-by-step framework for designing a pivot table:

1. Define the question - "What is total revenue by region and product category for Q1?"
2. Identify the fields - Rows: Region, Product Category. Values: SUM of Revenue. Filter: Date (Q1)
3. Build the pivot - Select data table, Insert > PivotTable, drag fields to areas
4. Format - Apply number formatting to values, add a slicer for Date for interactivity
5. Refresh strategy - If source data changes, right-click > Refresh. For auto-refresh, use VBA or Apps Script

Calculated field example (add a margin calculation inside the pivot):

Margin = Revenue - Cost

Pivot tables silently exclude rows with blank values in row/column fields. Clean your data before pivoting.

Design a dashboard

Build dashboards on a dedicated output sheet that references calculation sheets.

Layout checklist:

1. Top row: Title, date range selector (data validation drop-down), refresh button
2. Row 2-4: KPI cards (large numbers) - Revenue, Growth %, Units Sold
3. Main area: 2-3 charts (combo chart for trends, bar chart for comparisons, pie only if fewer than 6 categories)
4. Bottom or right: Detail table with conditional formatting (data bars, color scales)

KPI formula pattern:

=TEXT(total_revenue, "$#,##0") & "  (" & TEXT(growth_rate, "+0.0%;-0.0%") & ")"

Conditional formatting rules for a heatmap:

• Select the data range
• 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).

function sendWeeklyReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dashboard = ss.getSheetByName("Dashboard");
  const revenue = dashboard.getRange("B2").getValue();
  const growth = dashboard.getRange("B3").getValue();

  const subject = "Weekly Report - Revenue: $" + revenue.toLocaleString();
  const body = [
    "Weekly KPIs:",
    "Revenue: $" + revenue.toLocaleString(),
    "Growth: " + (growth * 100).toFixed(1) + "%",
    "",
    "View full dashboard: " + ss.getUrl()
  ].join("\n");

  MailApp.sendEmail("team@company.com", subject, body);
}

function createTrigger() {
  ScriptApp.newTrigger("sendWeeklyReport")
    .timeBased()
    .everyWeeks(1)
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(9)
    .create();
}

Apps Script has a 6-minute execution limit. For large datasets, use batch processing with continuation tokens.

Build a scenario / what-if analysis

Use Data Tables (Excel) or manual scenario switching for sensitivity analysis.

Two-variable data table pattern:

1. Place the output formula in the top-left corner of the table
2. Row input values across the top (e.g., price points)
3. Column input values down the left (e.g., volume levels)
4. Select the entire table, Data > What-If Analysis > Data Table
5. Set row input cell and column input cell references

Scenario Manager alternative:

=CHOOSE(Scenario_Selector, base_value, optimistic_value, pessimistic_value)

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

1. 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.

2. 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.

3. 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.

4. 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.

5. 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

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:

npx skills add AbsolutelySkilled/AbsolutelySkilled --skill <name>

Skip entirely if recommended_skills is empty or all companions are already installed.