excel-mcp - Claude MCP Skill

# Excel MCP Server Skill

Provides 326 Excel operations via Model Context Protocol. The MCP Server hosts the ExcelMCP Service in-process and calls it directly for low-latency Excel automation. Tools are auto-discovered - this documents quirks, workflows, and gotchas.

## Workflow Checklist

| Step | Tool | Action | When |
|------|------|--------|------|
| 1. Open file | `file` | `open` or `create` | Always first |
| 2. Create sheets | `worksheet` | `create`, `rename` | If needed |
| 3. Write data | `range` | `set-values` | Always (2D arrays) |
| 4. Format | `range` | `set-number-format` | After writing |
| 5. Structure | `table` | `create` | Convert data to tables |
| 6. Save & close | `file` | `close` with `save: true` | Always last |

## Preconditions

- Windows host with Microsoft Excel installed (2016+)
- Use full Windows paths: `C:\Users\Name\Documents\Report.xlsx`
- Excel files must not be open in another Excel instance

## Calculation Mode Workflow (Batch Performance)

Use `calculation_mode` for **bulk write performance optimization**. When writing many values or formulas, disable auto-recalc to avoid recalculating after every cell:

```
1. calculation_mode(action: 'set-mode', mode: 'manual')  → Disable auto-recalc
2. Perform all writes (range set-values, set-formulas)
3. calculation_mode(action: 'calculate', scope: 'workbook')  → Recalculate once
4. calculation_mode(action: 'set-mode', mode: 'automatic')  → Restore default
```

**Note:** You do NOT need manual mode to read formulas - `range get-formulas` returns formula text regardless of calculation mode.

## CRITICAL: Execution Rules (MUST FOLLOW)

### Rule 1: NEVER Ask Clarifying Questions

**STOP.** If you're about to ask "Which file?", "What table?", "Where should I put this?" - DON'T.

| Bad (Asking) | Good (Discovering) |
|--------------|-------------------|
| "Which Excel file should I use?" | `file(list)` → use the open session |
| "What's the table name?" | `table(list)` → discover tables |
| "Which sheet has the data?" | `worksheet(list)` → check all sheets |
| "Should I create a PivotTable?" | YES - create it on a new sheet |

**You have tools to answer your own questions. USE THEM.**

### Rule 2: Always End With a Text Summary

**NEVER end your turn with only a tool call.** After completing all operations, always provide a brief text message confirming what was done. Silent tool-call-only responses are incomplete.

### Rule 3: Format Data Professionally

Always apply number formats after setting values:

| Data Type | Format Code | Result |
|-----------|-------------|--------|
| USD | `$#,##0.00` | $1,234.56 |
| EUR | `€#,##0.00` | €1,234.56 |
| Percent | `0.00%` | 15.00% |
| Date (ISO) | `yyyy-mm-dd` | 2025-01-22 |

Write format codes in US notation (`,` grouping, `.` decimal) regardless of the machine's
locale — Excel translates them. The **rendered** separators follow the user's Windows regional
settings, so `$#,##0.00` shows `$1.234,56` on a German system. Don't "fix" that by swapping the
separators in the format code; it would break on every other locale.

**Workflow:**
```
1. range set-values (data is now in cells)
2. range set-number-format (apply format)
3. range_format auto-fit-columns (formatted values are wider than raw ones)
```

Step 3 is not optional. A column sized for `45678` is too narrow once that value renders as
`2025-01-22` or `$1,234.56`, and Excel displays `#####` instead of the number.

### Rule 4: Use Excel Tables (Not Plain Ranges)

Always convert tabular data to Excel Tables:

```
1. range set-values (write data including headers)
2. table(action: 'create', table_name: 'SalesData', range_address: 'A1:D100')
```

**Why:** Structured references, auto-expand, required for Data Model/DAX.

### Rule 5: Session Lifecycle

```
1. file(action: 'open', path: '...')  → sessionId
2. All operations use `session_id`
3. file(action: 'close', save: true)  → saves and closes
```

**Unclosed sessions leave Excel processes running, locking files.**

### Rule 6: Data Model Prerequisites

DAX operations require tables in the Data Model:

```
Step 1: Create table → Table exists
Step 2: table(action: 'add-to-data-model') → Table in Data Model
Step 3: datamodel(action: 'create-measure') → NOW this works
```

### Rule 7: Power Query Development Lifecycle

**BEST PRACTICE: Test-First Workflow**

```
1. powerquery(action: 'evaluate', m_code: '...') → Test WITHOUT persisting
2. powerquery(action: 'create', ...) → Store validated query
3. powerquery(action: 'refresh', ...) → Load data
```

**Why evaluate first:**
- Catches syntax errors and missing sources BEFORE creating permanent queries
- Better error messages than COM exceptions from create/update
- See actual data preview (columns + sample rows)
- No cleanup needed - like a REPL for M code
- Skip only for trivial literal tables

**Common mistake:** Creating/updating without evaluate → pollutes workbook with broken queries

### Rule 8: Targeted Updates Over Delete-Rebuild

- **Prefer**: `set-values` on specific range (e.g., `A5:C5` for row 5)
- **Avoid**: Deleting and recreating entire structures

**Why:** Preserves formatting, formulas, and references.

### Rule 9: Follow suggestedNextActions

Error responses include actionable hints:
```json
{
  "success": false,
  "errorMessage": "Table 'Sales' not found in Data Model",
  "suggestedNextActions": ["table(action: 'add-to-data-model', table_name: 'Sales')"]
}
```

## Tool Selection Quick Reference

| Task | Tool | Key Action |
|------|------|------------|
| Create/open/save workbooks | `file` | open, create, close |
| Write/read cell data | `range` | set-values, get-values |
| Format cells | `range` | set-number-format |
| Create tables from data | `table` | create |
| Add table to Power Pivot | `table` | add-to-data-model |
| Create DAX formulas | `datamodel` | create-measure |
| Create PivotTables | `pivottable` | create, create-from-datamodel |
| Filter with slicers | `slicer` | set-slicer-selection |
| Create charts | `chart` | create-from-range |
| Run what-if analysis | `analysis` | goal-seek, create-scenario, create-data-table |
| Control calculation mode | `calculation_mode` | get-mode, set-mode, calculate |
| Visual verification | `screenshot` | capture, capture-sheet |

## Reference Documentation

See `references/` for detailed guidance:

- [What-if analysis and Solver limits](./references/analysis.md)
- [Core execution rules and LLM guidelines](./references/behavioral-rules.md)
- [Common mistakes to avoid](./references/anti-patterns.md)
- [Bulk write performance optimization](./references/calculation.md)
- [Data Model constraints and patterns](./references/workflows.md)
- [Charts and formatting](./references/chart.md)
- [Conditional formatting operations](./references/conditionalformat.md)
- [Dashboard and report best practices](./references/dashboard.md)
- [Data Model/DAX specifics](./references/datamodel.md)
- [DMV query reference for Data Model analysis](./references/dmv-reference.md)
- [Excel agent mode and advanced automation](./references/excel_agent_mode.md)
- [Gotchas and known limits](./references/gotchas.md)
- [Power Query M code syntax reference](./references/m-code-syntax.md)
- [PivotTable operations](./references/pivottable.md)
- [Power Query specifics](./references/powerquery.md)
- [Range operations and number formats](./references/range.md)
- [Screenshot and visual verification](./references/screenshot.md)
- [Slicer operations](./references/slicer.md)
- [Table operations](./references/table.md)
- [Window and visibility operations](./references/window.md)
- [Worksheet operations](./references/worksheet.md)