working-with-spreadsheets_skill

Overview

This skill creates, edits, and validates Excel (.xlsx) workbooks following financial modeling standards. It emphasizes using Excel formulas (not hardcoded values), consistent color-coding for inputs and links, and standardized number formats. The skill also includes guidance for reading, writing, and recalculating formulas safely for analysis-ready models.

How this skill works

The skill uses openpyxl for writing formulas, formatting cells, and preserving workbook structure, and recommends pandas for data analysis and large-data I/O. It enforces rules like inserting Excel formulas rather than computed Python results, applying color/number-format standards, and adding cross-sheet references correctly. Recalculation and formula checking are handled by an external recalculation step (e.g., LibreOffice or a recalc script) that returns a JSON error summary.

When to use it

• Creating financial models, forecasts, and valuation workbooks
• Editing existing .xlsx files while preserving formulas and links
• Preparing formula-heavy reports for stakeholders with strict formatting rules
• Converting pandas outputs into production-grade Excel models
• Validating spreadsheets for formula errors before publication

Best practices

• Always write formulas into cells (e.g., '=SUM(B2:B9)') instead of writing precomputed values from Python
• Use the color-coding convention: blue for inputs, black for formulas, green for intra-workbook links, red for external links, yellow fill for key assumptions
• Apply consistent number formats: currency with thousands separator, percent formats, valuation multiples, and display zeros as a dash where appropriate
• Preserve formulas by loading workbooks with openpyxl (data_only=False) and only use data_only when you explicitly need calculated values
• Run an automated recalculation/check step after building the file and fix any #REF!, #DIV/0!, #VALUE!, or #NAME? issues

Example use cases

• Build a 3-statement financial model with clearly separated inputs, calculations, and outputs
• Convert a pandas DataFrame into a formatted model sheet with live formulas for future scenario testing
• Insert cross-sheet summaries and color-code inputs to make the workbook user-editable and audit-friendly
• Open and patch an existing client workbook, inserting rows/columns and preserving references and formulas
• Run a validation pass that reports total formulas, error counts, and locations before delivery