# ExcelMcp - Complete Feature Reference

**25 specialized tools with 230 operations for comprehensive Excel automation**

---

## 📁 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), preserving M code by default
- **Update:** Update M code, preserving M code by default, with optional 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)

**M-Code Formatting:** M code is preserved exactly by default. Create and Update can opt in to remote formatting with `formatMCode=true`, which sends M code to powerqueryformatter.com and adds network latency. If remote formatting fails, the original M code is saved unchanged.

---

## 📊 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, preserving DAX by default (format types: Currency, Percentage, Decimal, General)
- **Update Measure:** Modify existing measure, preserving DAX by default
- **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

**DAX Formatting:** DAX formulas are preserved exactly by default, subject to Excel locale separator translation. CreateMeasure and UpdateMeasure can opt in to remote formatting with `formatDax=true`, which sends DAX to daxformatter.com and adds network latency. If remote formatting fails, the original DAX is saved unchanged.

**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 (46 operations)

Formatting split: use `range` for number display formats such as dates, currency, percentages, and text display. Use `range_format` for visual styling, validation, auto-fit, and size/layout changes.

**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 (`range`):**
- Get number formats (as 2D array)
- Set number format (uniform)
- Set number formats (individual)

**Visual Formatting (`range_format`):**
- Get style
- Set style (built-in Excel styles)
- Format range (font, color, borders, alignment, orientation)
- Format multiple ranges with one shared formatting payload

**Data Validation (`range_format`):**
- Add validation rules (dropdowns, number/date/text rules)
- Get validation info
- Remove validation

**Merge Operations (`range_format`):**
- Merge cells
- Unmerge cells
- Get merge info

**Cell Protection:**
- Set cell lock status
- Get cell lock status

**Auto-Sizing (`range_format`):**
- 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 VBA components and discovered procedures
- **View:** Display component code without exporting
- **Import:** Create a new standard module from code or file input
- **Update:** Replace code in an existing VBA component
- **Delete:** Remove a VBA component by name
- **Run:** Execute a procedure with optional string parameters

**Features:**
- Procedural/module-focused VBA support for `.xlsm` workbooks
- Manual VBA trust prerequisite in Excel (no trust-configuration command)
- Import creates standard modules; list/view also cover class, form, and document components

---

## �️ 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

---

## �🎨 Conditional Formatting (2 operations)

- **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 applications
- `top-half` / `bottom-half` — Stacked view
- `center` — 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 | 29 |
| Ranges | 46 |
| Worksheets | 16 |
| Connections | 9 |
| Named Ranges | 6 |
| VBA Macros | 6 |
| Slicers | 8 |
| Conditional Formatting | 2 |
| Screenshot | 2 |
| Calculation Mode | 3 |
| Window Management | 9 |
| **Total** | **230** |

---

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

---

## 🔧 Tool Selection Quick Reference

| 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` / `range_format` (`format-range`, `format-ranges`, `validate-range`) |
| Script automation | `vba` (run macro) |

---

## 📚 Documentation

- **[Installation Guide](https://github.com/sbroenne/mcp-server-excel/blob/main/docs/INSTALLATION.md)** - Setup for all AI assistants
- **[MCP Server Guide](https://github.com/sbroenne/mcp-server-excel/blob/main/src/ExcelMcp.McpServer/README.md)** - Tool documentation and examples
- **[CLI Guide](https://github.com/sbroenne/mcp-server-excel/blob/main/src/ExcelMcp.CLI/README.md)** - Command-line reference
- **[Agent Skills](https://github.com/sbroenne/mcp-server-excel/blob/main/skills/excel-mcp/SKILL.md)** - Cross-platform AI assistant guidance (agentskills.io)
- **[Contributing](https://github.com/sbroenne/mcp-server-excel/blob/main/docs/CONTRIBUTING.md)** - Development guidelines
- **[Releases](https://github.com/sbroenne/mcp-server-excel/releases)** - Latest updates and features
