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