Spreadsheet Analysis

Spreadsheet Analysis

Preserve the source workbook, distinguish stored values from formulas, and support every conclusion with reproducible checks.

Inputs

Collect or state:

• Source file(s), business question, intended audience, output format, and acceptance criteria.
• Relevant sheets/ranges, keys, units, currencies, date/time zones, accounting signs, and reporting period.
• Whether formulas, formatting, comments, hidden content, macros, external links, pivots, charts, and protection must remain intact.
• Authoritative totals or source systems for reconciliation.
• Confidentiality constraints and whether local-only processing is required.

Do not guess the meaning of unlabeled fields or ambiguous blanks, zeros, percentages, dates, and IDs. Record assumptions.

Output contract

Return:

1. Source path, hash, format, workbook/sheet inventory, and analysis scope.
2. Findings with metric definition, formula or method, filters, units, period, denominator, and evidence location.
3. Data-quality and formula issues separated from business conclusions.
4. A new output workbook/data file when edits are requested; never silently overwrite the source.
5. Reconciliation, recalculation, re-open, and visual inspection results with limitations.

Clearly label calculated, estimated, cached, missing, and externally sourced values. Do not claim a workbook was recalculated if only cached formula results were read.

Workflow

1. Preserve and inventory

• Resolve exact paths, calculate source hashes, and work from a copy or new output path.
• Do not open untrusted workbooks with macros enabled or refresh external connections.
• Run the bundled read-only profiler from this skill directory for CSV, TSV, XLSX, or XLSM inventory:

python3 scripts/profile_table.py /path/to/data.csv --pretty
python3 scripts/profile_table.py /path/to/workbook.xlsx --pretty
python3 scripts/profile_table.py /path/to/data.csv --output /path/to/profile.json --pretty

The script uses the Python standard library, streams logical CSV/TSV records (including quoted multiline fields), applies byte/row/field/member limits, does not calculate formulas, and does not modify the file. It does not emit profiled data-row cell values, but it does emit headers, sheet names, counts, numeric ranges, and structural metadata. With --output, it refuses input aliases and non-regular destinations, then atomically creates or replaces the report via a sibling temporary file. A partial, row_limit_reached, or skipped_* status means the inventory is incomplete; review the stated limit instead of treating the profile as a verdict.

2. Choose an available toolchain

Read tool-routing.md. Inventory the installed spreadsheet application, Python/JavaScript libraries, and converters before selecting one. Prefer a workbook-aware engine when formulas, styles, charts, pivots, macros, or named ranges matter. Prefer a dataframe/query engine for tabular analysis after the workbook semantics are understood.

Do not install dependencies, upload data, or convert formats without permission. Conversion can lose formulas, formats, macros, dates, comments, charts, or multiple sheets.

3. Open or render a baseline

Open the original in a trusted spreadsheet application when available. For an untrusted original, require the controls defined in step 8: protected/read-only input, macros/VBA/events/add-ins/DDE disabled, no link/query refresh, and no network. If those controls cannot be verified, retain the static profiler output as partial and stop before application open or recalculation. Capture a visual baseline for every relevant sheet and any dashboard, print layout, chart, or unusual formatting. Inventory:

• Visible, hidden, and very-hidden sheets; used ranges; headers; merged cells; tables; filters; freezes; and defined names.
• Formulas, cached values, errors, array/spill formulas, circular references, and calculation mode.
• External links, data connections, queries, macros, validation rules, comments, and protection.
• Units, number formats, date systems, locale assumptions, and blank/null conventions.

Treat hidden rows/sheets as in scope for integrity and security, not automatically as analysis data.

4. Define the analytical grain

Identify what one row represents, the primary key, allowed duplicates, dimensions, measures, period boundaries, and join cardinality. Build a data dictionary for ambiguous columns. Read analysis-checklist.md for profiling and reconciliation checks.

Create a normalized analysis copy when necessary; retain source row identifiers so every result can be traced back.

5. Validate and reconcile before interpreting

Check row counts, duplicate keys, missingness, type drift, invalid categories, date gaps, outliers, formula inconsistencies, hidden exclusions, and join multiplication. Reconcile key totals to an authoritative control or explain why no control exists.

Inspect formulas as formulas and values separately. Detect hard-coded constants inside formula regions, relative-reference drift, mixed signs, inconsistent ranges, and error suppression. Never replace a formula with a value silently.

6. Analyze with explicit definitions

State the metric definition before calculating. Use precise filters, denominators, period logic, units, and rounding. Preserve full precision in calculations and round only for presentation. Separate descriptive results from forecasts or causal claims. For forecasts, document horizon, method, training window, seasonality, uncertainty, and backtest performance.

7. Create outputs minimally

Write only requested changes to a new workbook or table. Preserve formats, formulas, names, hidden state, validations, macros, and charts unless intentionally changed. Use formulas when recipients need an auditable model; use fixed values only when requested and label them.

Use analysis-report-template.md for a standalone evidence record.