Excel Data Analyzer

Overview

Analyze Excel files to identify data structure, quality issues, format inconsistencies, and statistical patterns. Generate comprehensive markdown reports with actionable insights for data cleaning and improvement.

Quick Start

Analyze any Excel file with a single command:

cd /path/to/skill/scripts
bun install  # First time only
bun run analyze_excel.ts /path/to/data.xlsx

Output: Markdown report (data_analysis.md) with complete analysis.

Core Capabilities

1. Data Structure Detection

Automatically identifies:

Column names and data types (integer, float, string, date, email, boolean, mixed)
Row and column counts per sheet
Distinct value counts
Sample values for quick inspection

2. Data Quality Analysis

Detects quality issues:

Missing values: Percentage and count of nulls per column
High null columns: Flags columns with >50% missing data
Mixed data types: Identifies columns with inconsistent types
Format issues: Detects leading/trailing whitespace, inconsistent casing, numeric strings

3. Statistical Summaries

Generates statistics for numeric columns:

Min, max, mean, median, standard deviation
Outlier detection: Values beyond 3 standard deviations
Value distribution: Top 10 most frequent values with counts

For text columns:

Min/max/average length
Value frequency distribution

4. Quality Scoring

Assigns quality scores (0-100) based on:

Missing headers: -10 points
High null percentage columns: -15 points
Format inconsistencies: -10 points
Duplicate column names: -15 points

5. Multi-Sheet Support

Analyzes all sheets in workbook:

Per-sheet quality scores
Sheet-by-sheet column analysis
Overall workbook quality score

Usage

Basic Analysis

bun run analyze_excel.ts data.xlsx

Generates: data_analysis.md

Custom Output Path

bun run analyze_excel.ts data.xlsx --output reports/audit.md

First-Time Setup

Before running analysis scripts:

cd /path/to/excel-data-analyzer/scripts
bun install

This installs required dependencies (xlsx library).

Workflow

When a user provides an Excel file for analysis:

Run the analysis script on the provided file
Read the generated report to understand findings
Summarize key issues for the user:
- Overall quality score
- Most critical issues (missing values, format problems)
- Columns requiring attention
Provide recommendations based on analysis:
- Which columns to investigate
- Suggested cleaning strategies
- Priority of fixes (high/medium/low)

Report Structure

Generated markdown reports include:

Executive Summary

File metadata (name, size, sheets)
Overall quality score
High-level findings

Per-Sheet Analysis

Dimensions (rows × columns)
Quality score
Detected issues list
Column analysis table (type, distinct values, missing %, issues)

Detailed Column Information

For each column:

Data type classification
Missing value statistics
Sample values
Format issues (if any)
Statistical summaries (numeric columns)
Value distributions

Common Data Issues

High Priority Issues

Mixed data types:

Column contains numbers, strings, and dates
Prevents proper analysis
Example: 123, "abc", 2023-01-15

High missing percentage (>50%):

Column has insufficient data
Consider dropping or imputing

Duplicate column names:

Creates ambiguity in analysis
Requires renaming

Medium Priority Issues

Numeric strings:

Numbers stored as text: "123" instead of 123
Prevents calculations

Format inconsistencies:

Leading/trailing whitespace: " value "
Inconsistent casing: "john", "JOHN", "John"
Mixed date formats: "2023-01-15", "01/15/2023"

Outliers:

Values beyond 3 standard deviations
May indicate errors or special cases
Requires investigation

Low Priority Issues

Missing headers:

Empty column names
Generates systematic names (Column_1, Column_2)

Text length variations:

Wide range in string lengths
May indicate data entry inconsistencies

Advanced Patterns

For detailed information on data quality patterns and detection methods, see:

references/analysis-patterns.md - Comprehensive guide covering:

Data type issues (mixed types, numeric strings, date formats)
Missing data patterns (high missing %, sparse data, placeholders)
Format inconsistencies (whitespace, casing, delimiters)
Statistical anomalies (outliers, skewed distributions)
Structural issues (duplicate names, empty rows/columns)
Domain-specific patterns (emails, phone numbers, dates)
Encoding issues (character encoding, Unicode)

Consult this reference when encountering unusual patterns or needing deeper analysis strategies.

Output Interpretation

Quality Score Ranges

90-100: Excellent - minimal issues
70-89: Good - minor format issues
50-69: Fair - significant quality concerns
Below 50: Poor - major data problems

Prioritizing Fixes

First: Address structural issues (duplicate columns, missing headers)
Second: Fix high missing value columns (>50%)
Third: Resolve mixed data types
Fourth: Clean format inconsistencies
Fifth: Investigate outliers

Performance

Optimized for large files:

Bun runtime: Fast JavaScript execution
Streaming support: Memory-efficient for large datasets
xlsx library: Industry-standard Excel parsing

Typical performance:

Small files (<1MB): <1 second
Medium files (1-100MB): 1-10 seconds
Large files (>100MB): 10-60 seconds

Limitations

Only generates analysis reports (does not perform data cleaning)
Text-based analysis (does not interpret business context)
Statistical methods assume numeric data for quantitative analysis
Outlier detection uses simple 3-sigma rule (not robust methods)

Resources

scripts/

analyze_excel.ts - Main analysis script (Bun/TypeScript)

Parses Excel files using xlsx library
Detects data types and quality issues
Generates statistical summaries
Produces markdown reports

package.json - Bun dependencies

xlsx: Excel file parsing

references/

analysis-patterns.md - Comprehensive guide to data quality patterns

Detailed detection methods
Impact assessments
Recommendations for each issue type

assets/

report-template.md - Markdown report template structure

Shows expected output format
Reference for understanding report sections