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:
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:
Should be:
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
Python in Excel Requires Microsoft 365 Entitlement¶
pythoninexcel runs code in Microsoft's cloud sandbox. It requires a licensed Microsoft 365 account with Python in Excel enabled and internet access; perpetual-license Excel and offline sessions cannot run =PY() formulas.
When Excel evaluates a PY() formula as #NAME?, both set-formula and get-result return a clear unavailable-feature error. Do not retry that condition as if calculation were pending. #BUSY!, #CONNECT!, and #BLOCKED! are transient cloud states and retain retry semantics.
Python cloud startup can take several minutes. Prefer an asynchronous workflow: set the formula, continue other workbook work, then call get-result later. For the CLI, --max-wait-seconds must be at least 1 and shorter than the session's operation timeout; do not use a polling wait equal to or longer than the session timeout.
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:
This is rarely an issue in normal workflows but matters if you're doing calculations with the dates outside Excel.