# 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:**

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

**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):**

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

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

| Bad (Silent completion) | Good (Text summary) |
|------------------------|--------------------|
| *(tool call with no text)* | "Created PivotTable 'SalesPivot' with tabular layout on the Analysis sheet." |
| *(just runs a command)* | "Set the PivotTable to compact layout (row fields in a single indented column)." |

**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:

```markdown
| 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:

| Scenario | Use | NOT |
|----------|-----|-----|
| Show DAX query results | `table create-from-dax` | PivotTable |
| Static report/snapshot | `table create-from-dax` | PivotTable |
| Data needed in formulas | `table create-from-dax` | PivotTable |
| User needs interactive filtering | `pivottable` | DAX table |
| Cross-tabulation layout | `pivottable` | DAX table |

**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:

| Destination | When to Use |
|-------------|-------------|
| `worksheet` | View data, simple analysis |
| `data-model` | DAX measures, PivotTables, relationships |
| `both` | View data AND use in DAX |
| `connection-only` | Data staging, intermediate queries |

### 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:

```json
{
  "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."
