Microsoft Excel

Microsoft Excel Automation AI Skill Guide (Claude)

Overview & Engine Architecture

Microsoft Excel combines a grid calculation engine with tables, Power Query (M), PivotCaches, dynamic array formulas, and automation via Office Scripts (Excel on the web / desktop with Automate) and legacy VBA. Claude operates as a Principal Spreadsheet Systems Analyst, specializing in Table-first models, Power Query transforms, Office Scripts TypeScript, and reproducible refresh pipelines.

Excel Workbook & Automation Stack

┌─────────────────────────────────────────────────────────────┐
│                 Excel Architecture                          │
│                                                             │
│  Calculation & Structure                                    │
│  ├── Workbooks / Worksheets / Ranges / Tables (ListObjects) │
│  ├── Formulas / Dynamic arrays / LAMBDA                     │
│  └── PivotTables / Charts / Data Model (Power Pivot)        │
│                                                             │
│  ETL & Automation                                           │
│  ├── Power Query (M) + refresh                              │
│  ├── Office Scripts (TypeScript)                            │
│  └── VBA / Office JS add-ins                                │
│                                                             │
│  Distribution                                               │
│  ├── xlsx / xlsm / csv                                      │
│  ├── SharePoint / OneDrive refresh                          │
│  └── Power Automate connectors                              │
└─────────────────────────────────────────────────────────────┘

Operational Capabilities & Agent Directives

1. Tables Over Naked Ranges: Convert ranges to Tables for stable structured references.
2. Power Query for ETL: Prefer M transforms over fragile copy-paste macros for recurring imports.
3. Office Scripts for Cloud Automation: Author TypeScript scripts that work with Automate flows.
4. No Magic Numbers: Document units, as-of dates, and assumptions in a README sheet.
5. Volatile Functions: Minimize INDIRECT/OFFSET in large models; prefer structured refs.

Production Office Script + Power Query M

Office Script (Automate → New Script):

// ==============================================================================
// Office Scripts: normalize a "Raw" sheet into an Excel Table and flag blanks
// ==============================================================================
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheet("Raw");
  if (!sheet) throw new Error('Missing sheet "Raw"');

  const used = sheet.getUsedRange();
  if (!used) throw new Error("Raw sheet is empty");

  const tables = sheet.getTables();
  for (const t of tables) t.delete();

  const table = sheet.addTable(used.getAddress(), true);
  table.setName("RawData");

  const cols = table.getColumns();
  const statusCol = cols[cols.length - 1];
  // Ensure a Status column exists as last column header named Status
  const header = statusCol.getName();
  if (header.toLowerCase() !== "status") {
    table.addColumn(null, null, "Status");
  }

  const rows = table.getRangeBetweenHeaderAndTotal().getRowCount();
  const values = table.getRangeBetweenHeaderAndTotal().getValues();
  for (let r = 0; r < rows; r++) {
    const row = values[r];
    const blank = row.some((c) => c === "" || c === null);
    // Status is last column
    values[r][row.length - 1] = blank ? "NEEDS_REVIEW" : "OK";
  }
  table.getRangeBetweenHeaderAndTotal().setValues(values);
}

Power Query M snippet (transform CSV):

let
  Source = Csv.Document(File.Contents("C:\data\sales.csv"), [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
  Promote = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
  Types = Table.TransformColumnTypes(Promote, {{"Amount", type number}, {"Date", type date}}),
  Filter = Table.SelectRows(Types, each [Amount] <> null and [Amount] > 0)
in
  Filter

Technical Troubleshooting Matrix

Best Practices

1. One fact table + dimension sheets; avoid circular cross-sheet spaghetti.
2. Parameterize file paths in Power Query.
3. Keep raw dumps immutable; transform into clean tables.

Essential Entry Points

• Automate gallery (Office Scripts)
• Data → Get Data (Power Query)
• Formulas → Name Manager

Agent Operational Directive

MANDATORY: Prefer Tables + Power Query for recurring data shaping. Use Office Scripts for cloud-safe automation. Document assumptions on a dedicated sheet and never overwrite raw source tabs in place.