Excel Mcp

Excel MCP Server Skill

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

Workflow Checklist

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.

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:

Workflow:

1. range set-values (data is now in cells)
2. range set-number-format (apply format)

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 create tableName="SalesData" rangeAddress="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 sessionId
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-datamodel') → 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', mCode: '...') → 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:

{
  "success": false,
  "errorMessage": "Table 'Sales' not found in Data Model",
  "suggestedNextActions": ["table(action: 'add-to-data-model', tableName: 'Sales')"]
}

Tool Selection Quick Reference

Reference Documentation

See references/ for detailed guidance:

• Core execution rules and LLM guidelines
• Common mistakes to avoid
• Bulk write performance optimization
• Data Model constraints and patterns
• Charts and formatting
• Conditional formatting operations
• Dashboard and report best practices
• Data Model/DAX specifics
• DMV query reference for Data Model analysis
• Excel agent mode and advanced automation
• Gotchas and known limits
• Power Query M code syntax reference
• PivotTable operations
• Power Query specifics
• Range operations and number formats
• Screenshot and visual verification
• Slicer operations
• Table operations
• Window and visibility operations
• Worksheet operations

Excel MCP Server Skill

Agent Skill for AI assistants using the Excel MCP Server via the Model Context Protocol.

Best For

• Conversational AI (Claude Desktop, VS Code Chat)
• Exploratory automation with iterative reasoning
• Self-healing workflows needing rich introspection
• Long-running autonomous tasks with continuous context

Installation

GitHub Copilot

The Excel MCP Server VS Code extension installs this skill automatically to ~/.copilot/skills/excel-mcp/.

Enable skills in VS Code settings:

{
  "chat.useAgentSkills": true
}

Other Platforms

Extract to your AI assistant's skills directory:

Or use npx:

# Interactive - prompts to select excel-cli, excel-mcp, or both
npx skills add sbroenne/mcp-server-excel

# Or specify directly
npx skills add sbroenne/mcp-server-excel --skill excel-mcp

Contents

excel-mcp/
├── SKILL.md           # Main skill definition with MCP tool guidance
├── VERSION            # Version tracking
├── README.md          # This file
└── references/        # Detailed domain-specific guidance
    ├── anti-patterns.md
    ├── behavioral-rules.md
    ├── chart.md
    ├── conditionalformat.md
    ├── dashboard.md
    ├── datamodel.md
    ├── dmv-reference.md
    ├── excel_agent_mode.md
    ├── gotchas.md
    ├── m-code-syntax.md
    ├── pivottable.md
    ├── powerquery.md
    ├── range.md
    ├── screenshot.md
    ├── slicer.md
    ├── table.md
    ├── window.md
    └── worksheet.md

MCP Server Setup

The skill works with the Excel MCP Server. See Installation Guide for setup instructions.

Related

• Excel CLI Skill - For coding agents preferring CLI tools
• Documentation
• GitHub Repository

Anti-Patterns to Avoid

These patterns cause data loss, poor performance, or user frustration. Avoid them.

Redundant Formatting Anti-Pattern

The Problem

Applying the same formatting to the same range more than once in a workflow:

WRONG: Applying bold repeatedly

range_format(action: 'format-range', rangeAddress: 'A1:D1', bold: true)
// ... other operations ...
range_format(action: 'format-range', rangeAddress: 'A1:D1', bold: true, fillColor: '#4472C4')
// Bold was already applied - the second call re-applies it unnecessarily

Also wrong: calling format-range separately for each property instead of combining:

WRONG: Separate calls for each property

range_format(action: 'format-range', rangeAddress: 'A1:D1', bold: true)
range_format(action: 'format-range', rangeAddress: 'A1:D1', fillColor: '#4472C4')
range_format(action: 'format-range', rangeAddress: 'A1:D1', fontColor: '#FFFFFF')
range_format(action: 'format-range', rangeAddress: 'A1:D1', horizontalAlignment: 'center')

The Solution

Apply all formatting properties for a range in one format-range call:

CORRECT: One call per range

range_format(action: 'format-range', rangeAddress: 'A1:D1',
    bold: true, fillColor: '#4472C4', fontColor: '#FFFFFF', horizontalAlignment: 'center')

Apply each formatting operation once. If a subsequent step explicitly changes a property (e.g., "now make the title red"), apply it again — otherwise don't.

If the same formatting applies to multiple disjoint ranges, do not repeat format-range for each target:

WRONG: Repeating the same shared formatting payload

range_format(action: 'format-range', rangeAddress: 'A1:D1', bold: true, fillColor: '#4472C4', fontColor: '#FFFFFF')
range_format(action: 'format-range', rangeAddress: 'A12:D12', bold: true, fillColor: '#4472C4', fontColor: '#FFFFFF')
range_format(action: 'format-range', rangeAddress: 'A24:D24', bold: true, fillColor: '#4472C4', fontColor: '#FFFFFF')

CORRECT: One shared multi-range formatting call

range_format(action: 'format-ranges',
    rangeAddresses: ['A1:D1', 'A12:D12', 'A24:D24'],
    bold: true, fillColor: '#4472C4', fontColor: '#FFFFFF')

When Multiple Calls ARE Appropriate

• Applying different formatting to different ranges
• A later step explicitly overrides a previously set property
• Applying a style (set-style) on top of individual properties (different actions)

Wrong Style System Anti-Pattern

The Problem

Applying range_format to cells that belong to an object with its own style system:

WRONG: Formatting a table header row with range_format

table(action: 'create', tableName: 'Sales', rangeAddress: 'A1:D10')
range_format(action: 'format-range', rangeAddress: 'A1:D1', bold: true, fillColor: '#4472C4')
// The table style already controls header appearance — this creates an inconsistent override

WRONG: Formatting PivotTable cells

pivottable(action: 'create-from-table', ...)
range_format(action: 'format-range', rangeAddress: 'B3:B20', fillColor: '#E2EFDA')
// Formatting is wiped on the next pivottable(refresh)

The Solution

Use the style system that belongs to each object type:

CORRECT: Table visual styling — one call at creation or via set-style

table(action: 'create', tableName: 'Sales', rangeAddress: 'A1:D10',
    tableStyle: 'TableStyleMedium2')

Delete-and-Rebuild Anti-Pattern

The Problem

Deleting entire structures to make small changes:

WRONG: User wants to update cell B5

table(action: 'delete', tableName: 'SalesData')
range(action: 'set-values', values: [[entire dataset with B5 fixed]])
table(action: 'create', tableName: 'SalesData', ...)

This destroys:

• Cell formatting
• Conditional formatting rules
• Data validation
• Named ranges pointing to the table
• PivotTable connections
• DAX measures referencing the table

The Solution

Use targeted modifications:

CORRECT: Update only the changed cell

range(action: 'set-values', rangeAddress: 'B5', values: [[newValue]])

When Rebuild IS Appropriate

• Fundamentally restructuring data (different columns)
• Converting between table types
• User explicitly requests replacement

Discovery Loop Anti-Pattern

The Problem

Repeating file(list), worksheet(list), or table(list) multiple times without taking action:

WRONG: Looping on discovery after an error

worksheet(action: 'list')           → gets sheet list
worksheet(action: 'list')           → gets same sheet list again
file(action: 'list')                → gets session list
worksheet(action: 'list')           → gets same sheet list again
... (dozens of repetitions)

This burns tokens, costs money, and never completes the task.

The Solution

If you already have a sessionId, use it. Do not rediscover:

CORRECT: Use the sessionId you already have

Error: "session expired"
→ file(action: 'open', path: original_path)  ← Re-open once, get new sessionId
→ Continue with the new sessionId immediately

The Rule

• Max 2 retries for any session or file operation
• After 2 failures: stop retrying, report the error, end your response
• Never call `list`, `worksheet(list)`, or `table(list)` more than twice in a row without doing something with the result

Confirmation Loop Anti-Pattern

The Problem

Asking for confirmation on every operation:

WRONG:

User: "Create a sales report"
AI: "Would you like me to create a new Excel file for the sales report?"
User: "Yes"
AI: "What would you like to name the file?"
User: "sales_report.xlsx"
AI: "Should I create it in your Documents folder?"
User: "Yes"
AI: "The file has been created. Would you like me to add headers?"
... (10 more questions)

The Solution

Execute with reasonable defaults, report results:

CORRECT:

User: "Create a sales report"
AI: "Created sales report at C:\Users\You\Documents\sales_report.xlsx with the following structure:
- Sheet 'Summary' with headers: Date, Product, Region, Sales
- Ready for data entry

What data would you like to add?"

When to Ask

• Genuinely ambiguous requests
• Destructive operations on existing data
• User explicitly asked for options

Wrong Cell Update Anti-Pattern

The Problem

Reading entire range, modifying in memory, writing entire range back:

WRONG: Update one cell by rewriting thousands

data = range(action: 'get-values', rangeAddress: 'A1:Z1000')
data[4][1] = "new value"  // Modify row 5, column B
range(action: 'set-values', rangeAddress: 'A1', values: data)

This:

• Transfers megabytes unnecessarily
• Risks data corruption if interrupted
• Destroys formulas (values only, not formulas)
• Loses cell formatting

The Solution

Write only the changed cells:

CORRECT: Direct cell update

range(action: 'set-values', rangeAddress: 'B5', values: [["new value"]])

Session Leak Anti-Pattern

The Problem

Opening files without closing them:

WRONG: Session accumulation

file(action: 'open', filePath: 'file1.xlsx')  // Session 1
file(action: 'open', filePath: 'file2.xlsx')  // Session 2
file(action: 'open', filePath: 'file3.xlsx')  // Session 3
// ... never closed

Results:

• Excel processes accumulate
• Memory usage grows
• File locks prevent other access
• System becomes unresponsive

The Solution

Always close sessions:

CORRECT: Proper lifecycle

session1 = file(action: 'open', path: 'file1.xlsx')
// ... work with file1 ...
file(action: 'close', sessionId: session1, save: true)

session2 = file(action: 'open', path: 'file2.xlsx')
// ... work with file2 ...
file(action: 'close', sessionId: session2, save: true)

Ignoring Error Context Anti-Pattern

The Problem

Retrying failed operations without reading the error:

WRONG: Blind retry

datamodel(action: 'create-measure', ...) → Error: Table not in Data Model
datamodel(action: 'create-measure', ...) → Error: Table not in Data Model
datamodel(action: 'create-measure', ...) → Error: Table not in Data Model

The Solution

Read and act on error context:

CORRECT: Error-driven correction

datamodel(action: 'create-measure', ...) 
→ Error: Table 'Sales' not in Data Model
→ Suggested: table(action: 'add-to-data-model', tableName: 'Sales')

table(action: 'add-to-data-model', tableName: 'Sales')  // Fix prerequisite
datamodel(action: 'create-measure', ...)  // Now succeeds

Number Format Locale Anti-Pattern

The Problem

Using locale-specific format codes:

WRONG: German/European format

range(action: 'set-number-format', formatCode: '#.##0,00')  // German
range(action: 'set-number-format', formatCode: '# ##0,00')  // French

The Solution

Always use US format codes (Excel translates automatically):

CORRECT: US format codes (universal)

range(action: 'set-number-format', formatCode: '#,##0.00')

Excel displays the result in the user's locale setting, but the API requires US format input.

Load Destination Mismatch Anti-Pattern

The Problem

Wrong load destination for the workflow:

WRONG: Loading to worksheet when DAX is needed

powerquery(action: 'create', loadDestination: 'worksheet', ...)
datamodel(action: 'create-measure', ...)  // FAILS: table not in Data Model

The Solution

Match load destination to workflow:

CORRECT: Load to Data Model for DAX workflows

powerquery(action: 'create', loadDestination: 'data-model', ...)
powerquery(action: 'refresh', ...)
datamodel(action: 'create-measure', ...)  // Works

Skipping Power Query Evaluate Anti-Pattern

The Problem

Creating or updating Power Query queries without testing M code first:

WRONG: Creating permanent query with untested M code

powerquery(action: 'create', mCode: '...', ...)
// M code has syntax error → COM exception with cryptic message
// Now workbook is polluted with broken query

This causes:

• Broken queries persisted in workbook
• Cryptic COM exceptions instead of helpful M error messages
• Need manual Excel cleanup to remove broken queries
• Wasted time debugging in wrong layer

The Solution

Always evaluate M code BEFORE creating permanent queries:

CORRECT: Test-first development workflow

// Step 1: Test M code without persisting
powerquery(action: 'evaluate', mCode: '...')
// → Returns actual data preview with columns and rows
// → Better error messages if M code has issues

// Step 2: Create permanent query with validated code
powerquery(action: 'create', mCode: '...', ...)

// Step 3: Load data to destination
powerquery(action: 'refresh', ...)

Benefits:

• Catch syntax errors and missing sources BEFORE persisting
• See actual data preview (columns, sample rows)
• Better error messages than COM exceptions
• No cleanup needed - temporary objects auto-deleted
• Like a REPL for M code

When Evaluate IS Optional

• Trivial literal tables: #table({"Column1"}, {{123}})
• M code already validated in previous evaluate call
• Copying known-working query from another workbook

When to Retry With Evaluate

If create/update fails with COM error, use evaluate to get detailed Power Query error message:

powerquery(action: 'create', ...)  // → COM exception
powerquery(action: 'evaluate', mCode: '...')  // → Detailed M error
// Fix M code based on error
powerquery(action: 'create', ...)  // → Success

Behavioral Rules for Excel MCP Operations

These rules ensure efficient and reliable Excel automation. AI assistants should follow these guidelines when executing Excel operations.

System Prompt Rules (LLM-Validated)

These rules are validated by automated LLM tests and MUST be followed:

• Execute tasks immediately without asking for confirmation
• Never ask clarifying questions - make reasonable assumptions and proceed
• Ask the user whether they want Excel visible or hidden when starting multi-step tasks
• When the user asks to "show Excel" or "watch" the work, use window(show) + window(arrange) to position it
• Format Excel files professionally (proper column widths, headers, number formats)
• Always format data ranges as Excel Tables (not plain ranges)
• Always end with a text summary - never end on just a tool call or command

CRITICAL: No Clarification Questions

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

Instead, discover the information yourself:

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

Core Execution Rules

Execute Immediately

Do NOT ask clarifying questions for standard operations. Proceed with reasonable defaults:

• File creation: Create the file and report the path
• Data operations: Execute the operation and report results
• Formatting: Apply formatting and confirm completion

When to ask: Only when the request is genuinely ambiguous (e.g., "update the data" without specifying what data or which file).

Ask About Excel Visibility

When starting a multi-step task, ask the user whether they want Excel visible or hidden. Present two clear action card choices:

Watch me work — Show Excel side-by-side so you see every change live. Operations run slightly slower because Excel renders each update on screen.

>

Work in background — Keep Excel hidden for maximum speed. You won't see changes until the task is done, but operations complete faster.

Skip asking when the user has already stated a preference:

• User says "show me Excel", "let me watch", "I want to see it" → Show immediately
• User says "just do it", "work in background" → Keep hidden
• Simple one-shot operations (e.g., "what's in A1?") → Keep hidden, no need to ask

If the user doesn't respond, keep Excel hidden.

How to show Excel:

1. window(action: 'show')                         → Make visible
2. window(action: 'arrange', preset: 'left-half') → Position for side-by-side

Do NOT:

• Show Excel without the user choosing to see it
• Tell users to look at Excel windows unless Excel is visible
• Reference Excel UI elements when Excel is hidden
• Suggest manual Excel interactions

Format Professionally

When creating or modifying Excel files:

• Set appropriate column widths for content
• Apply header formatting (bold, filters)
• Use proper number formats (currency, dates, percentages) with range set-number-format
• Auto-fit variable-width data with range_format auto-fit-columns or range_format auto-fit-rows
• Format data as Excel Tables (not plain ranges)
• When the same visual styling applies to multiple disjoint ranges on one sheet, use range_format format-ranges

Tool split to remember:

• range owns number display formats such as dates, currency, percentages, and text display
• range_format owns visual styling, validation, auto-fit, and explicit width/height changes

Use `set-style` for semantic status labels and document structure:

• Good / Bad / Neutral — colour-coded status cells (green/red/yellow fills, theme-aware)
• Heading 1 / Heading 2 / Title — document hierarchy
• Normal — reset all formatting

Use `format-range` for visual layout (header rows, custom colours) — ALL properties in ONE call:

• set-style('Heading 1') does NOT apply a fill colour; if you want a coloured header row use format-range
• Pass bold, fillColor, fontColor, and alignment together in a single call — do not call format-range multiple times for the same range
• If the same formatting payload repeats across multiple non-contiguous ranges, prefer one format-ranges call over repeated format-range calls

Apply each formatting operation once — do not reapply the same properties to the same range unless a later step explicitly changes them.

Format Cells by Data Type (CRITICAL)

Always apply number formats after setting values. Without formatting:

• Dates appear as serial numbers (45678 instead of 2025-01-22)
• Currency appears as plain numbers (1234.56 instead of $1,234.56)
• Percentages appear as decimals (0.15 instead of 15%)

Common format codes (US locale, auto-translated):

Workflow:

1. range set-values (data is now in cells)
2. range set-number-format (apply format to range)
3. range_format auto-fit-columns (when content would clip at default width)

Format Tabular Data as Excel Tables

Always convert tabular data to Excel Tables (ListObjects):

1. range set-values (write data including headers)
2. table create tableName="SalesData" rangeAddress="A1:D100"

Why Tables over plain ranges:

• Structured references: =SUM(Sales[Amount]) instead of =SUM(B2:B100)
• Auto-expand when rows are added
• Built-in filtering, sorting, and banded rows
• Required for add-to-data-model action (Data Model/DAX)
• Named reference for Power Query: Excel.CurrentWorkbook(){[Name="SalesData"]}

When NOT to use Tables:

• Single-cell parameters (use named ranges instead)
• Layout areas with merged cells
• Print-formatted reports with specific spacing

Named range listing: namedrange list returns visible user-defined names. Hidden/internal Excel names, including Power Query ExternalData_* and AutoFilter names, are omitted before value inspection. Large named ranges return metadata without a value preview; use namedrange read or range get-values when the actual value is needed.

Report Results

After completing operations, report:

• What was created/modified
• File path (for new files)
• Any relevant statistics (row counts, etc.)

CRITICAL: Always End With a Text Response

NEVER end your turn with only a tool call or command execution. After all operations are complete, you MUST provide a text message summarizing what was accomplished.

Why: Users and automation expect a text confirmation. A silent tool call or command with no follow-up text is an incomplete response.

Session Lifecycle

Always close sessions when done:

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

Why: Unclosed sessions leave Excel processes running, consuming memory and locking files.

Format Results as Tables

When presenting data to users, format as Markdown tables:

| Column A | Column B | Column C |
|----------|----------|----------|
| Value 1  | Value 2  | Value 3  |

NOT as raw JSON arrays: [["Column A","Column B"],["Value 1","Value 2"]]

Data Model Output Rules

Choose the Right Display Method

When displaying Data Model data:

Why: PivotTables add UI complexity (field panes, refresh prompts) that's unnecessary for simple data display. DAX-backed tables are cleaner for presenting query results.

Chart Data Model Data Directly

When creating charts from Data Model:

• Use: chart create-from-pivottable (creates PivotChart)
• NOT: Create PivotTable → Create separate Chart from the PivotTable

Why: A PivotChart is a single object connected to the Data Model. Creating PivotTable + Chart is redundant - two objects instead of one.

Data Modification Rules

Verify Before Delete

Before deleting tables, worksheets, or named ranges:

1. List existing items first 2. Confirm the exact name exists 3. Delete the specified item

Why: Delete operations cannot be undone. Verification prevents accidental data loss.

Targeted Updates Over Wholesale Replace

When updating data:

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

Why: Targeted updates preserve formatting, formulas, and references that wholesale replacement destroys.

Save Explicitly

Call file(action: 'close', save: true) to persist changes:

• Operations modify the in-memory workbook
• Changes are NOT automatically saved to disk
• Session termination WITHOUT save loses all changes

Workflow Sequencing Rules

Data Model Prerequisites

DAX operations require tables in the Data Model:

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

Skipping Step 2 causes DAX operations to fail with "table not found".

Power Query Load Destinations

Choose load destination based on workflow:

Refresh After Create

powerquery(action: 'create') imports the M code but does NOT execute it:

Step 1: powerquery(action: 'create', ...) → Query created
Step 2: powerquery(action: 'refresh', queryName: '...') → Data loaded

Without refresh, the query exists but contains no data.

Error Handling Rules

Interpret Error Messages

Excel MCP errors include actionable context:

{
  "success": false,
  "errorMessage": "Table 'Sales' not found in Data Model",
  "suggestedNextActions": ["table(action: 'add-to-data-model', tableName: 'Sales')"]
}

Follow suggestedNextActions when provided.

Retry with Corrections

If an operation fails:

1. Read the error message carefully 2. Check prerequisites (session, table in Data Model, etc.) 3. Retry with corrected parameters

Do NOT immediately re-run the same failing command.

Report Failures Clearly

When operations fail:

• State what was attempted
• Explain what went wrong
• Suggest the corrective action

Good: "Failed to add DAX measure: Table 'Sales' is not in the Data Model. Use table(action: 'add-to-data-model') first."

Bad: "An error occurred."

calculation_mode - Bulk Write Performance Optimization

Tool

• `calculation_mode`: Control Excel's automatic recalculation behavior

When to Use

Use calculation_mode to optimize performance when:

• Writing 10+ cells of data/formulas in a single operation
• Creating tables with multiple rows and calculated columns
• Performance matters more than immediate feedback (no need to wait for each formula to recalculate)

When NOT Needed

• Small edits (1-5 cells)
• When you need immediate calculation results to verify data
• Reading formulas (use range get-formulas — works in any mode)
• Single worksheet operations without bulk writes

Workflow

Always follow this 4-step pattern for bulk operations:

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

Why this pattern:

• Step 1: Prevents Excel from recalculating after EVERY cell write (10+ recalcs → 1 recalc)
• Step 2: All writes happen at normal speed
• Step 3: Single recalculation computes all formulas together
• Step 4: Restores default Excel behavior so subsequent edits auto-recalc

Actions

Common Scenarios

Scenario: Create Sales Table with Formulas

Task: Add 100 rows of product data with unit price, quantity, and total formulas.

1. calculation_mode set-mode manual
2. range set-values (add 100 rows: columns A-C values)
3. range set-formulas (add 100 total formulas in column D)
4. calculation_mode calculate workbook  (calculates all 100 formulas at once)
5. calculation_mode set-mode automatic

Performance: ~2-3 seconds total (vs ~30+ seconds if automatic after every cell)

Scenario: Dashboard with Multiple Sections

Task: Create 5 sections with headers, data, and subtotal formulas.

1. calculation_mode set-mode manual
2. Section 1: set-values + set-formulas
3. Section 2: set-values + set-formulas
4. Section 3: set-values + set-formulas
5. Section 4: set-values + set-formulas
6. Section 5: set-values + set-formulas
7. calculation_mode calculate workbook  (all 5 sections recalc together)
8. calculation_mode set-mode automatic

Best Practices

1. Always restore automatic mode - Never leave manual mode enabled, users expect auto-recalc 2. Use workbook scope for calculate - Simplest and fastest 3. Verify calculation completed - After step 3, data should show final calculated values 4. Test with smaller dataset first - If building a large operation, test with 10 rows first

Excel Charts Reference

Tools

• `chart`: Create charts, manage positioning and data sources
• `chart_config`: Configure chart appearance, formatting, and analysis features

Chart Creation

From Range

chart(create-from-range, chartType, sourceRange, sheetName)

Best for: Simple data in worksheet ranges

From PivotTable (PivotChart)

chart(create-from-pivottable, pivotTableName)

Best for: Data Model data - creates a single PivotChart object (don't create separate PivotTable + Chart)

From Table

chart(create-from-table, tableName, chartType)

Best for: Excel Tables with structured references

Chart Types

Common types: ColumnClustered, Line, Pie, Bar, Area, XYScatter, Doughnut

Specialized: Waterfall, Funnel, Treemap, Sunburst, BoxWhisker, Histogram, Pareto

Configuration Actions (chart_config)

Series Management

• add-series: Add data series with valuesRange and optional categoryRange
• remove-series: Remove series by index (1-based)
• set-source-range: Replace entire chart data source

Titles and Labels

• set-title: Set chart title (empty string hides)
• set-axis-title: Set axis labels (Category, Value, CategorySecondary, ValueSecondary)
• set-data-labels: Configure data labels (position, showValue, showCategory, showPercentage, showSeriesName, showLegendKey)

Axis Formatting

• get-axis-scale: Get min, max, majorUnit, minorUnit, and auto flags
• set-axis-scale: Configure scale properties
• get-axis-number-format: Get current tick label format
• set-axis-number-format: Format axis numbers (e.g., "$#,##0,,\"M\"" for millions)

Gridlines

• get-gridlines: Check visibility state
• set-gridlines: Show/hide major/minor gridlines

Series Formatting

• set-series-format: Configure markers (style, size, foregroundColor, backgroundColor)

Trendlines

• list-trendlines: View all trendlines on a series
• add-trendline: Add Linear, Exponential, Logarithmic, Polynomial, Power, or MovingAverage trendline
• delete-trendline: Remove trendline by index
• set-trendline: Configure display (equation, R² value) and forecasting (forward, backward periods)

Styling

• show-legend: Control legend visibility and position (Bottom, Corner, Top, Right, Left)
• set-style: Apply Excel chart styles (1-48)

Trendline Details

Types

Parameters

• order: Required for Polynomial (2-6, default 2)
• period: Required for MovingAverage (2+, default 2)
• forward/backward: Forecast periods ahead/behind data
• intercept: Force trend through specific Y value
• displayEquation: Show formula on chart
• displayRSquared: Show R² goodness-of-fit value

Common Workflows

Create Chart with Formatting

1. chart(create-from-range) → chartName
2. chart_config(set-title, title="Monthly Sales")
3. chart_config(set-axis-title, axis="Value", title="Revenue ($)")
4. chart_config(set-axis-number-format, axis="Value", numberFormat="$#,##0")
5. chart_config(set-data-labels, position="OutsideEnd", showValue=true)

Add Analysis

1. chart_config(add-trendline, trendlineType="Linear", displayEquation=true, displayRSquared=true)
2. chart_config(set-trendline, forward=3) # Forecast 3 periods ahead

Best Practices

1. PivotCharts for Data Model: Use create-from-pivottable not PivotTable + separate chart 2. Format numbers: Set axis number format for readability 3. Use gridlines sparingly: Minor gridlines often add clutter 4. Trendlines for insights: Add R² to show fit quality 5. Data labels placement: OutsideEnd for bar charts, Center for pie charts

Chart Positioning

Charts support three positioning modes, listed in order of preference:

1. targetRange (PREFERRED - One Step)

chart(create-from-range, sourceRange='A1:B10', chartType='Line', targetRange='F2:K15')

Creates chart AND positions it to the cell range in one call. No point math needed.

2. Auto-Positioning (No Position Specified)

When you omit both targetRange and left/top, the chart is automatically placed below all existing content (data ranges + other charts) with 10pt padding. This prevents overlap automatically.

chart(create-from-range, sourceRange='A1:B10', chartType='Line')
# → Chart auto-positioned below the used range and any existing charts

3. Manual Coordinates

chart(create-from-range, sourceRange='A1:B10', left=360, top=20)
# left/top in points (72 points = 1 inch)

Collision Detection (Automatic)

All chart create, move, and fit-to-range operations automatically check for overlaps with data and other charts. If collisions are detected, the result includes an OVERLAP WARNING message. Always check the result message and fix overlaps before proceeding.

Result example with collision warning:
{
  "success": true,
  "chartName": "Chart 1",
  "message": "OVERLAP WARNING: Chart overlaps data area $A$1:$D$20. Use chart fit-to-range to reposition, or screenshot capture-sheet to verify layout."
}

If you see an overlap warning: 1. Use chart(fit-to-range, chartName, rangeAddress='F2:K15') to reposition 2. Or use chart(move, chartName, left=..., top=...) to adjust 3. Always follow up with screenshot(capture-sheet) to verify

Position Estimates

• Rows: ~15 points per row (varies with row height)
• Columns: ~60 points per column (varies with column width)
• Default chart: 400×300 points

Positioning Workflow

1. Preferred: Use targetRange='F2:K15' in create call — avoids all overlap issues 2. Alternative: Omit position — auto-positioning places chart below content 3. Manual: get-used-range → calculate coordinates → specify left/top 4. Always verify: Use screenshot(capture-sheet) to visually confirm layout

Multi-Chart Layout (CRITICAL)

When creating dashboards with multiple charts, every chart needs explicit positioning:

Grid Layout Pattern

Data at A1:D10. Place 4 charts in a 2×2 grid below data:

chart(create-from-range, ..., targetRange='A12:F25')   # Top-left
chart(create-from-range, ..., targetRange='G12:L25')   # Top-right
chart(create-from-range, ..., targetRange='A27:F40')   # Bottom-left
chart(create-from-range, ..., targetRange='G27:L40')   # Bottom-right
screenshot(capture-sheet) → Verify no overlaps

Rules

• Use targetRange for every chart in multi-chart layouts — auto-positioning stacks vertically
• Leave at least 1-2 rows/columns gap between charts
• If any chart result includes an overlap warning, fix it before creating the next chart
• Take a final screenshot(capture-sheet) to verify the complete layout

Claude Desktop Configuration

Excel MCP Server works with Claude Desktop on Windows, but requires specific configuration for the Windows container environment.

Configuration Location

Claude Desktop config file:

%APPDATA%\Claude\claude_desktop_config.json

Basic Configuration

{
  "mcpServers": {
    "excel-mcp": {
      "command": "excel-mcp-server.exe",
      "args": []
    }
  }
}

Or using the .NET tool:

{
  "mcpServers": {
    "excel-mcp": {
      "command": "dotnet",
      "args": ["excel-mcp-server"]
    }
  }
}

Windows Container Considerations

Claude Desktop runs in a Windows container with specific constraints:

File System Access

The container has limited file system access. Excel files should be in accessible locations:

• User Documents: C:\Users\<username>\Documents\
• User Desktop: C:\Users\<username>\Desktop\
• Temp directory: %TEMP% or C:\Users\<username>\AppData\Local\Temp\

Recommendation: Work with files in your Documents folder.

Excel Instance

• Excel MCP Server manages its own Excel instance via COM automation
• The Excel window may be visible or hidden depending on operation
• Long-running operations show Excel's progress indicators

Session Persistence

Sessions are tied to the Claude Desktop session:

• Closing Claude Desktop terminates active Excel sessions
• Unsaved changes may be lost
• Use explicit file(action: 'close', save: true) to persist work

Recommended Workflow

1. Create or open file in accessible location:
   file(action: 'create', filePath: 'C:\\Users\\Me\\Documents\\report.xlsx')

2. Perform operations with returned sessionId

3. Explicitly save and close when done:
   file(action: 'close', sessionId: '...', save: true)

Troubleshooting

"Excel not found" Error

• Ensure Microsoft Excel is installed on the Windows system
• Excel 2016, 2019, 2021, or Microsoft 365 required

"Access denied" Error

• Check file path is in accessible directory
• Ensure file is not open in another Excel instance
• Try using Documents folder instead of other locations

"COM timeout" Error

• Excel may be showing a dialog - check for visible Excel window
• Operation may be long-running - wait for completion
• Restart Claude Desktop if Excel becomes unresponsive

VBA Operations Fail

VBA requires explicit trust setting in Excel: 1. Open Excel Options → Trust Center → Trust Center Settings 2. Enable "Trust access to the VBA project object model" 3. Restart Excel MCP Server

MCPB Bundle Alternative

For simplified installation, use the MCPB bundle which auto-configures Claude Desktop:

1. Download excel-mcp-bundle.mcpb from releases 2. Double-click to install 3. Restart Claude Desktop

See the main repository for MCPB installation instructions.

````markdown

conditionalformat - Server Quirks

Rule Types:

Operators (for cell-value type):

Format Options:

• interiorColor: Background fill color as #RRGGBB hex
• fontColor: Text color as #RRGGBB hex
• fontBold: true or false
• fontItalic: true or false
• borderStyle: Excel border style name
• borderColor: Border color as #RRGGBB hex

Actions:

Formula Notes:

• For cell-value type: formula1/formula2 can be numbers, strings, or cell references
• For expression type: formula must return TRUE/FALSE
• Formulas use the top-left cell perspective (e.g., =$A1>100 for relative rows)
• Use absolute references ($A$1) when comparing to a fixed cell

Examples:

Highlight cells greater than 100:

{
  "action": "add-rule",
  "rangeAddress": "A1:A10",
  "ruleType": "cell-value",
  "operatorType": "greater",
  "formula1": "100",
  "interiorColor": "#FFFF00"
}

Highlight cells between 50 and 100:

{
  "action": "add-rule",
  "rangeAddress": "A1:A10",
  "ruleType": "cell-value",
  "operatorType": "between",
  "formula1": "50",
  "formula2": "100",
  "interiorColor": "#90EE90"
}

Highlight row if column A is "Active" (expression):

{
  "action": "add-rule",
  "rangeAddress": "A1:D10",
  "ruleType": "expression",
  "formula": "=$A1=\"Active\"",
  "interiorColor": "#90EE90"
}

CLI Usage:

# Add rule: highlight values > 100 in yellow
excelcli conditionalformat add-rule --session <id> --sheet-name "Data" --range-address "B2:B100" `
  --rule-type "cell-value" --operator-type "greater" --formula1 "100" --interior-color "#FFFF00"

# Add expression rule: highlight entire row if column A is "Error"
excelcli conditionalformat add-rule --session <id> --sheet-name "Data" --range-address "A2:E100" `
  --rule-type "expression" --formula1 "=`$A2=`"Error`"" --interior-color "#FF0000" --font-color "#FFFFFF"

# Clear all rules from range
excelcli conditionalformat clear-rules --session <id> --sheet-name "Data" --range-address "A1:E100"

Common Mistakes:

• Using cell-value type without operatorType → Error
• Using between without both formula1 AND formula2 → Error
• Forgetting $ in expression formulas → Rule applies incorrectly across rows/columns
• Colors without # prefix → May not apply correctly

Best Practices:

1. Test expression formulas in Excel first to verify logic 2. Use clear-rules before applying new rules if replacing existing formatting 3. For row-based highlighting, apply rule to full range (not just one column) 4. Use relative row references ($A1) and absolute column references for row highlighting

````

Dashboard & Report Best Practices

The Professional Report Workflow

Every report or dashboard should follow this sequence:

1. Structure data → Excel Tables (never plain ranges)
2. Format values → Number formats by data type
3. Add visuals → Charts with explicit positioning
4. Verify layout → Screenshot to confirm no overlaps
5. Save and close → Persist changes

Step 1: Structure Data as Excel Tables

Always use Excel Tables for tabular data:

range(set-values, rangeAddress='A1', values=[[headers + data]])
table(create, tableName='SalesData', rangeAddress='A1:D20')

Why Tables matter:

• Auto-filters on every column
• Banded rows for readability
• Structured references in formulas
• Required for Data Model / DAX / PivotTables
• Auto-expand when new rows are added

Step 2: Format Values by Data Type

Apply number formats AFTER setting values — not before:

Always use US format codes — Excel translates automatically to the user's locale.

Step 3: Position Charts with No Overlaps

Charts have automatic collision detection and three positioning modes:

Single Chart (Auto-Position or targetRange)

# Option A: targetRange (explicit cell placement)
chart(create-from-range, sourceRange='A1:D20', targetRange='F2:K15')

# Option B: Omit position — auto-places below content
chart(create-from-range, sourceRange='A1:D20', chartType='Line')
# → Automatically positioned below the used range

Multiple Charts (Dashboard) — Always Use targetRange

Place in a grid pattern below data:

Chart 1: targetRange='A22:F35'    (top-left)
Chart 2: targetRange='G22:L35'    (top-right)
Chart 3: targetRange='A37:F50'    (bottom-left)
Chart 4: targetRange='G37:L50'    (bottom-right)

Collision Detection

All chart operations automatically warn about overlaps. If a result includes an OVERLAP WARNING message: 1. Use chart(fit-to-range) to reposition 2. Take screenshot(capture-sheet) to verify

Rules:

• Use targetRange for multi-chart layouts — auto-positioning stacks vertically
• Leave 1-2 rows/columns gap between charts
• Place charts BELOW the data area, not beside it (more room)
• Keep chart sizes consistent (same row/column span)
• Always check result messages for overlap warnings

Step 4: Verify with Screenshot

Always take a screenshot after creating charts or complex layouts:

screenshot(capture-sheet)
→ Confirm: no overlaps, professional spacing, readable labels
→ If issues found: chart(fit-to-range) to reposition, then screenshot again

Common Dashboard Layouts

Summary Dashboard (Data + 2 Charts)

A1:D10    → Data table (formatted as Excel Table)
A12:F25   → Main chart (bar/column)
G12:L25   → Supporting chart (pie/line)

Analytics Dashboard (4 Charts)

A1:D10    → Source data table
A12:F25   → Chart 1 (trend line)
G12:L25   → Chart 2 (distribution pie)
A27:F40   → Chart 3 (comparison bar)
G27:L40   → Chart 4 (detail scatter)

Executive Report (Summary + Detail)

Sheet "Summary":
  A1:D5     → KPI table (small, formatted)
  A7:F20    → Summary chart

Sheet "Detail":
  A1:H100   → Full data table
  A102:H120 → Detail charts

Formatting Checklist

• [ ] Data in Excel Tables (not plain ranges)
• [ ] Number formats applied (currency, dates, percentages)
• [ ] Column widths appropriate for content
• [ ] Chart titles are descriptive
• [ ] Chart axis labels formatted (currency, percentages)
• [ ] No chart overlaps with data or other charts
• [ ] Consistent chart sizes in dashboards
• [ ] Screenshot taken to verify final layout

datamodel - Server Quirks

PREREQUISITE: Tables must be added to Data Model first!

The Data Model (Power Pivot) only contains tables that were explicitly added. You CANNOT create DAX measures on tables that aren't in the Data Model.

MSOLAP Prerequisite (for evaluate/execute-dmv)

The `evaluate` and `execute-dmv` actions require Microsoft Analysis Services OLE DB Provider (MSOLAP).

If you see "Class not registered" (0x80040154) error, install one of: 1. Power BI Desktop (recommended - includes MSOLAP): https://powerbi.microsoft.com/desktop 2. Microsoft OLE DB Driver for Analysis Services: https://learn.microsoft.com/analysis-services/client-libraries 3. SQL Server Analysis Services client tools

After installation, restart Excel and try again.

CRITICAL: Data Model Sync (Worksheet Tables)

Worksheet tables and Data Model tables are SEPARATE copies!

When you append/modify a worksheet table, the Data Model does NOT auto-update. You MUST explicitly refresh the Data Model to sync changes.

# WRONG: Data still shows old values
table(append, tableName="Sales", csvData="...")  # Worksheet updated
datamodel(evaluate, daxQuery="...")               # Returns OLD values!

# CORRECT: Refresh Data Model after worksheet changes
table(append, tableName="Sales", csvData="...")  # Worksheet updated
datamodel(refresh)                                 # Sync to Data Model
datamodel(evaluate, daxQuery="...")               # Returns NEW values!

When refresh is automatic:

• powerquery(refresh) refreshes BOTH Power Query AND Data Model
• Tables loaded via Power Query auto-sync on Power Query refresh

When refresh is REQUIRED:

• After table(append) to worksheet table
• After range(set-values) that modifies table data
• After any manual/direct worksheet edits

Excel Power Pivot Limitations (vs SSAS/Power BI)

Key Insight: Excel's COM API cannot create or modify calculated columns. If you need computed columns: 1. Preferred: Add the column in Power Query (computed at refresh time) 2. Alternative: Use a DAX measure instead (computed at query time)

How to add tables to the Data Model:

DAX Formatting:

DAX formulas are preserved exactly by default on WRITE operations (create-measure, update-measure), subject to Excel locale separator translation. Set formatDax=true only with explicit user consent; it sends DAX to daxformatter.com. Remote formatting adds ~100-500ms network latency per write operation. If formatting fails (network issues, API errors), the original DAX is saved unchanged - operations never fail due to formatting.

Action disambiguation:

• list-tables: List all tables currently in the Data Model
• list-measures: List all DAX measures (returns raw DAX from Excel)
• create-measure: Create a new DAX measure (DAX preserved by default; formatDax=true opts into remote formatting)
• update-measure: Modify existing measure's formula/format/description (DAX preserved by default; formatDax=true opts into remote formatting)
• delete-measure: Remove a measure
• delete-table: Remove table AND ALL its measures (DESTRUCTIVE!)
• read-info: Get Data Model metadata (culture, compatibility level)
• refresh: Refresh all Data Model data from sources
• evaluate: Execute DAX EVALUATE queries and return tabular results (read-only, no side effects)
• execute-dmv: Execute DMV queries for metadata discovery (SELECT FROM $SYSTEM.)

evaluate action:

Execute any DAX EVALUATE query against the Data Model and return results as JSON. Useful for ad-hoc analysis, testing DAX expressions, or extracting aggregated data.

// Examples of valid EVALUATE queries:
EVALUATE 'SalesTable'                    // Return entire table
EVALUATE TOPN(10, 'Sales', 'Sales'[Amount], DESC)  // Top 10 by amount
EVALUATE SUMMARIZE('Sales', 'Sales'[Region], "Total", SUM('Sales'[Amount]))  // Aggregation
EVALUATE FILTER('Products', 'Products'[Category] = "Electronics")  // Filtered
EVALUATE ROW("TotalRevenue", SUM('Sales'[Amount]))  // Single row result

execute-dmv action (DMV = Dynamic Management Views):

Execute SQL-like DMV queries to discover Data Model metadata. DMVs are schema rowsets that expose Analysis Services internal information.

SYNTAX: SELECT * FROM $SYSTEM.<SchemaRowset>

IMPORTANT LIMITATIONS (Excel's embedded Analysis Services):

• ONLY SELECT * works - specific column selection (SELECT col1, col2) fails
• Some TMSCHEMA views return empty results despite Data Model having data
• Excel's embedded AS has limited support compared to full SQL Server Analysis Services

Working DMV queries (verified in Excel):

DMV queries that execute but may return empty in Excel:

Full list of TMSCHEMA DMVs (from MS-SSAS-T protocol):

DISCOVER DMVs (server/analysis metadata):

Example use cases:

-- Find all measures and their DAX formulas
SELECT * FROM $SYSTEM.TMSCHEMA_MEASURES

-- Discover what objects a measure depends on
SELECT * FROM $SYSTEM.DISCOVER_CALC_DEPENDENCY

-- List all relationships
SELECT * FROM $SYSTEM.TMSCHEMA_RELATIONSHIPS

-- Get catalog information
SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS

Reference: Microsoft DMV Documentation

DAX measure creation:

• tableName: Which table the measure belongs to (for organization)
• measureName: Display name for the measure
• daxFormula: DAX expression (e.g., "SUM(Sales[Revenue])")
• formatString: Optional number format (#,##0.00, 0%, $#,##0, etc.)

Common DAX patterns:

// Sum
SUM(TableName[ColumnName])

// Average
AVERAGE(TableName[ColumnName])

// Count rows
COUNTROWS(TableName)

// Calculated ratio
DIVIDE(SUM(Sales[Revenue]), SUM(Sales[Units]), 0)

Displaying Data Model Data - Choose the Right Output

Rule: Prefer table create-from-dax for displaying query results. Use pivottable only when the user needs interactive analysis capabilities.

Charting Data Model Data - Use PivotChart Directly

WRONG: Create PivotTable → Create separate Chart from PivotTable data RIGHT: Use chart create-from-pivottable to create a PivotChart directly

A PivotChart is a single object connected to the Data Model. Creating a PivotTable + separate chart is unnecessary extra work and creates two objects to maintain.

Star Schema Architecture

Why use DAX over Power Query for calculations?

• DAX recalculates on refresh without re-running Power Query
• Useful when lookup/rate tables change frequently

Common mistakes:

• Creating measures before adding source table to Data Model → Error
• Using worksheet table names instead of Data Model table names
• Forgetting that delete-table removes ALL measures on that table
• Not specifying tableName when creating measures (required for organization)

Server-specific quirks:

• 2-minute auto-timeout on Data Model operations
• Table names in Data Model may differ from worksheet (check list-tables)
• Refresh refreshes ALL tables, not individual ones
• Measure names must be unique across entire Data Model (not per-table)

DMV Query Reference (Excel's Embedded Analysis Services)

When to Use DMV Queries

Use DMV queries (via the datamodel tool with execute-dmv action) when you need metadata that is NOT accessible through regular datamodel actions:

Do NOT use DMV queries for:

• Reading regular worksheet data → use range tool
• Listing Power Query queries → use powerquery list
• Reading PivotTable data → use pivottable tool

SYNTAX: SELECT * FROM $SYSTEM.<SchemaRowset>

LIMITATIONS:

• ONLY SELECT * works — specific column selection fails
• Some TMSCHEMA views return empty results in Excel's embedded AS

Working DMV Queries (verified)

May Return Empty in Excel

TMSCHEMA_TABLES, TMSCHEMA_COLUMNS, TMSCHEMA_PARTITIONS

Full TMSCHEMA Catalog

Reference: Microsoft DMV Docs

Agent Mode in Excel — Watch AI Work

Excel MCP's Agent Mode lets users watch AI operations happen in real-time. Instead of hidden automation, users see Excel respond to commands live — like Microsoft's Agent Mode in Excel, but with the full power of 225 COM-level operations.

MCP Server feature only. Agent Mode uses conversational UI to ask about visibility preferences.

When to Offer Agent Mode

Always ask the user at session start whether they want Excel visible or hidden. Present two clear choices using action cards:

Watch me work — Show Excel side-by-side so you see every change live. Operations run slightly slower because Excel renders each update on screen.

>

Work in background — Keep Excel hidden for maximum speed. You won't see changes until the task is done, but operations complete faster.

Skip asking only when the user has already stated a preference:

• User says "show me", "let me watch", "I want to see" → Show immediately
• User says "just do it", "work in background" → Keep hidden
• Simple one-shot operations (e.g., "what's in A1?") → Keep hidden, no need to ask

Three Workflows

1. Agent Mode — Interactive Side-by-Side

User watches AI build a spreadsheet in real-time, side-by-side with the AI assistant.

1. file(open, path='report.xlsx')
2. window(show)                                    → Make visible
3. window(arrange, preset='right-half')            → Excel on right, AI on left
4. window(set-status-bar, text='Creating headers...') → Live feedback
5. range(set-values, ...)                          → User sees data appear
6. window(set-status-bar, text='Building PivotTable...')
7. pivottable(create, ...)                         → User sees PivotTable form
8. window(set-status-bar, text='Adding chart...')
9. chart(create-from-range, ...)                   → User sees chart render
10. window(clear-status-bar)                       → Clean up status bar
11. ASK: "I've finished the report. Would you like me to save and close, or keep it open?"

Key behaviors:

• Status bar shows what operation is in progress
• Ask before closing — user may want to inspect or make manual changes
• Narrate findings alongside visual changes

2. Presentation Mode — Guided Walkthrough

AI navigates through a completed workbook, explaining findings while the user watches.

1. file(open, path='analysis.xlsx')
2. window(show)
3. window(set-state, windowState='maximized')      → Full screen for best visibility
4. window(set-status-bar, text='Reviewing Sales sheet...')
5. "On the Sales sheet, you can see quarterly revenue trending up 15%..."
6. worksheet(activate, name='Analysis')            → Switch to next sheet
7. window(set-status-bar, text='Reviewing Analysis sheet...')
8. "The Analysis sheet shows the PivotTable breakdown by region..."
9. screenshot(capture-sheet)                       → Capture for AI context
10. window(clear-status-bar)
11. "Here's what I found: [summary with insights]"

Key behaviors:

• Maximized for full visibility
• Navigate sheets while narrating
• Screenshots at key points for AI context
• End with comprehensive summary

3. Debug Mode — Step-by-Step Inspection

AI performs operations one at a time, showing results between each step for troubleshooting.

1. file(open, path='broken.xlsx')
2. window(show)
3. window(arrange, preset='right-half')
4. window(set-status-bar, text='Inspecting Power Query...')
5. powerquery(list)
6. "Found 3 queries. Let me check each one..."
7. powerquery(view, queryName='Sales')
8. window(set-status-bar, text='Refreshing Sales query...')
9. powerquery(refresh, queryName='Sales')
10. screenshot(capture-sheet)                      → Show result
11. "Sales query refreshed successfully. 150 rows loaded. Moving to next..."
12. window(set-status-bar, text='Refreshing Products query...')
13. powerquery(refresh, queryName='Products')
14. "Products query failed: [error]. Let me fix the M code..."
15. window(clear-status-bar)

Key behaviors:

• Pause between operations to show intermediate state
• Screenshot after each significant step
• Narrate what's happening and what was found
• Ideal for diagnosing query failures, formula errors, data issues

Status Bar Best Practices

Use window(set-status-bar) to show operation progress in Excel's status bar:

Always clear with window(clear-status-bar) when the workflow completes.

Only set when visible: Status bar text is only useful when Excel is visible. Skip status bar calls when Excel is hidden.

Asking About Visibility

When starting a session, present the visibility choice as action cards so the user can pick with one click:

Watch me work — Show Excel side-by-side so you see every change live. Operations run slightly slower because Excel renders each update on screen.

>

Work in background — Keep Excel hidden for maximum speed. You won't see changes until the task is done, but operations complete faster.

If the user picks "Watch me work": 1. window(show) → Make Excel visible 2. window(arrange, preset='right-half') → Position for side-by-side 3. Use window(set-status-bar) throughout the workflow for live progress

If the user picks "Work in background" or doesn't respond, keep Excel hidden and skip all status bar calls.

Gotchas & Known Limits

Real architectural and behavioral limits you'll encounter. Knowing these saves debugging time.

PivotTable Custom Formatting Doesn't Persist

User formatting (colors, bold, borders, etc.) on PivotTables is erased on refresh or when the workbook recalculates.

Why: PivotTables are generated by Excel's layout engine, which reapplies default formatting on every refresh.

Workaround: Use pivottable(action: 'set-style') to apply table-level styles. Styles are persisted across refreshes. Avoid range_format() on PivotTable cells.

Data Model Hidden Objects Invisible to COM

Columns, relationships, and measures marked "Hidden from client tools" in Power Pivot cannot be detected, listed, or accessed via Excel COM API. No workaround.

Why: Microsoft intentionally hides these objects from automation to prevent breaking dependent calculations.

What this means:

• You cannot enumerate ALL columns in a Data Model table (hidden ones don't appear)
• You cannot detect relationships that are marked hidden
• You cannot list all measures (hidden measures are invisible)
• If an expected object doesn't appear in datamodel list calls, check Power Pivot UI (right-click table → check "Hidden from client tools" status)

Mitigation: If you need to work with hidden objects, ask the user to unhide them in Power Pivot first.

Large Power Query Refreshes Timeout

Default timeout for powerquery(action: 'refresh') is 5 minutes. Power Query operations querying large datasets often exceed this.

Symptoms: Operation appears to succeed but data doesn't load, or timeout error after 5 minutes.

Fix: Increase timeout when opening the session:

{
  "action": "open",
  "filePath": "C:\\path\\to\\workbook.xlsx",
  "timeoutSeconds": 900
}

Set timeoutSeconds to match your expected query duration (900 = 15 min, 1800 = 30 min).

Session Concurrency Requires STA Thread

Multiple Excel sessions on non-STA (Single-Threaded Apartment) threads can deadlock during initialization. The COM engine requires all sessions to run on the same STA thread pool.

When this matters: Only if you're building your own multi-threaded host. MCP Server and CLI handle this automatically.

What you need to know: If implementing custom session management, ensure all Excel COM calls happen on a single dedicated STA thread.

Connection Strings Are Case-Sensitive

OLEDB and ODBC connection strings require exact case for parameter keys (Provider, Data Source, User ID, Password, etc.). Excel does NOT validate syntax when creating the connection — failures appear at refresh time, not create time.

Example that fails at refresh:

provider=SQLOLEDB;data source=server;Initial Catalog=db;

Should be:

Provider=SQLOLEDB;Data Source=server;Initial Catalog=db;

Fix: Always validate connection string syntax before creating connections. Test with connection(action: 'test-connection') after creation.

Formulas Return 0 Until Calculation Completes

When you write formulas with range(action: 'set-formulas'), they don't automatically calculate. If you read them back immediately with range(action: 'get-values'), you'll see 0 (or #VALUE!) instead of the calculated result.

Why: Excel calculates formulas in background or on-demand depending on calculation mode.

Fix: After writing formulas, call calculation_mode(action: 'calculate', scope: 'workbook') before reading values back:

1. range(action: 'set-formulas', ...)     → Formula written, not calculated
2. calculation_mode(action: 'calculate')  → Now recalculate
3. range(action: 'get-values', ...)       → Read calculated results

File Locking Across Sessions

If multiple Excel sessions have the same workbook open, operations on one session can lock the file and timeout on other sessions (30-second timeout).

Prevention: Only open each file in one session at a time. Use file(action: 'list') to check if a file is already open before opening it again.

Excel Visible/Hidden State is Session-Global

When you call window(action: 'show') or window(action: 'hide'), it affects the entire Excel application, not just your session. If multiple sessions are open, this will show/hide Excel for all of them.

What you need to know: If you have multiple sessions open and need one visible while others work silently, call window(action: 'show') only when needed and call window(action: 'hide') to clean up.

Power Query Doesn't Support External Data Refresh on Save

Power Query connections that reference external files or URLs have a "refresh on open" setting, but changes to external files won't be detected until you manually refresh or the user opens the file in Excel.

Workaround: Always call powerquery(action: 'refresh') explicitly after updating external data sources.

Special Characters in Range Addresses

Range addresses in M1 notation (e.g., A1:D10) work fine, but range names with spaces, hyphens, or special characters must be enclosed in single quotes or backticks:

rangeAddress: 'Sales Data'      ← Correct (with spaces)
rangeAddress: Sales Data        ← WRONG (fails)
rangeAddress: `Sales Data`      ← Also correct (backticks)

Same rule applies for sheet names: 'Sheet Name'!A1:D10.

DATE Conversion from Power Query

When Power Query returns dates, they come back as OLE date numbers (e.g., 45300 for 2024-01-01). Excel handles conversion automatically in cells, but if you're reading these values programmatically, parse them as:

actual_date = datetime.fromordinal(int(ole_date_value) + 1)  # Python example

This is rarely an issue in normal workflows but matters if you're doing calculations with the dates outside Excel.

M Code Syntax Reference

Column/Field Name Quoting (CRITICAL)

M code requires #"..." quoting for identifiers with hyphens, spaces, or special characters:

Common mistake: [Non-Recurring] parses as [Non] - [Recurring] (subtraction!) and fails with cryptic "The name 'X' wasn't recognized" errors.

Rule: If a column name contains anything other than letters, numbers, and underscores, use [#"Column Name"] syntax.

Reading Named Ranges (parameters)

Excel.CurrentWorkbook(){[Name = "Param_Name"]}[Content]{0}[Column1]

Query Chaining

Reference other queries by name directly: Source = OtherQueryName

````markdown

pivottable - Server Quirks

CRITICAL: Required Parameters

`pivotTableName` is REQUIRED for almost all PivotTable operations across pivottable, pivottable_calc, and pivottable_field tools. The only exception is list (which lists all PivotTables). Always specify the PivotTable name.

Calculated Fields vs DAX Measures

PivotTable calculated fields work well for simple single-table formulas. Use DAX measures for complex scenarios.

Calculated Field Workflow

pivottable_calc(CreateCalculatedField, fieldName="Revenue", formula="=Quantity*UnitPrice")
pivottable_field(AddValueField, fieldName="Revenue", aggregationFunction="Sum")

DAX Measure Workflow (for complex scenarios)

table(add-to-data-model, tableName="Sales")
datamodel(create-measure, measureName="Revenue", daxFormula="SUMX(Sales, Sales[Quantity]*Sales[UnitPrice])")
pivottable(create-from-datamodel, ...)  # Measure automatically available

When to Use DAX Instead of Calculated Fields

• Multi-table calculations (need relationships between tables)
• Complex logic (time intelligence, YTD, running totals)
• Calculations involving filtered contexts
• Reusable measures across multiple PivotTables

PivotTable Source Types

Rule: If you need calculated revenue/aggregations, use Data Model as source.

Refresh Behavior (CRITICAL)

PivotTables do NOT auto-refresh when source data changes!

After adding rows to source table:

table(append, ...)           # Add rows to worksheet table
pivottable(refresh, ...)     # Refresh PivotTable to see new rows
datamodel(refresh)           # ALSO refresh Data Model if using DAX measures

After Power Query refresh:

powerquery(refresh, ...)     # Refreshes Power Query AND Data Model
# PivotTables connected to Data Model auto-refresh

Field Configuration

Row/Column/Value Fields

When creating PivotTables, configure fields in order: 1. Add Row fields: pivottable_field(AddRowField, fieldName="Region") 2. Add Column fields: pivottable_field(AddColumnField, fieldName="Year") 3. Add Value fields: pivottable_field(AddValueField, fieldName="Amount", aggregationFunction="Sum") 4. Add filters: pivottable_field(AddFilterField, fieldName="Status") 5. Refresh to update display: pivottable(refresh, pivotTableName="...")

IMPORTANT: Field operations are structural only - they modify the PivotTable layout but don't trigger visual refresh. Call pivottable(refresh) after configuring all fields to update the display. This is especially important for OLAP/Data Model PivotTables.

Aggregation Functions for Value Fields

Common Patterns

Revenue Analysis from Worksheet Table

# Option 1: Add revenue column to source table FIRST
range(set-formula, sheetName="Sales", rangeAddress="I2", formula="=[@Quantity]*[@UnitPrice]")
pivottable(create-from-table, sourceTableName="SalesTable", ...)
pivottable_field(AddValueField, fieldName="Revenue", aggregationFunction="Sum")  # Works!

# Option 2: Use Data Model (RECOMMENDED)
table(add-to-data-model, tableName="SalesTable")
datamodel(create-measure, measureName="Revenue", daxFormula="SUMX(SalesTable, SalesTable[Quantity]*SalesTable[UnitPrice])")
pivottable(create-from-datamodel, ...)  # Measure automatically available

Multi-Table Analysis

Always use Data Model for multi-table analysis:

table(add-to-data-model, tableName="Sales")
table(add-to-data-model, tableName="Products")
datamodel_relationship(create-relationship, fromTable="Sales", fromColumn="ProductID", toTable="Products", toColumn="ProductID")
datamodel(create-measure, tableName="Sales", measureName="Revenue", daxFormula="SUMX(Sales, RELATED(Products[Price])*Sales[Quantity])")
pivottable(create-from-datamodel)

Layout Styles

The layoutStyle parameter controls PivotTable appearance:

Common Errors and Solutions

````

powerquery - Server Quirks

RECOMMENDED DEVELOPMENT WORKFLOW (ALWAYS USE THIS)

Test BEFORE persisting - avoid polluting workbooks with broken queries:

Step 1: evaluate → Test M code, verify results (catches syntax errors, missing sources)
Step 2: create/update → Store VALIDATED query in workbook
Step 3: refresh/load-to → Load data to destination (worksheet/data-model)

Why this workflow:

• evaluate executes M code WITHOUT creating permanent query (test-then-commit)
• Returns actual data preview with columns and rows in JSON
• Better error messages than COM exceptions from create/update
• No cleanup needed - temporary objects auto-deleted
• Skip evaluate only for trivial literal tables (#table with hardcoded values)

IF CREATE/UPDATE FAILS: Use evaluate to get detailed Power Query error message, fix code, retry.

Additional evaluate use cases:

• Execute one-off queries without creating permanent queries
• Ad-hoc data exploration or debugging M code transformations
• Quick testing during development (like REPL for M code)

---

M-Code Formatting:

• Create and Update preserve M code exactly by default and do not call remote services
• Set formatMCode=true only with explicit user consent; it sends M code to powerqueryformatter.com
• Remote formatting adds ~100-500ms network latency per call
• Graceful fallback: saves original M code if the formatting service is unavailable
• Read operations (List, View) return M code as stored (no formatting on read)

Data Model workflow:

Power Query can load data to different destinations:

• worksheet (default): Creates an Excel Table on a worksheet
• data-model: Loads directly to Power Pivot for DAX analysis
• both: Loads to worksheet AND Power Pivot
• connection-only: Imports query definition without loading data

To create DAX measures on Power Query data: 1. Use powerquery create/load-to with loadDestination='data-model' 2. Then use datamodel to create DAX measures

Alternative path (for existing worksheet tables): 1. Use table with add-to-data-model action 2. Then use datamodel to create DAX measures

Action disambiguation:

• evaluate: CRITICAL - USE THIS FIRST - Execute M code directly, return results WITHOUT creating a permanent query (test before create/update!)
• create: Import NEW query using inline mCode (FAILS if query already exists - use update instead)
• update: Update EXISTING query M code + refresh data (use this if query exists)
• rename: Change query name (requires both queryName and newName parameters)
• load-to: Loads to worksheet or data model or both (not just config change) - CHECKS for sheet conflicts
• unload: Removes data from ALL destinations (worksheet AND Data Model) - keeps query definition
• delete: Completely removes query AND all associated data (worksheet, Data Model connections)

Rename behavior:

• Names are trimmed and compared case-insensitively for uniqueness
• Renaming "Query1" to "query1" is allowed (case-only change, no conflict)
• Renaming "Query1" to " Query1 " is a no-op (trimmed names match)
• No-op (same normalized name) → success with oldName = newName
• Conflict with existing query → error with errorMessage
• M code content is unchanged - only the name changes
• No auto-save: workbook must be saved separately to persist the rename

When to use create vs update:

• Query doesn't exist? → Use create
• Query already exists? → Use update (create will error "already exists")
• Not sure? → Check with list action first, then use update if exists or create if new
• ALWAYS evaluate M code FIRST to catch errors before persisting

List action and IsConnectionOnly:

• IsConnectionOnly=true means query has NO data destination (not in worksheet, not in Data Model)
• IsConnectionOnly=false means query loads data SOMEWHERE (worksheet OR Data Model OR both)
• A query loaded ONLY to Data Model is NOT connection-only

Inline M code:

• Provide raw M code directly via mCode
• Keep .pq files only for GIT workflows

Create/LoadTo with existing sheets:

• Use targetCellAddress to place the table on an existing worksheet without deleting other content
• Applies to BOTH create and load-to
• If the worksheet already has data and you omit targetCellAddress, the tool returns guidance telling you to provide one
• Existing tables are refreshed in-place; specifying a different targetCellAddress requires unload + reload
• Worksheets that exist but are empty behave like new sheets (default destination = A1)

Common mistakes:

• WARNING: Skipping evaluate → Create/update with untested M code (ERROR: pollutes workbook with broken queries)
• Using create on existing query → ERROR "Query 'X' already exists" (should use update)
• Using update on new query → ERROR "Query 'X' not found" (should use create)
• Calling LoadTo without checking if sheet exists (will error if sheet exists)
• Assuming unload only removes worksheet data → Also removes Data Model connections
• Calling rename without trimming newName → Server trims automatically, " Query " becomes "Query"
• Renaming to conflicting name → Check list first if unsure about existing names

Server-specific quirks:

• Validation = execution: M code only validated when data loads/refreshes
• connection-only queries: NOT validated until first execution
• refresh with loadDestination: Applies load config + refreshes (2-in-1)
• Single cell returns [[value]] not scalar
• refresh defaults to 30-minute timeout if refreshTimeoutSeconds is 0 or omitted. Any positive value is accepted. For quick queries use a smaller value (e.g., 60-120 seconds).
• load-to uses the same 30-minute timeout as refresh. If Excel is blocked by privacy dialogs/credentials, you'll get SuggestedNextActions instead of a hang—surface them to the user before retrying.

Data Model connection cleanup:

• Unload removes BOTH worksheet ListObjects AND Data Model connections
• Delete removes query, worksheet ListObjects, AND Data Model connections
• Connection naming pattern: "Query - {queryName}" or "Query - {queryName} - suffix"

M Code - Server-Specific Notes

For full M code language syntax, see m-code-syntax reference.

Column/Field Name Quoting (CRITICAL)

M code requires special syntax for identifiers containing hyphens, spaces, or special characters:

Common mistake: [Non-Recurring] parses as [Non] - [Recurring] (subtraction!) and fails with cryptic "The name 'X' wasn't recognized" errors.

Rule: If a column name contains anything other than letters, numbers, and underscores, use [#"Column Name"] syntax.

Reading Named Ranges (parameters)

Excel.CurrentWorkbook(){[Name = "Param_Name"]}[Content]{0}[Column1]

Query Chaining

Reference other queries by name directly: Source = OtherQueryName

Source Control Pattern

1. Store M code in .pq files 2. powerquery create or update with inline mCode 3. refresh to validate 4. File name MUST match query name

Query naming: File name MUST match Excel query name exactly.

range - Number Formats and Cell Formatting

IMPORTANT: Always use US format codes. The server automatically translates to the user's locale.

Discoverability note: number display formats live on range; visual styling and auto-fit live on range_format.

Formatting Split Across Two Tools

If you are looking for percentage, currency, date, or text display formatting, use range, not range_format. If you are looking for auto-fit, width, height, borders, fill, or font styling, use range_format. If you need the same styling on multiple non-contiguous ranges, use format-ranges instead of repeating format-range.

Quick Pattern: Write, Format, Auto-Fit

range(action: 'set-values', rangeAddress: 'A1:D4', values: [[...], [...]])
range(action: 'set-number-format', rangeAddress: 'C2:D4', formatCode: '$#,##0.00')
range_format(action: 'auto-fit-columns', rangeAddress: 'A:D')

Quick Pattern: Repeated Section Headers

Use format-ranges when the same header or section style repeats across disjoint ranges on one sheet:

range_format(action: 'format-ranges',
    rangeAddresses: ['A1:G1', 'A12:G12', 'A24:G24'],
    bold: true,
    fillColor: '#243F60',
    fontColor: '#FFFFFF',
    horizontalAlignment: 'center')

All target ranges are validated before formatting begins. If any target range is invalid, nothing is formatted.

Quick Pattern: Header Row With Fill Colour

set-style('Heading 1') does not apply a fill — use format-range for coloured headers. Pass ALL properties in one call:

range_format(action: 'format-range', rangeAddress: 'A1:D1',
    bold: true,
    fillColor: '#4472C4',
    fontColor: '#FFFFFF',
    horizontalAlignment: 'center')

Quick Pattern: Semantic Status Cells

Use set-style when the meaning (Good/Bad/Neutral) matters and theme-awareness is useful:

range_format(action: 'set-style', rangeAddress: 'B2:B10', styleName: 'Good')
range_format(action: 'set-style', rangeAddress: 'C2:C10', styleName: 'Bad')

format-range Properties

set-style Presets

Built-in style names: Normal, Heading 1, Heading 2, Heading 3, Heading 4, Title, Good, Bad, Neutral, Currency, Percent, Comma

range_format(action: 'set-style', rangeAddress: 'A1:D1', styleName: 'Heading 1')

Format Codes

All format codes are auto-translated to the user's locale. Use US codes (d/m/y for dates, . for decimal, , for thousands).

Actions

SetNumberFormat: Apply one format to entire range.

• formatCode: Format code from table above

SetNumberFormats: Apply different formats per cell.

• formats: 2D array matching range dimensions
• Example: [["$#,##0.00", "0.00%"], ["mm/dd/yyyy", "General"]]

Related range_format Actions

• auto-fit-columns: Fit column widths to content after writing data
• auto-fit-rows: Fit row heights to wrapped or multi-line content
• format-range: Apply fills, fonts, borders, and alignment
• format-ranges: Apply one shared formatting payload to multiple ranges on the same worksheet
• set-style: Apply named Excel styles such as Good, Bad, or Heading 1

Screenshot & Visual Verification Reference

REQUIRED: Screenshot After Chart Creation

You MUST call `screenshot` after creating any chart when visual output is requested or implied. Do not close the file or end your response without capturing a screenshot.

1. chart(create-from-range, ...)  → Chart created
2. screenshot(capture, rangeAddress='A1:M20')  ← REQUIRED — never skip this step
3. file(close, save=true)

This rule applies even if:

• The chart was created on the first try
• No errors occurred
• The task description doesn't explicitly say "take a screenshot"

Tools

• `screenshot`: Capture worksheet content as PNG images

Actions

Quality Parameter

Default is Medium — use this for most cases. Only use High when fine text or formulas need careful inspection.

When to Use Screenshots

After Chart Creation or Positioning

1. chart(create-from-range, ..., targetRange='F2:K15')
2. screenshot(capture, rangeAddress='A1:O25')  → Verify chart doesn't overlap data

After Complex Formatting

1. range(set-number-format, ...)
2. conditionalformat(add-rule, ...)
3. screenshot(capture-sheet)  → Verify formatting looks correct

After PivotTable Layout Changes

1. pivottable(add-row-field, ...)
2. pivottable(add-value-field, ...)
3. screenshot(capture-sheet)  → Verify layout and field arrangement

Best Practices

1. Verify chart placement: After creating or repositioning charts, capture a screenshot to confirm no overlap with data or other charts 2. Capture relevant area: Use capture with a specific range rather than capture-sheet when you only need part of the worksheet 3. Use after multi-step operations: Screenshots are most valuable after a sequence of formatting, layout, or chart operations 4. MCP returns image directly: The image is returned as native ImageContent — no file handling needed 5. CLI with `--output`: Use --output screenshot.png to save the captured image directly as a PNG file 6. Apply formatting once: Apply each formatting operation (bold, fill color, number format) to a given range only once. Do not reapply unless a subsequent step explicitly changes or clears it — redundant calls waste turns and cost.

Common Patterns

Chart Overlap Verification

1. range(get-used-range) → "A1:D20"
2. chart(create-from-range, sourceRange='A1:D20', targetRange='F2:K15')
3. screenshot(capture, rangeAddress='A1:K20')
   → Visually confirm chart is positioned next to data, not on top of it

Multi-Chart Dashboard Layout

When creating dashboards with multiple charts:

1. get-used-range → Know where data ends
2. Create Chart 1 with targetRange below/beside data
3. Create Chart 2 with targetRange that does NOT overlap Chart 1
4. Create Chart 3, Chart 4, etc. — each in a non-overlapping targetRange
5. screenshot(capture-sheet) → Verify NO charts overlap each other or data

Key rules for multi-chart layouts:
- Use targetRange for every chart — never rely on default positioning
- Leave at least 1-2 rows/columns between charts
- Place charts in a grid pattern (e.g., 2x2) below the data area
- If overlap detected, use chart(fit-to-range) to reposition

Dashboard Layout Check

1. Create multiple charts and tables
2. screenshot(capture-sheet)
   → Verify overall dashboard layout, spacing, and alignment
3. If issues found: reposition with chart(fit-to-range), then screenshot again

````markdown

slicer - Server Quirks

Slicer Types:

Two distinct slicer types exist:

• PivotTable Slicers: Filter PivotTables (can control multiple PivotTables)
• Table Slicers: Filter Excel Tables (single table only)

Actions:

CRITICAL: Required Parameters - The "Required Parameters" column above is strict. Missing any required parameter will cause an error. Pay special attention to pivotTableName for PivotTable slicers and slicerName for selection/deletion operations.

Naming Convention:

• If slicerName not provided, auto-generates {FieldName}Slicer or {ColumnName}Slicer
• Slicer names must be unique within workbook
• Use list-slicers or list-table-slicers to check existing names

Selection Behavior:

• selectedItems is a list of strings: ["Value1", "Value2"]
• Empty list [] clears all filters (shows all items)
• Values must match exactly (case-sensitive)
• Invalid values are silently ignored

CLI: JSON Array Quoting (important for --selected-items):

The --selected-items parameter requires a JSON array. Use proper shell escaping:

# PowerShell: use single quotes around the JSON, double quotes inside
--selected-items '["West","East"]'

# Or escape inner quotes with backtick
--selected-items "[`"West`",`"East`"]"

# Clear filter (show all items)
--selected-items '[]'

Positioning:

• destinationSheet specifies which worksheet hosts the slicer
• position is a cell address for top-left corner (e.g., 'E1', 'G5')
• The slicer's top-left corner aligns to the specified cell
• Default position if not specified: Excel chooses

Common Mistakes:

• Creating slicer for field not in PivotTable → Error
• Creating table slicer for column not in table → Error
• Setting selection with wrong case → Values ignored (filter shows nothing)
• Deleting slicer that doesn't exist → Error

Best Practices:

1. Call list-slicers before creating to avoid name conflicts 2. Use list-slicers to get exact slicer names for selection/deletion 3. Multi-PivotTable filtering: Create one slicer, connect to multiple PivotTables in Excel UI

CLI Usage:

# Create PivotTable slicer
excelcli slicer create-slicer --session <id> --pivot-table-name "SalesPivot" --field-name "Region" --destination-sheet "Dashboard"

# Set slicer filter
excelcli slicer set-slicer-selection --session <id> --slicer-name "RegionSlicer" --selected-items "[`"West`",`"East`"]"

# Clear slicer filter (show all)
excelcli slicer set-slicer-selection --session <id> --slicer-name "RegionSlicer" --selected-items "[]"

# Create Table slicer
excelcli slicer create-table-slicer --session <id> --table-name "SalesTable" --column-name "Category"

# List all slicers
excelcli slicer list-slicers --session <id>
excelcli slicer list-table-slicers --session <id>

````

table - Server Quirks

Data Model workflow (CRITICAL):

Excel Tables on worksheets are NOT automatically in the Data Model (Power Pivot). To analyze worksheet data with DAX measures:

1. Ensure data is formatted as an Excel Table (use create action if needed) 2. Use add-to-data-model action to add the table to Power Pivot 3. Then use datamodel to create DAX measures on it

Action disambiguation:

• create: Create NEW table from a range (requires sheetName, tableName, rangeAddress). Pass tableStyle here to style at creation time.
• read: Get table metadata (range, columns, style, row counts)
• get-data: Get actual table DATA as 2D array (use visibleOnly=true for filtered data)
• rename: Rename an existing table
• delete: Remove table (keeps data, removes table formatting)
• resize: Change table range (expand/contract)
• set-style: Change table visual style (TableStyleLight1-21, TableStyleMedium1-28, TableStyleDark1-11). Default is TableStyleMedium2.
• toggle-totals: Show or hide the totals row (showTotals: true/false)
• set-column-total: Set the aggregate function on a totals-row column (Sum, Count, Average, Min, Max, None)
• add-to-data-model: Add an existing worksheet table to Power Pivot for DAX analysis
• append: Add rows to existing table (requires rows or rowsFile parameter)
• create-from-dax: Create table populated by a DAX EVALUATE query from Data Model
• update-dax: Update an existing DAX-backed table's query
• get-dax: Get the DAX query behind a DAX-backed table

Table styling — always use table styles, not range_format:

Excel Tables manage their own header/row/totals formatting through table styles. Never use range_format(action: 'format-range') on table header rows — it conflicts with the table style and produces inconsistent formatting.

Common table style choices:

• TableStyleMedium2 — standard blue, most widely used
• TableStyleMedium9 — orange accent
• TableStyleLight1 — minimal borders, no header fill
• TableStyleDark1 — dark header with white text

DAX-backed tables (NEW):

Create worksheet tables populated by DAX EVALUATE queries against the Data Model. Perfect for creating summary/report tables with aggregated data.

Workflow:
1. Have data in Data Model (via table add-to-data-model or powerquery)
2. Use create-from-dax with a DAX EVALUATE query
3. Table is created on worksheet with query results
4. Use update-dax to change the query, get-dax to inspect it

Example DAX queries for create-from-dax:

• EVALUATE SUMMARIZE('Sales', 'Sales'[Region], "Total", SUM('Sales'[Amount]))
• EVALUATE TOPN(10, 'Products', 'Products'[Revenue], DESC)
• EVALUATE FILTER('Customers', 'Customers'[Country] = "USA")

add-to-data-model behavior:

• Only works on Excel Tables (ListObjects), not plain ranges
• Table appears in Power Pivot with same name
• After adding, use datamodel to create DAX measures
• Idempotent: calling on already-added table is a no-op

When to use which tool:

Common mistakes:

• Trying to create DAX measures without first adding table to Data Model
• Using datamodel to add tables (it only manages existing Data Model tables)
• Confusing get-data (returns cell values) with read (returns metadata)
• Forgetting hasHeaders parameter when creating tables from headerless data

Server-specific quirks:

• Style parameter is overloaded: table style name OR total function (context-dependent)
• csvData parameter: dedicated parameter for append action (CSV format: comma-separated, newline-separated rows)
• visibleOnly parameter only applies to get-data action
• Table names must be unique within workbook (Excel requirement)

Window Management Reference

Tools

• `window`: Control Excel window visibility, position, and state

Actions

When to Use Window Management

Interactive "Agent Mode" — User Watches AI Work in Excel

1. window(show)                              → Excel becomes visible
2. window(arrange, preset='right-half')      → Position Excel on right side of screen
3. ... perform Excel operations ...          → User watches changes live
4. window(hide)                              → Hide when done (optional)

Side-by-Side Layout

1. window(show)
2. window(arrange, preset='left-half')       → Excel takes left half of screen
   → User's AI assistant occupies the right half

Check Current State

1. window(get-info) → Returns visibility, position, size, window state, foreground status

Arrange Presets

Best Practices

1. Show before operating visually: If the user wants to watch operations, call show + arrange before starting the workflow 2. Visibility syncs with session: Show/hide updates session metadata — file(list) reflects the current visibility state 3. Arrange makes visible: arrange automatically shows Excel if it's hidden 4. set-state makes visible: Setting state to normal/maximized automatically shows Excel 5. set-position ensures normal state: Setting position switches from maximized/minimized to normal automatically 6. Use get-info to check state: Before positioning, check if Excel is already visible and where it is

Common Patterns

Demo Mode — Show User the Work

1. file(open, path='report.xlsx')
2. window(show)
3. window(arrange, preset='left-half')
4. ... create tables, charts, formatting ...
5. file(close, save=true)
   → Excel hidden automatically on close

Quick Peek — Show Result Then Hide

1. ... perform operations while hidden ...
2. window(show)                    → Show the result
3. screenshot(capture-sheet)       → Also capture for chat
4. window(hide)                    → Hide again

Status Bar Feedback — Live Progress

1. window(show)
2. window(arrange, preset='right-half')
3. window(set-status-bar, text='Writing 500 rows...') → User sees progress
4. range(set-values, ...)
5. window(set-status-bar, text='Building chart...')
6. chart(create-from-range, ...)
7. window(clear-status-bar)                           → Clean up when done

Excel MCP Server - Key Constraints

These are the critical constraints and workarounds specific to Excel automation via COM.

Excel Power Pivot Limitations

Excel's Power Pivot has key limitations compared to Power BI/SSAS:

Implication: Design your architecture to put computed columns in Power Query, not DAX.

Architecture: Power Query vs DAX

Why separate? DAX measures recalculate on refresh without re-running Power Query. Useful when lookup/rate tables change often.

Tool Sequencing

Data Model Prerequisites

1. Load table (powerquery refresh loadDestination="data-model")
2. THEN create relationships (datamodel_relationship with create-relationship action)
3. THEN create measures (datamodel create-measure)

Skipping step 1 causes "table not found" errors.

Power Query Development Lifecycle

1. powerquery evaluate (test M code without persisting - catches errors early)
2. powerquery create/update (store validated query in workbook)
3. powerquery refresh/load-to (load data to destination)

Skipping step 1 causes broken queries in workbook and cryptic COM errors.

Parameter Setup for Power Query

1. worksheet create (e.g., "_Setup")
2. range set-values (parameter values)
3. namedrange create (named reference)

Power Query reads via Excel.CurrentWorkbook(){[Name = "..."]}

Verification Commands

After Power Query: powerquery list, powerquery view
After refresh:     datamodel list-tables
After measure:     datamodel list-measures, datamodel evaluate
After relationship: datamodel_relationship list-relationships
After chart/layout: screenshot capture-sheet (visual verification)

worksheet - Worksheet Operations

Same-File Session Operations

Use session-based actions for worksheet lifecycle within the same workbook:

Rename example:

action: rename
old_name: Sheet1
new_name: Summary

Rename requires old_name + new_name.

Atomic Cross-File Operations

copy-to-file and move-to-file are the simplest way to transfer sheets between files.

Benefits:

• No session management required
• Files are opened, modified, saved, and closed automatically
• Single atomic operation - no cleanup needed

Example - Copy sheet to another file:

action: copy-to-file
source_file: C:\Reports\Q1.xlsx
source_sheet: Summary
target_file: C:\Reports\Annual.xlsx
target_sheet_name: Q1 Summary  # Optional: rename during copy

Example - Move sheet to another file:

action: move-to-file
source_file: C:\Drafts\Data.xlsx
source_sheet: FinalData
target_file: C:\Published\Report.xlsx
before_sheet: Sheet1  # Optional: position in target

Positioning Parameters

Use before_sheet OR after_sheet (not both) to control where the sheet appears in the target file:

• before_sheet: "Sheet1" - Insert before Sheet1
• after_sheet: "Sheet1" - Insert after Sheet1
• Neither specified - Append to end

When to Use Session-Based Operations

For same-file operations (copy within same workbook, rename, delete, tab colors), use session-based actions with session_id.

Rename Parameters

For rename, use old_name and new_name.

• MCP rename requires old_name + new_name
• CLI uses --old-name + --new-name
• Copy and cross-file parameters such as sheet_name, source_name, source_sheet, target_name, and target_sheet_name are not rename aliases

Common Errors