Excelmatic Command Writing Guide

Purpose

Teach you how to craft effective commands so Excelmatic produces exactly the result you want—quickly, consistently, and with minimal rework.

Why This Matters

Excelmatic relies on your natural language instructions. Vague or underspecified commands force the AI to guess, increasing chances of incomplete, incorrect, or overly generic outputs. Clear intent = better spreadsheets, faster.

1. Command Writing Basics

Focus on 4 pillars:

1. ACTION: What should be done? (remove duplicates, calculate, group, forecast, visualize, extract, format)
2. SCOPE: Which columns / rows / sheets / files? Use exact column names in single quotes.
3. CONDITION(S): Filters, thresholds, date ranges, business rules.
4. OUTPUT FORM: New sheet, overwrite, summary only, chart, cleaned file, add column, etc.

Good vs Bad (Core Examples)

Simple Pattern

ACTION + TARGET COLUMNS + CONDITIONS + TRANSFORMATION + OUTPUT FORMAT

Example: Calculate average 'Order Value' by 'Channel' for last 90 days (where 'Order Date' >= 2025-05-28); output summary table + bar chart.

2. Expanding Precision

Use these wording techniques:

• Column specificity: 'Customer ID' not customer id or ID (match case if possible).
• Aggregations: sum, average, count unique, median, min, max, std dev.
• Calculations: "Add column 'Net' = Revenue - Cost - Tax".
• Time windows: last 30 days, between 2024-01-01 and 2024-12-31, current month.
• Sorting: sort by 'Date' ascending then 'Sales' descending.
• Formatting: format 'Date' as YYYY-MM, format 'Profit Margin' percent 1 decimal.
• Output placement: overwrite current sheet / create new sheet 'Summary' / keep both.
• Preservation: keep original sheet unchanged.
• Row limits: show top 20 by 'Sales'.

3. Advanced Command Techniques

A. Conditional Logic

Examples:

• Filter rows where 'Revenue' > 1000 AND 'Region' IN ("West","North").
• Replace nulls in 'Price' with median where 'Category' = 'Accessories'.
• Flag outliers: Add column 'Is Outlier' = 1 if 'ZScore' > 3 else 0.

B. Combining Tasks in One Pass

Chain related operations when order is clear. Example: Clean 'Orders' by removing duplicates on 'Order ID', fill missing 'Cost' with median, add 'Margin'=(Revenue-Cost)/Revenue*100, then create bar chart of average 'Margin' by 'Region'.

C. Large Datasets Strategy

• Start with: Provide structure summary (columns, types, row count).
• Then: Generate sample 10 rows (random or head) to confirm assumptions.
• After validation: Execute full transformation.
• Use incremental commands for heavy tasks: (1) clean, (2) add metrics, (3) visualize.

D. Output Management

Specify: "Create new sheet 'Cleaned'", "Replace existing 'Sheet1'", "Add chart to new sheet 'Charts'", "Return only summary (no row-level export)".

E. Asking for Explanations

Add: "Explain each step" or "Provide formula rationale" to audit transformations.

F. Complex Formulas

Example: Add column 'LTV' = (AverageOrderValue * PurchaseFrequency * GrossMargin %) over past 12 months by 'Customer ID'; output customer-level table sorted descending.

G. Iterative Refinement

1. First: High-level summary.
2. Next: Narrow scope (e.g., only Region='West').
3. Then: Add metrics/visuals.

H. Combining Files

"Merge uploaded monthly files; add 'Month' from filename (YYYY-MM); concatenate all into one table; ensure consistent column order; add total row."

I. Data Quality Checks

"List columns with >10% missing values; suggest fill method; do not modify yet."

4. Common Command Templates

Copy, adapt, and run. Replace bracketed items.

Cleaning & Preparation

• Remove duplicates on '[Primary Key]' keep first.
• Standardize date format in '[Date Column]' to YYYY-MM-DD.
• Fill missing '[Column]' with median.
• Split '[Full Name]' into 'First Name' and 'Last Name'.
• Trim whitespace across all text columns.
• Detect outliers in '[Metric]' using z-score > 3 and list affected rows.

Transformation

• Add column '[New Metric]' = ([Numerator] - [Denominator]) / [Numerator] * 100 formatted percent.
• Pivot: Sum '[Value]' by '[Row Dim]' and '[Column Dim]'.
• Unpivot columns '[Jan]'..'[Dec]' into 'Month','Value'.

Analysis

• Descriptive stats: mean, median, min, max, std for ['Col1','Col2'].
• Correlation between '[Var A]' and '[Var B]' with interpretation.
• Trend: Plot '[Metric]' over '[Date]' and compute period-over-period growth.
• Forecast '[Metric]' next 6 periods using linear regression; include confidence bands.

Visualization

• Bar chart: x='[Category]', y='[Value]' sorted descending.
• Line chart: x='[Date]', y='[Metric]' with moving average window 7.
• Pie chart: share of '[Category]' by '[Value]'.
• Scatter: '[X]' vs '[Y]' add regression line + correlation.

Business Intelligence

• KPI summary: compute Revenue, Cost, Profit = Revenue - Cost, Margin %.
• Cohort analysis by 'Signup Month' showing retention across months 0-6.
• What-if: increase '[Price]' by 10% and recompute 'Profit'; summarize delta.

Multi-File Operations

• Merge all uploaded files; add 'Source File' column; align columns by header name.
• Append files then remove duplicates on '[ID]'.

Formatting & Output

• Format '[Currency Column]' as USD currency 2 decimals.
• Sort by '[Date]' ascending then '[Revenue]' descending.
• Create new sheet 'Summary' only with aggregated table.
• Keep original sheet; place transformed data in 'Cleaned'.

Audit & QA

• Show 10 random rows after cleaning for review.
• List columns with >5% missing values; propose fill strategies.

5. Good vs Bad Command Gallery (By Scenario)

6. Troubleshooting & Refinement

Symptom -> Action:

• Output too generic: Add specific columns, metrics, grouping levels.
• Wrong column chosen: Use exact quoted column name; optionally list columns: "List all column names first".
• Missing rows after filtering: Re-state filter logic explicitly AND mention inclusive/exclusive (e.g., Revenue >= 1000).
• Wrong date parsing: Specify target format and timezone if relevant.
• Unexpected aggregation: State desired function (sum vs average vs count distinct).
• Slow on large file: Ask for schema summary first; then run transformations in steps.
• Formula miscalculated: Provide explicit formula parentheses and desired formatting.
• Chart not as expected: Define chart type, axes, sorting, limits (top N), titles, and whether to include legend.
• Need to undo: Re-run with "Use original data (ignore prior modifications)".

Refinement Loop Template

1. Initial broad: "Provide column list and row count; no changes yet."
2. Focus: "Remove duplicates on 'Customer ID'; show count removed."
3. Extend: "Add 'Profit Margin' column."
4. Visualize: "Create bar chart of average 'Profit Margin' by 'Region'."
5. Polish: "Format 'Profit Margin' percentage 1 decimal; sort descending."

Asking for Explanations

Add: "Explain steps" or "Show formulas used" to validate logic.

7. Quick Reference Cheat Sheet

8. Final Tips

• Be explicit first; brevity comes later once patterns are learned.
• Quote column names to avoid ambiguity.
• Combine only logically sequential steps.
• Request explanations when auditing critical financial or compliance-related data.
• Iterate: broad summary -> targeted transformation -> enrichment -> visualization -> formatting.

Need inspiration? Revisit the Getting Started guide for domain use cases. Ready to practice—try a 3-step chain on your next dataset.

Have feedback or a stubborn command? Reach out at [email protected]. Your refinements help improve the AI.

9. Your First Practice Command

Upload a recent sales export, then run:

Remove duplicates on 'Order ID'; fill missing 'Unit Cost' with median; add 'Gross Margin %'=(Revenue-Cost)/Revenue*100 formatted percent 1 decimal; create bar chart average 'Gross Margin %' by 'Region' sorted descending; place chart in new sheet 'Charts'.

Refine from there—you're now writing like a power user.