XLSX

XLSX Creation, Editing, And Validation

Use this skill when the deliverable is an .xlsx, .xlsm, .csv, or .tsv file, or when an existing spreadsheet file must be read, fixed, edited, formatted, charted, cleaned, converted, or validated.

Do not use it when the primary deliverable is a Word document, HTML report, standalone Python script, database pipeline, or Google Sheets API integration.

Required Inputs

Ask for or infer:

• Source file or source data, including sheet names and expected output path.
• Desired deliverable: new workbook, edited workbook, cleaned table, model, chart, template update, or format conversion.
• Required sheets, columns, formulas, charts, filters, pivots, tables, styling, and accessibility needs.
• Existing template conventions that must be preserved.
• Calculation expectations, formula dependencies, assumptions, and validation criteria.
• Whether formulas must remain live and recalculable.

Do not invent source data, validation output, financial assumptions, or formula results.

Core Workflow

1. Inspect the workbook or tabular data before modifying it.
2. Choose the tool: pandas for data analysis and bulk reshaping; openpyxl for formulas, formatting, charts, workbook editing, and template preservation.
3. Preserve existing templates, sheet names, formulas, styles, and conventions unless the user asks for a redesign.
4. Build or edit sheets, tables, formulas, charts, and formatting.
5. Save the workbook.
6. If formulas were added or changed, recalculate with python skills/xlsx/scripts/recalc.py <excel_file> [timeout_seconds].
7. Fix every formula error and rerun recalculation until the workbook is clean.
8. Report file path, major changes, validation results, and any limitations.

Decision-Critical Routing

• Use pandas for reading, profiling, cleaning, joining, filtering, grouping, and writing simple tabular outputs.
• Use openpyxl for preserving formulas, modifying existing workbooks, adding Excel formulas, applying formats, creating charts, and managing multiple sheets.
• Use Excel formulas for calculated workbook values instead of hardcoding Python-calculated results.
• Use load_workbook(..., data_only=False) when editing formula workbooks. Saving a workbook opened with data_only=True can replace formulas with values.
• Preserve existing workbook conventions over these defaults.
• Use formulas, tables, named sheets, units in headers, and source notes when the workbook needs to remain auditable.

Data Integrity And Formula Safety

• Deliver Excel workbooks with zero formula errors: #REF!, #DIV/0!, #VALUE!, #N/A, #NAME?, #NULL!, and #NUM!.
• Put assumptions such as growth rates, margins, multiples, and thresholds in separate cells.
• Use cell references instead of formula hardcodes.
• Verify ranges, row offsets, cross-sheet references, far-right columns, and denominator safety.
• Test formulas on a few cells before filling large regions.
• Include edge cases: zero values, negative values, blanks, and large values.
• Document hardcoded inputs with source notes where appropriate.

Styling And Accessibility

• Use a consistent professional font unless the user or template says otherwise.
• Match existing formatting exactly when updating templates.
• Use clear headers, frozen panes, filters, sensible widths, and readable number formats.
• Include units in headers such as Revenue ($mm).
• Do not rely on color alone to communicate meaning.
• For financial models, default to standard conventions unless the template overrides them:
  • Blue text: user-editable hardcoded inputs.
  • Black text: formulas and calculations.
  • Green text: links to other worksheets in the same workbook.
  • Red text: external file links.
  • Yellow fill: key assumptions or cells requiring attention.

Validation Gates

• Workbook opens successfully.
• Sheet names, row counts, column counts, and key headers match expectations.
• Formulas are live where calculations are required.
• Recalculation reports zero formula errors when formulas exist.
• Template styles and formulas are preserved when editing existing files.
• Formatting is readable and consistent.
• Source notes, assumptions, and units are present where required.
• No secrets, credentials, private context, local paths, or personal identifiers are added.

Output Expectations

Return:

• Output workbook path.
• Summary of sheets, tables, formulas, charts, and formatting changed.
• Validation commands run and result summary.
• Formula error status and recalc result when applicable.
• Any assumptions, skipped validation, or user follow-ups.

Minimal Examples

Formula-first calculation:

sheet["B10"] = "=SUM(B2:B9)"
sheet["C5"] = "=(C4-C2)/C2"

Recalculate formulas:

python skills/xlsx/scripts/recalc.py output.xlsx 30

Reference Map

• Workbook workflow
• Formatting and layout
• Formulas, charts, and tables
• Validation, troubleshooting, and anti-patterns
• Examples and templates