Excel Pivot Wizard

Overview — Prerequisites — Instructions — Output — Examples — Error Handling — Resources

Overview

Creates pivot tables and visualizations from raw data using natural language commands. Automates the full workflow from data inspection to formatted pivot tables with charts, calculated fields, and conditional formatting so analysts can produce polished reports without manual Excel work.

Prerequisites

Node.js 18+
@negokaz/excel-mcp-server MCP server configured
Claude Code 1.0+
Source data in .xlsx format accessible locally

Instructions

Step 1: Understand the Data

Read the source Excel file and identify:

Column names and data types
Data grain (transaction-level, daily summary, etc.)
Which fields are dimensions (group by) vs measures (aggregate)
Row count and date range

If unclear, use AskUserQuestion to clarify what each column represents, what fields should be aggregated vs grouped, and what aggregation function to use (sum, average, count).

Step 2: Interpret the Request

Parse natural language into pivot table structure:

Request	Rows	Columns	Values
"Show sales by region"	Region	--	Sum of Sales
"Sales by region and month"	Region	Month	Sum of Sales
"Average order value by segment"	Segment	--	Avg of Order Value
"Count orders by category and rep"	Category	Sales Rep	Count of Orders

Step 3: Build the Pivot Table

Use the Excel MCP server to:

Create pivot table on a new sheet
Set row fields (one or more dimensions)
Set column fields if cross-tabulation requested
Set value fields with correct aggregation (sum, average, count, min, max)
Add subtotals and grand totals
Sort largest to smallest by default (chronological for dates)

Step 4: Add Enhancements

Calculated Fields (if applicable): Profit Margin %, Growth %, % of Total.

Conditional Formatting: Top 10% dark green, bottom 10% dark red, color gradient for heatmap.

Sorting: Largest to smallest by default, chronological for date fields.

Step 5: Create Visualization

Choose chart type based on analysis pattern:

Pattern	Chart Type
Comparisons across categories	Column/bar chart
Trends over time	Line chart
Composition/share	Pie/donut chart
Correlations	Scatter plot
Multiple metrics	Combo chart (column + line)

Step 6: Format Professionally

Currency: $1,250,000 or $1.25M
Counts: thousands separator (2,000)
Percentages: 1 decimal place (35.0%)
Bold headers, freeze top row and left column
Alternating row colors for readability

Step 7: Return Results

Report summary of what the pivot table shows, top 3-5 key insights, specific numbers for standout findings, suggested follow-up analyses, and offer to add slicers, filters, or drill-downs.

Output

.xlsx file with new pivot table sheet(s) added to the source workbook
Chart visualization matching the analysis pattern
Summary text with top insights and key numbers
Suggestions for follow-up analysis

Examples

Single Dimension Summary

User: "Show total sales by region"

Output:
| Region    | Total Sales |
|-----------|-------------|
| West      | $1,450,000  |
| Northeast | $1,250,000  |
| Midwest   | $1,100,000  |
| Southeast | $980,000    |
| Total     | $4,780,000  |

Insight: West leads at 30.3% of total sales.

Cross-Tabulation

User: "Sales by region and product category"

Output: 4x3 grid with row/column totals.
Insight: West + Electronics = highest cell at $550K.

Error Handling

Scenario	Response
No numeric columns found	Ask user which field to aggregate
Ambiguous dimension field	List options and ask user to choose
Too many categories (>50 rows)	Suggest Top N or grouping approach
Missing data in key columns	Report % missing, offer to exclude nulls
Date field not recognized	Ask user to confirm date column and format

Edge Cases

If data has no headers, infer from content or ask user
If user asks for "breakdown" without specifying metric, default to count
If multiple numeric columns exist, ask which to aggregate
If data spans multiple sheets, ask which sheet to analyze

Resources

${CLAUDE_SKILL_DIR}/references/REFERENCE.md - Pivot table best practices, chart selection guide

excel-pivot-wizard