intent-solutions-io/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