๐ File Operations (6 operations)
- List Sessions: View all active Excel sessions
- Open: Open workbook and create session (returns session ID for all subsequent operations). IRM/AIP-protected files are automatically detected and opened read-only with Excel visible for credential authentication โ no extra parameters needed.
- Close: Close session with optional save
- Close Workbook: Close workbook without closing Excel
- Create Empty: Create new .xlsx or .xlsm workbook
- Test: Verify workbook can be opened and is accessible. Returns
isIrmProtected flag for IRM/AIP-protected files.
๐งฎ Calculation Mode (3 operations)
- Get Mode: Query current calculation mode and calculation state
- Set Mode: Switch between automatic, manual, and semi-automatic modes
- Calculate: Explicitly recalculate workbook, sheet, or range
๐ Power Query & M Code (12 operations)
Atomic Operations - Single-call workflows:
- List: List all Power Query queries in workbook
- View: View the M code of a Power Query
- Create: Import + load in one operation (atomic workflow) with automatic formatting
- Update: Update M code with automatic formatting and auto-refresh
- Rename: Rename a Power Query (trim + case-insensitive uniqueness check)
- Refresh: Refresh a Power Query with timeout detection
- Refresh All: Batch refresh all queries in workbook
- Load To: Configure load destination and refresh (atomic)
- Get Load Config: Get current load configuration
- Unload: Remove data from all destinations (keeps query definition)
- Delete: Remove Power Query from workbook
- Evaluate: Execute M code directly and return results (without creating a permanent query)
Automatic M-Code Formatting: M code is automatically formatted on write operations (Create, Update) using the powerqueryformatter.com API (by mogularGmbH, MIT License). Read operations return M code as stored in Excel. Formatting adds ~100-500ms network latency but dramatically improves readability with proper indentation, spacing, and line breaks. Graceful fallback returns original M code if formatting fails.
๐ Data Model & DAX (Power Pivot) (19 operations)
- List Tables: Discover all tables in the Data Model
- Read Table: Get specific table information
- Rename Table: Rename a Data Model table (best-effort via Power Query; returns clear error if not supported)
- List Columns: List columns for a table
- List Measures: List all DAX measures with formula previews
- Read Info: Get comprehensive model information
- Create Measure: Create new DAX measure with automatic formatting (format types: Currency, Percentage, Decimal, General)
- Update Measure: Modify existing measure with automatic formatting
- Delete Measure: Remove measure from model
- Delete Table: Remove table from Data Model
- List Relationships: View all table relationships
- Read Relationship: Get specific relationship info
- Create Relationship: Create relationship between tables
- Update Relationship: Modify relationship (toggle active/inactive)
- Delete Relationship: Remove relationship
- Refresh: Refresh entire Data Model
- List Workbook Connections: List Power Query sources available for integration
- Evaluate: Execute DAX EVALUATE queries and return tabular results (for ad-hoc analysis)
- Execute DMV: Execute SQL-like DMV (Dynamic Management View) queries for metadata discovery
Automatic DAX Formatting: DAX formulas are automatically formatted on write operations (CreateMeasure, UpdateMeasure) using the official Dax.Formatter library (SQLBI). Read operations return raw DAX as stored in Excel. Formatting adds ~100-500ms network latency but dramatically improves readability. Graceful fallback returns original DAX if formatting fails.
Note: DAX calculated columns not supported - use Excel UI for calculated columns
๐จ Excel Tables (ListObjects) (27 operations)
Lifecycle:
- List, read, create, rename, resize, delete tables
Styling & Formatting:
- Apply table styles
- Toggle totals row
- Set column totals
Data Operations:
- Append rows
- Get table data (with optional visible-only filtering)
- Add to Data Model
DAX-Backed Tables:
- Create from DAX (create Excel Table populated by DAX EVALUATE query)
- Update DAX (change the DAX query of an existing DAX-backed table)
- Get DAX (retrieve DAX query info from a table)
Filter Operations:
- Apply filter (criteria)
- Apply filter (values)
- Clear filters
- Get filter state
Column Management:
- Add, remove, rename columns
Structured References:
- Get structured reference (formula syntax for table columns/ranges)
Sorting:
- Single-column sort
- Multi-column sort (up to 3 levels)
Number Formatting:
- Get column number formats
- Set column number formats
๐ PivotTables (30 operations)
Creation:
- Create from range
- Create from Excel Table
- Create from Data Model
Field Management:
- List all fields (row, column, value, filter areas)
- Add row field, column field, value field, filter field
- Remove field
Field Configuration:
- Set field aggregation function (Sum, Average, Count, Min, Max, etc.)
- Set custom field name
- Set field number format
- Set field filter criteria
- Sort field (ascending/descending)
Calculated Fields (Regular PivotTables):
- List calculated fields
- Create calculated field
- Delete calculated field
Calculated Members (OLAP/Data Model PivotTables):
- List calculated members
- Create calculated member
- Delete calculated member
Layout & Formatting:
- Set layout (table or outline)
- Set subtotals display
- Set grand totals display
Data Operations:
- Get PivotTable data as 2D array
- Refresh PivotTable
Lifecycle:
- List PivotTables
- Read PivotTable info
- Delete PivotTable
๐ Charts (28 operations)
Creation:
- Create from range
- Create from PivotTable
Series Management:
- Add series
- Remove series
- Update series data
Configuration:
- Set data source range
- Set chart type
- Show/hide legend
- Set style
Formatting:
- Set chart title
- Set axis title
- Set axis number format
- Get axis number format
Data Labels:
- Configure data labels (show values, percentages, category names, etc.)
- Set label position (Center, InsideEnd, OutsideEnd, etc.)
- Apply to all series or specific series
Axis Scale:
- Get axis scale settings
- Set minimum/maximum scale
- Set major/minor units
Gridlines:
- Get gridlines configuration
- Set major/minor gridlines visibility
Series Formatting:
- Set marker style (Circle, Square, Diamond, Triangle, etc.)
- Set marker size
- Set marker colors
Trendlines:
- Add trendline (Linear, Exponential, Logarithmic, Polynomial, Power, MovingAverage)
- List trendlines on series
- Delete trendline
- Configure trendline (forecast forward/backward, display equation, display Rยฒ)
Placement & Positioning:
- Set chart placement (move/size with cells options)
- Fit to range (position and size to match a range)
Lifecycle:
- List charts
- Read chart info
- Move chart (to different worksheet or new sheet)
- Delete chart
๐ Ranges (42 operations)
Data Operations:
- Get values
- Set values
- Get formulas
- Set formulas
- Clear all
- Clear contents
- Clear formats
- Copy
- Copy values
- Copy formulas
- Insert cells
- Delete cells
- Insert rows
- Delete rows
- Insert columns
- Delete columns
- Find
- Replace
- Sort
Discovery & Utilities:
- Get used range
- Get current region
- Get range info (address, dimensions)
Hyperlinks:
- Add hyperlink
- Remove hyperlink
- List hyperlinks
- Get specific hyperlink
Number Formatting:
- Get number formats (as 2D array)
- Set number format (uniform)
- Set number formats (individual)
Visual Formatting:
- Get style
- Set style (built-in Excel styles)
- Format range (font, color, borders, alignment, orientation)
Data Validation:
- Add validation rules (dropdowns, number/date/text rules)
- Get validation info
- Remove validation
Merge Operations:
- Merge cells
- Unmerge cells
- Get merge info
Cell Protection:
- Set cell lock status
- Get cell lock status
Auto-Sizing:
- Auto-fit columns
- Auto-fit rows
๐ Worksheets (16 operations)
Lifecycle:
- List worksheets
- Create worksheet
- Rename worksheet
- Copy worksheet
- Move worksheet
- Delete worksheet
Cross-Workbook Operations:
- Copy worksheet to file (atomic)
- Move worksheet to file (atomic)
Tab Colors:
- Set tab color (RGB)
- Get tab color
- Clear tab color
Visibility:
- Show worksheet
- Hide worksheet
- Very hide worksheet (hidden from UI)
- Get visibility status
- Set visibility status
๐ Data Connections (9 operations)
- List: View all data connections
- View: Get connection details
- Create: Create OLEDB/ODBC connections (requires provider installed)
- Test: Verify connection validity
- Refresh: Refresh connection data
- Delete: Remove connection
- Load To: Load connection data to worksheet (when supported)
- Get Properties: Get connection string and metadata
- Set Properties: Update connection string, command text, and settings
Supported Types:
- OLEDB (requires Microsoft.ACE.OLEDB.16.0 or similar)
- ODBC (requires ODBC driver installed)
- Power Query connections (atomic redirect to powerquery)
Automatic Fallback:
- TEXT/WEB connections automatically redirect to powerquery for reliable imports
๐ท๏ธ Named Ranges (Parameters) (6 operations)
- List: List all named ranges with references
- Read: Get value of a named range
- Write: Set value of a named range (ideal for parameter automation)
- Create: Create new named range
- Update: Modify existing named range
- Delete: Remove named range
Use Cases:
- Workbook parameter management without touching worksheets
- Ideal for automation: update parameter โ Power Query refreshes automatically
๐ VBA Macros (6 operations)
- List: List all VBA modules and procedures
- View: Display module code without exporting
- Import: Add VBA module from file
- Update: Modify existing VBA module
- Delete: Remove VBA module
- Run: Execute macro with optional parameters
Features:
- Version control through file exports
- Parameter passing to macros
- Full module lifecycle management
๏ฟฝ๏ธ Slicers (8 operations)
PivotTable Slicers:
- Create Slicer: Add slicer for PivotTable field with optional position
- List Slicers: List all PivotTable slicers in workbook
- Set Selection: Filter PivotTable by slicer selection (single or multi-select)
- Delete Slicer: Remove PivotTable slicer
Table Slicers:
- Create Table Slicer: Add slicer for Excel Table column
- List Table Slicers: List all Table slicers in workbook
- Set Table Selection: Filter Table by slicer selection
- Delete Table Slicer: Remove Table slicer
Use Cases:
- Interactive data filtering without modifying PivotTable/Table structure
- Dashboard creation with visual filter controls
- Multi-slicer filtering for complex data analysis
- Add Rule: Create conditional formatting rules
- Cell value comparisons (>, <, =, etc.)
- Expression-based formulas (custom DAX/Excel formulas)
- Color scales, data bars, icons
- Clear Rules: Remove formatting from ranges
๐ธ Screenshot (2 operations)
- Capture Range: Capture a specific range as a PNG image
- Capture Sheet: Capture the entire used area of a worksheet as a PNG image
- Uses Excelโs built-in rendering (CopyPicture) โ captures formatting, charts, conditional formatting
- MCP: Returns image directly as ImageContent (base64 PNG)
- CLI: Returns JSON with base64-encoded image data
๐ชง Window Management (9 operations)
- Show: Makes Excel visible and brings it to the foreground
- Hide: Hides the Excel window
- Bring to Front: Brings Excel to the foreground without changing visibility
- Get Info: Gets current window state (visibility, position, size, foreground status)
- Set State: Sets window state to normal, minimized, or maximized
- Set Position: Sets window position and size in points (left, top, width, height)
- Arrange: Arranges Excel window using preset layouts
- Set Status Bar: Displays custom text in Excelโs status bar for real-time feedback
- Clear Status Bar: Restores the default status bar text
Arrange Presets:
left-half / right-half โ Side-by-side with other applicationstop-half / bottom-half โ Stacked viewcenter โ Centered window (60% of screen)full-screen โ Maximized
Use Cases:
- Interactive โagent modeโ where users watch Excel respond to AI commands in real-time
- Side-by-side: Excel on one half, AI assistant on the other
- Visibility changes are reflected in session metadata (session list shows updated state)
๐ Total Operations Summary
| Category |
Operations |
| File Operations |
6 |
| Power Query |
12 |
| Data Model/DAX |
19 |
| Excel Tables |
27 |
| PivotTables |
30 |
| Charts |
28 |
| Ranges |
42 |
| Worksheets |
16 |
| Connections |
9 |
| Named Ranges |
6 |
| VBA Macros |
6 |
| Slicers |
8 |
| Conditional Formatting |
2 |
| Screenshot |
2 |
| Calculation Mode |
3 |
| Window Management |
9 |
| Total |
225 |
๐ Key Capabilities
Data Transformation:
- Comprehensive Power Query M code management
- Atomic import + load workflows
- Calculated fields and members for analysis
Data Model:
- Full DAX measure lifecycle
- Relationship management
- Multi-table integration
Analysis & Visualization:
- PivotTable creation and configuration
- Chart automation
- Custom calculations
Automation:
- VBA macro execution and management
- Named range parameter automation
- Conditional formatting rules
Data Loading:
- Multiple connection type support
- OLEDB/ODBC management
- Power Query atomic workflows
| Task |
Tool |
| Import data |
powerquery or connection |
| Create analysis |
pivottable (data model-based for OLAP) |
| Visualize data |
chart |
| Update parameters |
namedrange (write operation) |
| Manage formulas |
range (set-formulas) |
| Format data |
range (format-range, validate-range) |
| Script automation |
vba (run macro) |
๐ Documentation