Spreadsheet Analysis and Visualization

When to use this

• You have a sales or ops export and need analysis plus charts
• Dozens of spreadsheets need merging into one master table
• You need complex formulas (lookups, pivots, conditional aggregation)
• Data needs cleaning (dedupe, normalization, missing values)

The workflow

1. Make it read the structure first

Don’t jump to “analyze this.” Ask for the shape:

Read ~/data/sales.xlsx and tell me:

1. How many sheets, and what each contains
2. Row count, column names, and data types per sheet
3. Missing values, duplicate rows, inconsistent formats

Why it matters: this surfaces 80% of problems — dates stored as text, currency symbols mixed into amount columns, hidden blank rows.

2. State the analysis goal

It doesn’t know which metrics you care about.

❌ "Analyze this sales data"
✅ "Aggregate revenue and order count by region, identify the top 5 regions
     and any region down more than 20% month over month.
     Output: a conclusion, one bar chart, one trend line."

3. Clean before analyzing

Dirty data produces wrong conclusions every time.

Issue	Fix
Duplicate rows	Dedupe by key, keep the newest
Dates stored as text	Convert to real dates
Amounts with symbols	Strip to numeric
Missing values	Pick a policy: zero / drop / flag

Always ask how many rows were removed. Silent 30% data loss invalidates the entire analysis.

4. Generate charts

Specify type and dimensions:

Produce three charts:

1. Revenue by region, bar, descending
2. Monthly trend, line, 12 months
3. Category share, pie Every chart needs a title and axis labels, with consistent colors.

5. Validate every formula

If it wrote formulas, validate them:

Check all formulas in the workbook and list every cell that errors.

This catches misplaced references, divide-by-zero, and type mismatches. Writing formulas without validating is burying a landmine.

Merging many files

The classic “combine 40 monthly reports” task:

Merge all xlsx files in ./reports/ into one master sheet:

• Append data into a single sheet
• Add a “source_file” column recording provenance
• Align differing column names; show me any that can’t be aligned
• Verify total rows equals the sum of the parts

The source column is essential — when one batch looks wrong, you can trace it.

Make it explain the numbers

After the analysis:

How was this calculated? Which columns and what definition?

If it can’t explain its own methodology, the conclusion is suspect. This catches results that look plausible but are computed wrong.

Gotchas

Large files are slow Hundreds of thousands of rows will crawl. Sample or aggregate first.

Encoding issues When exporting CSV with non-ASCII content, use UTF-8 with BOM, or Excel renders mojibake.

Date formats Regional formats (2026/09/08 vs 09/08/2026) parse incorrectly. Specify the format explicitly during cleaning.

Next

The AI Long-Form Writing Pipeline.