Excel Mcp

1---2name: excel-mcp3description: >4  Automate Microsoft Excel on Windows via COM interop. Use when creating, reading,5  or modifying Excel workbooks. Supports Power Query (M code), Data Model (DAX measures),6  PivotTables, Tables, Ranges, Charts, Slicers, Formatting, VBA macros, connections, and calculation mode control.7  Triggers: Excel, spreadsheet, workbook, xlsx, Power Query, DAX, PivotTable, VBA.8---9 10# Excel MCP Server Skill11 12Provides 227 Excel operations via Model Context Protocol. The MCP Server forwards all requests to the shared ExcelMCP Service, enabling session sharing with CLI. Tools are auto-discovered - this documents quirks, workflows, and gotchas.13 14## Workflow Checklist15 16| Step | Tool | Action | When |17|------|------|--------|------|18| 1. Open file | `file` | `open` or `create` | Always first |19| 2. Create sheets | `worksheet` | `create`, `rename` | If needed |20| 3. Write data | `range` | `set-values` | Always (2D arrays) |21| 4. Format | `range` | `set-number-format` | After writing |22| 5. Structure | `table` | `create` | Convert data to tables |23| 6. Save & close | `file` | `close` with `save: true` | Always last |24 25## Preconditions26 27- Windows host with Microsoft Excel installed (2016+)28- Use full Windows paths: `C:\Users\Name\Documents\Report.xlsx`29- Excel files must not be open in another Excel instance30 31## Calculation Mode Workflow (Batch Performance)32 33Use `calculation_mode` for **bulk write performance optimization**. When writing many values or formulas, disable auto-recalc to avoid recalculating after every cell:34 35```361. calculation_mode(action: 'set-mode', mode: 'manual')  → Disable auto-recalc372. Perform all writes (range set-values, set-formulas)383. calculation_mode(action: 'calculate', scope: 'workbook')  → Recalculate once394. calculation_mode(action: 'set-mode', mode: 'automatic')  → Restore default40```41 42**Note:** You do NOT need manual mode to read formulas - `range get-formulas` returns formula text regardless of calculation mode.43 44## CRITICAL: Execution Rules (MUST FOLLOW)45 46### Rule 1: NEVER Ask Clarifying Questions47 48**STOP.** If you're about to ask "Which file?", "What table?", "Where should I put this?" - DON'T.49 50| Bad (Asking) | Good (Discovering) |51|--------------|-------------------|52| "Which Excel file should I use?" | `file(list)` → use the open session |53| "What's the table name?" | `table(list)` → discover tables |54| "Which sheet has the data?" | `worksheet(list)` → check all sheets |55| "Should I create a PivotTable?" | YES - create it on a new sheet |56 57**You have tools to answer your own questions. USE THEM.**58 59### Rule 2: Always End With a Text Summary60 61**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.62 63### Rule 3: Format Data Professionally64 65Always apply number formats after setting values:66 67| Data Type | Format Code | Result |68|-----------|-------------|--------|69| USD | `$#,##0.00` | $1,234.56 |70| EUR | `€#,##0.00` | €1,234.56 |71| Percent | `0.00%` | 15.00% |72| Date (ISO) | `yyyy-mm-dd` | 2025-01-22 |73 74**Workflow:**75```761. range set-values (data is now in cells)772. range set-number-format (apply format)78```79 80### Rule 4: Use Excel Tables (Not Plain Ranges)81 82Always convert tabular data to Excel Tables:83 84```851. range set-values (write data including headers)862. table create tableName="SalesData" rangeAddress="A1:D100"87```88 89**Why:** Structured references, auto-expand, required for Data Model/DAX.90 91### Rule 5: Session Lifecycle92 93```941. file(action: 'open', path: '...')  → sessionId952. All operations use sessionId963. file(action: 'close', save: true)  → saves and closes97```98 99**Unclosed sessions leave Excel processes running, locking files.**100 101### Rule 6: Data Model Prerequisites102 103DAX operations require tables in the Data Model:104 105```106Step 1: Create table → Table exists107Step 2: table(action: 'add-to-datamodel') → Table in Data Model108Step 3: datamodel(action: 'create-measure') → NOW this works109```110 111### Rule 7: Power Query Development Lifecycle112 113**BEST PRACTICE: Test-First Workflow**114 115```1161. powerquery(action: 'evaluate', mCode: '...') → Test WITHOUT persisting1172. powerquery(action: 'create', ...) → Store validated query1183. powerquery(action: 'refresh', ...) → Load data119```120 121**Why evaluate first:**122- Catches syntax errors and missing sources BEFORE creating permanent queries123- Better error messages than COM exceptions from create/update124- See actual data preview (columns + sample rows)125- No cleanup needed - like a REPL for M code126- Skip only for trivial literal tables127 128**Common mistake:** Creating/updating without evaluate → pollutes workbook with broken queries129 130### Rule 8: Targeted Updates Over Delete-Rebuild131 132- **Prefer**: `set-values` on specific range (e.g., `A5:C5` for row 5)133- **Avoid**: Deleting and recreating entire structures134 135**Why:** Preserves formatting, formulas, and references.136 137### Rule 9: Follow suggestedNextActions138 139Error responses include actionable hints:140```json141{142  "success": false,143  "errorMessage": "Table 'Sales' not found in Data Model",144  "suggestedNextActions": ["table(action: 'add-to-data-model', tableName: 'Sales')"]145}146```147 148### Rule 10: Use Calculation Mode for Bulk Write Performance149 150When writing many values/formulas (10+ cells), use `calculation_mode` to avoid recalculating after every write:151 152```1531. calculation_mode(action: 'set-mode', mode: 'manual')  → Disable auto-recalc1542. Perform data writes (range set-values, set-formulas)1553. calculation_mode(action: 'calculate', scope: 'workbook')  → Recalculate once at end1564. calculation_mode(action: 'set-mode', mode: 'automatic')  → Restore default157```158 159**When NOT needed:** Reading formulas, small edits (1-10 cells), or when you need immediate calculation results.160 161## Tool Selection Quick Reference162 163| Task | Tool | Key Action |164|------|------|------------|165| Create/open/save workbooks | `file` | open, create, close |166| Write/read cell data | `range` | set-values, get-values |167| Format cells | `range` | set-number-format |168| Create tables from data | `table` | create |169| Add table to Power Pivot | `table` | add-to-data-model |170| Create DAX formulas | `datamodel` | create-measure |171| Create PivotTables | `pivottable` | create, create-from-datamodel |172| Filter with slicers | `slicer` | set-slicer-selection |173| Create charts | `chart` | create-from-range |174| Control calculation mode | `calculation_mode` | get-mode, set-mode, calculate |175| Visual verification | `screenshot` | capture, capture-sheet |176 177## Reference Documentation178 179See `references/` for detailed guidance:180 181- [Core execution rules and LLM guidelines](./references/behavioral-rules.md)182- [Common mistakes to avoid](./references/anti-patterns.md)183- [Bulk write performance optimization](./references/calculation.md)184- [Data Model constraints and patterns](./references/workflows.md)185- [Charts and formatting](./references/chart.md)186- [Conditional formatting operations](./references/conditionalformat.md)187- [Dashboard and report best practices](./references/dashboard.md)188- [Data Model/DAX specifics](./references/datamodel.md)189- [DMV query reference for Data Model analysis](./references/dmv-reference.md)190- [Power Query M code syntax reference](./references/m-code-syntax.md)191- [PivotTable operations](./references/pivottable.md)192- [Power Query specifics](./references/powerquery.md)193- [Range operations and number formats](./references/range.md)194- [Screenshot and visual verification](./references/screenshot.md)195- [Slicer operations](./references/slicer.md)196- [Table operations](./references/table.md)197- [Worksheet operations](./references/worksheet.md)