Microsoft Excel MCP Server

Create a powerful Model Context Protocol (MCP) server for Microsoft Excel in minutes with our AI Gateway. This guide walks you through setting up seamless spreadsheet automation with enterprise-grade security and instant OAuth authentication.

About Microsoft Excel API

The Microsoft Graph API provides comprehensive access to Excel workbooks stored in OneDrive, SharePoint, and Teams. It enables programmatic manipulation of worksheets, ranges, tables, charts, and formulas within the Microsoft 365 ecosystem.

Key Capabilities

• Workbook Management: Create, open, and save workbooks
• Worksheet Operations: Add, delete, and modify sheets
• Data Manipulation: Read and write cell values
• Table Management: Create and modify Excel tables
• Chart Creation: Generate various chart types
• Formula Evaluation: Calculate and update formulas
• Formatting: Apply styles and conditional formatting
• Named Items: Work with named ranges and formulas

API Features

• Microsoft Graph API: Unified endpoint
• REST Interface: Standard HTTP operations
• Real-time Collaboration: Multi-user support
• OAuth 2.0: Enterprise authentication
• Session Management: Persistent operations
• Batch Requests: Efficient bulk updates
• Change Tracking: Version history
• Cross-platform: Web, desktop, mobile

What You Can Do with Microsoft Excel MCP Server

The MCP server transforms Excel's API into a natural language interface, enabling AI agents to:

Workbook & Worksheet Management

• Workbook Operations

  • "Create new budget workbook for 2025"
  • "Open quarterly report from SharePoint"
  • "Copy workbook as template"
  • "Save workbook to Teams channel"

• Worksheet Management

  • "Add new sheet for January data"
  • "Rename sheet to 'Q1 Summary'"
  • "Copy worksheet to another workbook"
  • "Delete unused worksheets"

• Navigation & Selection

  • "Go to cell A1 in Sales sheet"
  • "Select range B2:D10"
  • "Find all cells containing 'Total'"
  • "Navigate to named range 'Budget'"

Data Operations

• Cell Operations

  • "Enter sales figures in column B"
  • "Update cell C5 with new total"
  • "Clear data from last month"
  • "Copy formulas down column"

• Range Operations

  • "Fill series from 1 to 100"
  • "Sort data by revenue descending"
  • "Filter to show only active accounts"
  • "Find and replace old product names"

• Bulk Data Import

  • "Import CSV data into new sheet"
  • "Paste JSON array as table"
  • "Load data from SQL query"
  • "Update from external API"

Table Management

• Table Creation

  • "Convert range A1:E100 to table"
  • "Create table with headers"
  • "Name table as 'SalesData'"
  • "Add total row to table"

• Table Operations

  • "Add new column for calculations"
  • "Insert row at position 5"
  • "Delete duplicate rows"
  • "Resize table to include new data"

• Table Features

  • "Apply table style 'Medium 2'"
  • "Enable filter buttons"
  • "Add slicer for Region column"
  • "Create structured references"

Charts & Visualization

• Chart Creation

  • "Create column chart from sales data"
  • "Build pie chart of market share"
  • "Generate line graph of trends"
  • "Design combo chart with dual axis"

• Chart Customization

  • "Change chart type to waterfall"
  • "Add trendline to series"
  • "Update chart title and labels"
  • "Apply corporate color scheme"

• Advanced Charts

  • "Create pivot chart from table"
  • "Build sparklines in cells"
  • "Design dashboard with multiple charts"
  • "Add data labels and callouts"

Formulas & Functions

• Formula Operations

  • "Sum values in column D"
  • "Calculate average of range"
  • "Add VLOOKUP for pricing"
  • "Create IF statement for status"

• Advanced Formulas

  • "Build INDEX MATCH formula"
  • "Create dynamic SUMIFS"
  • "Add array formula for analysis"
  • "Design nested IF conditions"

• Named Formulas

  • "Create named formula for tax rate"
  • "Define dynamic named range"
  • "Build custom function"
  • "Reference named items in formulas"

Formatting & Styling

• Cell Formatting

  • "Format cells as currency"
  • "Apply percentage to column"
  • "Set date format to MM/DD/YYYY"
  • "Add borders around data"

• Conditional Formatting

  • "Highlight cells above average"
  • "Apply color scale to values"
  • "Add icon sets for KPIs"
  • "Create data bars in cells"

• Style Management

  • "Apply corporate theme"
  • "Create custom cell style"
  • "Format as financial report"
  • "Standardize number formats"

PivotTables & Analysis

• PivotTable Creation

  • "Create pivot from sales data"
  • "Summarize by region and product"
  • "Add calculated fields"
  • "Group dates by month"

• PivotTable Management

  • "Update pivot data source"
  • "Change aggregation to average"
  • "Add filters and slicers"
  • "Drill down to details"

• Analysis Features

  • "Show values as % of total"
  • "Add running total column"
  • "Create year-over-year comparison"
  • "Design pivot dashboard"

Integration & Automation

• Data Connections

  • "Connect to Power BI dataset"
  • "Link to SharePoint list"
  • "Refresh external data"
  • "Update from web query"

• Automation

  • "Schedule data refresh"
  • "Trigger calculations"
  • "Export to PDF automatically"
  • "Send email with attachment"

• Collaboration

  • "Share workbook with team"
  • "Set edit permissions"
  • "Track changes by user"
  • "Add comments to cells"

Prerequisites

• Access to Cequence AI Gateway
• Microsoft 365 subscription with Excel
• Azure AD tenant access
• App registration permissions

Step 1: Register Azure AD Application

1.1 Access Azure Portal

1. Go to portal.azure.com
2. Navigate to Azure Active Directory
3. Select App registrations
4. Click New registration

1.2 Configure Application

1. Basic Information:

   • Name: "AI Gateway Excel MCP"
   • Supported account types: Choose based on needs
   • Redirect URI: Web platform

     https://auth.aigateway.cequence.ai/v1/outbound/oauth/callback

2. Click Register

1.3 Configure API Permissions

1. Go to API permissions
2. Click Add a permission
3. Select Microsoft Graph
4. Choose Delegated permissions
5. Add Excel-related permissions (see scopes section)
6. Grant admin consent if required

1.4 Create Client Secret

1. Go to Certificates & secrets
2. Click New client secret
3. Add description and expiry
4. Copy the secret value immediately

1.5 Note Application Details

1. Go to Overview
2. Copy Application (client) ID
3. Copy Directory (tenant) ID

Step 2-4: Standard Setup

Follow standard steps to access AI Gateway, find Microsoft Excel API, and create MCP server.

Step 5: Configure API Endpoints

1. Base URL: https://graph.microsoft.com/v1.0
2. Select Excel endpoints:
   • Workbook endpoints
   • Worksheet endpoints
   • Range endpoints
   • Table endpoints
3. Click Next

Step 6: MCP Server Configuration

1. Name: "Microsoft Excel Automation"
2. Description: "Spreadsheet automation and analysis"
3. Configure production mode
4. Click Next

Step 7: Configure Authentication

1. Authentication Type: OAuth 2.0
2. Authorization URL:

   https://login.microsoftonline.com/{tenant-id}/oauth2/v2.0/authorize

3. Token URL:

   https://login.microsoftonline.com/{tenant-id}/oauth2/v2.0/token

4. Enter Client ID and Secret
5. Select required scopes

Available Microsoft Excel OAuth Scopes

File Access

• Files.Read

  • Read user's files
  • Open workbooks
  • View file metadata
  • List files

• Files.ReadWrite

  • Full file access
  • Create workbooks
  • Save changes
  • Delete files

• Files.Read.All / Files.ReadWrite.All

  • Access all files user can access
  • Team and SharePoint files
  • Shared workbooks
  • Organization files

SharePoint & Sites

• Sites.Read.All / Sites.ReadWrite.All
  • Access SharePoint sites
  • Work with team files
  • Document libraries
  • Site workbooks

Offline Access

• offline_access
  • Refresh tokens
  • Long-term access
  • Background operations
  • Scheduled tasks

User Profile

• User.Read
  • Basic profile info
  • Email address
  • Display name
  • User ID

Recommended Scope Combinations

For Personal Workbooks:

Files.ReadWrite
User.Read
offline_access

For Team Collaboration:

Files.ReadWrite.All
Sites.ReadWrite.All
User.Read
offline_access

For Read-Only Analysis:

Files.Read
Sites.Read.All
User.Read

Step 8-10: Complete Setup

Configure security, choose deployment, and deploy.

Using Your Microsoft Excel MCP Server

With Claude Desktop

{
  "servers": {
    "excel": {
      "url": "your-mcp-server-url",
      "auth": {
        "type": "oauth2",
        "client_id": "your-client-id"
      }
    }
  }
}

Natural Language Commands

• "Open budget spreadsheet from OneDrive"
• "Create pivot table from sales data"
• "Add chart showing monthly trends"
• "Calculate totals for each department"
• "Format report with company colors"

API Integration Example

// Initialize MCP client
const mcpClient = new MCPClient({
  serverUrl: 'your-mcp-server-url',
  auth: {
    type: 'oauth2',
    token: 'access-token'
  }
});

// Create new workbook
const workbook = await mcpClient.excel.workbooks.create({
  name: 'Financial Report 2025.xlsx'
});

// Add worksheet
const worksheet = await mcpClient.excel.worksheets.add({
  workbookId: workbook.id,
  name: 'Q1 Revenue'
});

// Write data to range
const data = [
  ['Month', 'Product A', 'Product B', 'Total'],
  ['January', 45000, 38000, '=B2+C2'],
  ['February', 52000, 41000, '=B3+C3'],
  ['March', 48000, 44000, '=B4+C4'],
  ['Q1 Total', '=SUM(B2:B4)', '=SUM(C2:C4)', '=SUM(D2:D4)']
];

await mcpClient.excel.ranges.update({
  workbookId: workbook.id,
  worksheetId: worksheet.id,
  address: 'A1:D5',
  values: data
});

// Format as table
const table = await mcpClient.excel.tables.add({
  workbookId: workbook.id,
  worksheetId: worksheet.id,
  address: 'A1:D4',
  hasHeaders: true,
  name: 'RevenueTable'
});

// Apply formatting
await mcpClient.excel.ranges.format({
  workbookId: workbook.id,
  worksheetId: worksheet.id,
  address: 'B2:D5',
  format: {
    numberFormat: '$#,##0.00',
    font: { name: 'Calibri', size: 11 },
    fill: { color: '#F2F2F2' }
  }
});

// Add conditional formatting
await mcpClient.excel.conditionalFormats.add({
  workbookId: workbook.id,
  worksheetId: worksheet.id,
  range: 'D2:D4',
  rule: {
    type: 'cellValue',
    operator: 'greaterThan',
    value: 90000,
    format: {
      fill: { color: '#92D050' },
      font: { bold: true }
    }
  }
});

// Create chart
const chart = await mcpClient.excel.charts.add({
  workbookId: workbook.id,
  worksheetId: worksheet.id,
  type: 'ColumnClustered',
  sourceData: 'A1:C4',
  seriesBy: 'Columns'
});

// Update chart properties
await mcpClient.excel.charts.update({
  workbookId: workbook.id,
  worksheetId: worksheet.id,
  chartId: chart.id,
  title: { text: 'Q1 Revenue by Product' },
  legend: { position: 'Bottom' },
  axes: {
    categoryAxis: { title: { text: 'Month' } },
    valueAxis: { title: { text: 'Revenue ($)' } }
  }
});

// Create pivot table
const pivotTable = await mcpClient.excel.pivotTables.add({
  workbookId: workbook.id,
  sourceData: 'RevenueTable',
  destination: 'F1',
  name: 'RevenuePivot',
  rows: ['Month'],
  values: [
    { name: 'Product A', function: 'Sum' },
    { name: 'Product B', function: 'Sum' }
  ]
});

// Save workbook
await mcpClient.excel.workbooks.save({
  workbookId: workbook.id
});

Common Use Cases

Financial Reporting

• Budget creation and tracking
• Financial statements
• Expense reports
• Revenue analysis

Data Analysis

• Statistical analysis
• Trend identification
• Forecasting models
• KPI dashboards

Project Management

• Gantt charts
• Resource allocation
• Timeline tracking
• Status reports

Business Intelligence

• Executive dashboards
• Performance metrics
• Competitive analysis
• Market research

Security Best Practices

1. OAuth Security:

   • Use minimal scopes
   • Implement token refresh
   • Monitor access patterns
   • Regular credential rotation

2. Data Protection:

   • Encrypt sensitive data
   • Control file sharing
   • Audit access logs
   • Implement DLP policies

3. Workbook Security:

   • Password protect files
   • Use Information Rights Management
   • Control external sharing
   • Monitor modifications

Troubleshooting

Common Issues

1. Authentication Errors

   • Verify app registration
   • Check tenant ID
   • Validate redirect URI
   • Review permissions

2. API Errors

   • Confirm file permissions
   • Check range addresses
   • Validate data types
   • Review session state

3. Formula Errors

   • Verify formula syntax
   • Check cell references
   • Confirm calculation mode
   • Test in Excel UI

Getting Help

• Documentation: AI Gateway Docs
• Support: support@cequence.ai
• Microsoft Graph: docs.microsoft.com/graph/api/resources/excel

---