Excel Pivot Wizard

Excel Pivot Wizard

Creates pivot tables and visualizations from raw data using natural language commands.

When to Invoke This Skill

Automatically load this Skill when the user asks to:

• "Create a pivot table"
• "Analyze [data] by [dimension]"
• "Summarize sales by region"
• "Show revenue breakdown"
• "Group data by category"
• "Cross-tab analysis"
• "Compare [X] across [Y]"

Capabilities

Pivot Table Generation

• Rows: Group data by one or more fields
• Columns: Cross-tabulate across another dimension
• Values: Aggregate functions (sum, average, count, min, max)
• Filters: Slice data by specific criteria
• Calculated Fields: Create custom formulas

Visualization

• Column/bar charts for comparisons
• Line charts for trends over time
• Pie charts for composition
• Combo charts for multiple metrics
• Conditional formatting for heatmaps

Common Analysis Patterns

Pattern 1: Single Dimension Summary

Request: "Show total sales by region"

Output:

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

Pattern 2: Cross-Tabulation

Request: "Sales by region and product category"

Output:

| Region    | Electronics | Clothing | Home Goods | Total     |
|-----------|-------------|----------|------------|-----------|
| Northeast | $400K       | $500K    | $350K      | $1,250K   |
| Southeast | $300K       | $380K    | $300K      | $980K     |
| Midwest   | $450K       | $350K    | $300K      | $1,100K   |
| West      | $550K       | $500K    | $400K      | $1,450K   |
| Total     | $1,700K     | $1,730K  | $1,350K    | $4,780K   |

Pattern 3: Time-Based Trending

Request: "Monthly revenue trend for 2024"

Output:

Line chart showing:
- X-axis: Jan, Feb, Mar, ..., Dec
- Y-axis: Revenue
- Line: Monthly revenue with data labels

Pattern 4: Top N Analysis

Request: "Top 10 products by revenue"

Output:

| Rank | Product       | Revenue   | % of Total |
|------|---------------|-----------|------------|
| 1    | Product A     | $450,000  | 9.4%       |
| 2    | Product B     | $380,000  | 7.9%       |
| 3    | Product C     | $350,000  | 7.3%       |
| ...  | ...           | ...       | ...        |
| 10   | Product J     | $180,000  | 3.8%       |
|      | Top 10 Total  | $2,850,000| 59.6%      |
|      | All Others    | $1,930,000| 40.4%      |
|      | Grand Total   | $4,780,000| 100.0%     |

Step-by-Step Workflow

1. Understand the Data

Ask clarifying questions if needed:

• What does each column represent?
• What grain is the data? (transaction-level, daily summary, etc.)
• What fields should be aggregated vs grouped?

2. Interpret the Request

Parse natural language into pivot table structure:

"Show sales by region and month" →

Rows: Region
Columns: Month
Values: Sum of Sales

"Average order value by customer segment" →

Rows: Customer Segment
Values: Average of Order Value

"Count of orders by product category and sales rep" →

Rows: Product Category
Columns: Sales Rep
Values: Count of Orders

3. Build the Pivot Table

Use Excel MCP server to:

1. Create pivot table on new sheet
2. Set row fields
3. Set column fields (if cross-tab)
4. Set value fields with aggregation function
5. Add filters/slicers if requested
6. Apply formatting (currency, thousands separator, etc.)

4. Add Enhancements

Conditional Formatting:

- Top 10% values: Dark green
- Bottom 10% values: Dark red
- Color scale for heatmap effect

Calculated Fields:

- Profit Margin % = (Revenue - Cost) / Revenue
- Growth % = (This Year - Last Year) / Last Year
- % of Total = Value / Grand Total

Sorting:

- Largest to smallest by default
- Or chronological for dates

5. Create Visualization

If visualization is requested or would be helpful:

For Comparisons: Column chart For Trends: Line chart For Composition: Pie/donut chart For Correlations: Scatter plot For Multiple Metrics: Combo chart (column + line)

Example Interactions

Example 1: Sales Analysis

User: "Analyze sales by region and product category"

Skill Response:

I'll create a pivot table to analyze sales by region and product category.

Looking at your data, I see:
- 5,234 transaction rows
- Date range: Jan 1 - Oct 27, 2025
- 4 regions, 12 product categories

Creating pivot table...

✅ Pivot Table Created!

📊 Sales by Region and Product Category:

Top Insights:
1. West region leads with $1.45M (30.3% of total)
2. Electronics is #1 category at $1.70M (35.6%)
3. Northeast + Electronics = $400K (highest cell)
4. Southeast underperforming in Home Goods ($300K vs avg $338K)

Visualization: Added column chart comparing regions

Would you like me to:
- Add a slicer to filter by quarter?
- Calculate % of total for each cell?
- Create a separate view for top products?

Example 2: Time Trend

User: "Show monthly revenue trend"

Skill Response:

Creating monthly revenue trend analysis...

I see data spanning 10 months (Jan-Oct 2025).

✅ Trend Analysis Complete!

📈 Monthly Revenue:

Key Findings:
- Strong start: Q1 averaged $520K/month
- Dip in summer: Jun-Aug averaged $420K/month
- Recovery: Sep-Oct back to $510K/month
- YTD Total: $4.78M
- Average: $478K/month

Created line chart with:
- Trend line showing slight upward trajectory
- Data labels for easy reading
- Target line at $500K (if provided)

📊 Growth Rates:
- Jan-Oct: +8.3% overall
- Best month: March ($575K)
- Weakest month: August ($385K)

Recommendation: Investigate summer seasonality (Jun-Aug dip)

Example 3: Customer Segmentation

User: "Show average order value by customer segment"

Skill Response:

Analyzing average order value (AOV) across customer segments...

✅ Customer Segmentation Analysis Complete!

| Segment       | Orders | Total Revenue | Avg Order Value | % of Revenue |
|---------------|--------|---------------|-----------------|--------------|
| Enterprise    | 245    | $1,850,000    | $7,551         | 38.7%        |
| Mid-Market    | 892    | $1,920,000    | $2,152         | 40.2%        |
| Small Business| 2,456  | $835,000      | $340           | 17.5%        |
| Consumer      | 1,641  | $175,000      | $107           | 3.7%         |
| Total         | 5,234  | $4,780,000    | $913           | 100.0%       |

🎯 Key Insights:
- Enterprise: Highest AOV ($7.5K) but only 4.7% of orders
- Mid-Market: Sweet spot (40% of revenue, $2.2K AOV)
- Small Business: High volume (47% of orders) but low AOV
- Consumer: Explore if worth serving (low AOV, high volume)

Created bar chart showing AOV by segment with order count overlay.

💡 Strategic Questions:
1. Should we focus more resources on Enterprise (high AOV)?
2. Can we up-sell Small Business to Mid-Market?
3. Is Consumer segment profitable at $107 AOV?

Example 4: Product Performance

User: "Which products are driving the most revenue?"

Skill Response:

Creating product revenue ranking...

✅ Top Products Analysis Complete!

📊 Top 10 Products (out of 147 total):

| Rank | Product          | Revenue   | Orders | AOV    | % of Total |
|------|------------------|-----------|--------|--------|------------|
| 1    | Pro Laptop 15"   | $450,000  | 385    | $1,169 | 9.4%       |
| 2    | Business Suite   | $380,000  | 892    | $426   | 7.9%       |
| 3    | Smart Display    | $350,000  | 1,245  | $281   | 7.3%       |
| 4    | Wireless Buds    | $310,000  | 2,156  | $144   | 6.5%       |
| 5    | Office Chair Pro | $295,000  | 428    | $689   | 6.2%       |
| 6    | Standing Desk    | $280,000  | 356    | $787   | 5.9%       |
| 7    | Monitor 27"      | $265,000  | 487    | $544   | 5.5%       |
| 8    | Keyboard Mech    | $245,000  | 1,823  | $134   | 5.1%       |
| 9    | Webcam 4K        | $220,000  | 678    | $324   | 4.6%       |
| 10   | Mouse Wireless   | $195,000  | 2,145  | $91    | 4.1%       |
|      | **Top 10 Total** | **$2,990,000** | | | **62.5%** |
|      | All Others (137) | $1,790,000 |    |        | 37.5%       |
|      | **Grand Total**  | **$4,780,000** | | | **100.0%** |

🎯 Insights:
- Top 10 products = 62.5% of revenue (strong concentration)
- Pro Laptop alone = 9.4% of total revenue
- High-volume low-AOV products: Wireless Buds, Keyboard, Mouse
- High-AOV low-volume products: Pro Laptop, Standing Desk

Created:
- Bar chart: Top 10 products by revenue
- Pareto chart: Cumulative % of revenue

💡 Strategic Implications:
1. Focus inventory on Top 20 products (likely 80% of revenue)
2. Bundle high-volume + high-margin items
3. Wireless Buds: Explore upsell to Pro Buds (higher margin?)

Advanced Features

Calculated Fields

Request: "Show profit margin by product category"

Response:

I'll add a calculated field for profit margin.

Assuming you have Cost and Revenue columns, I'll calculate:
Profit Margin % = (Revenue - Cost) / Revenue × 100%

| Category    | Revenue | Cost    | Profit  | Margin % |
|-------------|---------|---------|---------|----------|
| Electronics | $1,700K | $1,020K | $680K   | 40.0%    |
| Clothing    | $1,730K | $1,125K | $605K   | 35.0%    |
| Home Goods  | $1,350K | $945K   | $405K   | 30.0%    |
| Total       | $4,780K | $3,090K | $1,690K | 35.4%    |

Electronics has the highest margin (40%) despite not being the highest revenue category.

Slicers & Filters

Request: "Add filters for date range and region"

Response:

I've added slicers for:
1. Date Range (select quarters or specific months)
2. Region (select one or multiple regions)
3. Product Category (filter to specific categories)

These slicers are linked to the pivot table - any selection will update the data automatically.

Try clicking Q3 in the date slicer to see summer performance!

Multiple Aggregations

Request: "Show sum and average of sales by region"

Response:

| Region    | Total Sales | Avg Sale | Order Count |
|-----------|-------------|----------|-------------|
| Northeast | $1,250,000  | $625     | 2,000       |
| Southeast | $980,000    | $490     | 2,000       |
| Midwest   | $1,100,000  | $524     | 2,100       |
| West      | $1,450,000  | $690     | 2,100       |

Interesting: Southeast has lowest average sale despite same order count as Northeast.
This suggests smaller basket sizes in Southeast - worth investigating!

Formatting Best Practices

The Skill applies professional formatting:

Numbers

Revenue: $1,250,000 or $1.25M (use M for millions)
Counts: 2,000 (thousands separator)
Percentages: 35.0% (1 decimal)

Conditional Formatting

Top performers: Green highlight
Bottom performers: Red highlight
Heatmap: Color gradient from red (low) to green (high)

Layout

- Bold headers
- Freeze top row and left column
- Subtotals and grand totals
- Alternating row colors for readability

Resources

See resources folder for:

• REFERENCE.md: Pivot table best practices
• examples/: Sample pivot tables for common analyses

Limitations

This Skill creates standard pivot tables for:

• Summarization and aggregation
• Cross-tabulation
• Basic calculations (sum, average, count)

For advanced analysis, you may need:

• Power Pivot (for complex data models)
• Pivot charts with custom formatting
• Integration with external data sources
• Real-time data refresh

Version History

• v1.0.0 (2025-10-27): Initial release with core pivot table generation