# 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')
```

| Object | Correct style approach | Do NOT use |
|--------|----------------------|------------|
| Excel Tables | `table(action:'set-style')` or `tableStyle` on create | `range_format` on header/data rows |
| PivotTables | Not supported — leave default | `range_format` (wiped on refresh) |
| Charts | `chart_config(action:'set-style', styleNumber: 1-48)` | `range_format` |
| Plain cells/ranges | `range_format` | — |

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

| Workflow Goal | Load Destination |
|---------------|------------------|
| View data in cells | `worksheet` |
| Use in DAX/PivotTables | `data-model` |
| Both viewing and DAX | `both` |
| Intermediate staging | `connection-only` |

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