Excel and Spreadsheet Automation

researched 2026-05-25· 0 sources · 9 concepts · skill xlsx

- Use a consistent, professional font (e.g., Arial, Times New Roman) for all deliverables unless otherwise instructed by the user

Professional Font

Use a consistent, professional font (e.g., Arial, Times New Roman) for all deliverables unless otherwise instructed by the user [source]

Zero Formula Errors

Every Excel model MUST be delivered with ZERO formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?) [source]

Preserve Existing Templates (when updating templates)

Study and EXACTLY match existing format, style, and conventions when modifying files [source]
Never impose standardized formatting on files with established patterns [source]
Existing template conventions ALWAYS override these guidelines [source]

Color Coding Standards

Unless otherwise stated by the user or existing template [source]
#### Industry-Standard Color Conventions [source]
Blue text (RGB: 0,0,255): Hardcoded inputs, and numbers users will change for scenarios [source]
Black text (RGB: 0,0,0): ALL formulas and calculations [source]
Green text (RGB: 0,128,0): Links pulling from other worksheets within same workbook [source]
Red text (RGB: 255,0,0): External links to other files [source]
Yellow background (RGB: 255,255,0): Key assumptions needing attention or cells that need to be updated [source]

Number Formatting Standards

#### Required Format Rules [source]
Years: Format as text strings (e.g., "2024" not "2,024") [source]
Currency: Use $#,##0 format; ALWAYS specify units in headers ("Revenue ($mm)") [source]
Zeros: Use number formatting to make all zeros "-", including percentages (e.g., "$#,##0;($#,##0);-") [source]
Percentages: Default to 0.0% format (one decimal) [source]
Multiples: Format as 0.0x for valuation multiples (EV/EBITDA, P/E) [source]
Negative numbers: Use parentheses (123) not minus -123 [source]

Formula Construction Rules

#### Assumptions Placement [source]
Place ALL assumptions (growth rates, margins, multiples, etc.) in separate assumption cells [source]
Use cell references instead of hardcoded values in formulas [source]
Example: Use =B5(1+$B$6) instead of =B51.05 [source]
#### Formula Error Prevention [source]
Verify all cell references are correct [source]
Check for off-by-one errors in ranges [source]
Ensure consistent formulas across all projection periods [source]
Test with edge cases (zero values, negative numbers) [source]
Verify no unintended circular references [source]
#### Documentation Requirements for Hardcodes [source]
Comment or in cells beside (if end of table). Format: "Source: [System/Document], [Date], [Specific Reference], [URL if applicable]" [source]
Examples: [source]
"Source: Company 10-K, FY2024, Page 45, Revenue Note, [SEC EDGAR URL]" [source]
"Source: Company 10-Q, Q2 2025, Exhibit 99.1, [SEC EDGAR URL]" [source]
"Source: Bloomberg Terminal, 8/15/2025, AAPL US Equity" [source]
"Source: FactSet, 8/20/2025, Consensus Estimates Screen" [source]

Overview

A user may ask you to create, edit, or analyze the contents of an .xlsx file. You have different tools and workflows available for different tasks. [source]

Important Requirements

LibreOffice Required for Formula Recalculation: You can assume LibreOffice is installed for recalculating formula values using the scripts/recalc.py script. The script automatically configures LibreOffice on first run, including in sandboxed environments where Unix sockets are restricted (handled by scripts/office/soffice.py) [source]

Data analysis with pandas

For data analysis, visualization, and basic operations, use pandas which provides powerful data manipulation capabilities: [source]

CRITICAL: Use Formulas, Not Hardcoded Values

Always use Excel formulas instead of calculating values in Python and hardcoding them. This ensures the spreadsheet remains dynamic and updateable. [source]

✅ CORRECT - Using Excel Formulas

This applies to ALL calculations - totals, percentages, ratios, differences, etc. The spreadsheet should be able to recalculate when source data changes. [source]

Common Workflow

Choose tool: pandas for data, openpyxl for formulas/formatting [source]
Create/Load: Create new workbook or load existing file [source]
Modify: Add/edit data, formulas, and formatting [source]
Save: Write to file [source]
Recalculate formulas (MANDATORY IF USING FORMULAS): Use the scripts/recalc.py script [source]
Verify and fix any errors: [source]
The script returns JSON with error details [source]
If status is errors_found, check error_summary for specific error types and locations [source]
Fix the identified errors and recalculate again [source]
Common errors to fix: [source]
#REF!: Invalid cell references [source]
#DIV/0!: Division by zero [source]
#VALUE!: Wrong data type in formula [source]
#NAME?: Unrecognized formula name [source]

Recalculating formulas

Excel files created or modified by openpyxl contain formulas as strings but not calculated values. Use the provided scripts/recalc.py script to recalculate formulas: [source]
Automatically sets up LibreOffice macro on first run [source]
Recalculates all formulas in all sheets [source]
Scans ALL cells for Excel errors (#REF!, #DIV/0!, etc.) [source]
Returns JSON with detailed error locations and counts [source]
Works on both Linux and macOS [source]

Formula Verification Checklist

Quick checks to ensure formulas work correctly: [source]

Essential Verification

[ ] Test 2-3 sample references: Verify they pull correct values before building full model [source]
[ ] Column mapping: Confirm Excel columns match (e.g., column 64 = BL, not BK) [source]
[ ] Row offset: Remember Excel rows are 1-indexed (DataFrame row 5 = Excel row 6) [source]

Common Pitfalls

[ ] NaN handling: Check for null values with pd.notna() [source]
[ ] Far-right columns: FY data often in columns 50+ [source]
[ ] Multiple matches: Search all occurrences, not just first [source]
[ ] Division by zero: Check denominators before using / in formulas (#DIV/0!) [source]
[ ] Wrong references: Verify all cell references point to intended cells (#REF!) [source]
[ ] Cross-sheet references: Use correct format (Sheet1!A1) for linking sheets [source]

Formula Testing Strategy

[ ] Start small: Test formulas on 2-3 cells before applying broadly [source]
[ ] Verify dependencies: Check all cells referenced in formulas exist [source]
[ ] Test edge cases: Include zero, negative, and very large values [source]

Interpreting scripts/recalc.py Output

The script returns JSON with error details: [source]

Library Selection

pandas: Best for data analysis, bulk operations, and simple data export [source]
openpyxl: Best for complex formatting, formulas, and Excel-specific features [source]

Working with openpyxl

Cell indices are 1-based (row=1, column=1 refers to cell A1) [source]
Use data_only=True to read calculated values: load_workbook('file.xlsx', data_only=True) [source]
Warning: If opened with data_only=True and saved, formulas are replaced with values and permanently lost [source]
For large files: Use read_only=True for reading or write_only=True for writing [source]
Formulas are preserved but not evaluated - use scripts/recalc.py to update values [source]

Working with pandas

Specify data types to avoid inference issues: pd.read_excel('file.xlsx', dtype={'id': str}) [source]
For large files, read specific columns: pd.read_excel('file.xlsx', usecols=['A', 'C', 'E']) [source]
Handle dates properly: pd.read_excel('file.xlsx', parse_dates=['date_column']) [source]

Code Style Guidelines

IMPORTANT: When generating Python code for Excel operations: [source]
Write minimal, concise Python code without unnecessary comments [source]
Avoid verbose variable names and redundant operations [source]
Avoid unnecessary print statements [source]
For Excel files themselves: [source]
Add comments to cells with complex formulas or important assumptions [source]
Document data sources for hardcoded values [source]
Include notes for key calculations and model sections [source]

Children

XLSX File Creation and Editing (frontier)
Financial Model Formulas (frontier)
Tabular Data Cleaning (frontier)
CSV and TSV Conversion (frontier)

Frontier under this node: CSV and TSV Conversion, Financial Model Formulas, Tabular Data Cleaning, XLSX File Creation and Editing

← the whole tree · 3D view· how to read this page