Excel and Spreadsheet Automation

Parent: Document & File Formats · Published reference · snapshot 2026-09-08 · skill document-formats/references/xlsx.md

↓ Facts as markdown all context files

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

These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.

Professional Font

Use a consistent, professional font (e.g., Arial, Times New Roman) for all deliverables unless otherwise instructed by the user [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#professional-font]

Zero Formula Errors

Every Excel model MUST be delivered with ZERO formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?) [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#zero-formula-errors]

Preserve Existing Templates (when updating templates)

Study and EXACTLY match existing format, style, and conventions when modifying files [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#preserve-existing-templates-when-updating-templates]
Never impose standardized formatting on files with established patterns [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#preserve-existing-templates-when-updating-templates]
Existing template conventions ALWAYS override these guidelines [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#preserve-existing-templates-when-updating-templates]

Color Coding Standards

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

Number Formatting Standards

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

Formula Construction Rules

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

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. [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#overview]

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) [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#important-requirements]

Data analysis with pandas

For data analysis, visualization, and basic operations, use pandas which provides powerful data manipulation capabilities: [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#data-analysis-with-pandas]

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. [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#critical-use-formulas-not-hardcoded-values]

✅ 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. [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#-correct---using-excel-formulas]

Common Workflow

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

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: [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#recalculating-formulas]
- Automatically sets up LibreOffice macro on first run [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#recalculating-formulas]
- Recalculates all formulas in all sheets [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#recalculating-formulas]
- Scans ALL cells for Excel errors (#REF!, #DIV/0!, etc.) [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#recalculating-formulas]
- Returns JSON with detailed error locations and counts [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#recalculating-formulas]
- Works on both Linux and macOS [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#recalculating-formulas]

Formula Verification Checklist

Quick checks to ensure formulas work correctly: [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-verification-checklist]

Essential Verification

[ ] Test 2-3 sample references: Verify they pull correct values before building full model [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#essential-verification]
[ ] Column mapping: Confirm Excel columns match (e.g., column 64 = BL, not BK) [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#essential-verification]
[ ] Row offset: Remember Excel rows are 1-indexed (DataFrame row 5 = Excel row 6) [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#essential-verification]

Common Pitfalls

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

Formula Testing Strategy

[ ] Start small: Test formulas on 2-3 cells before applying broadly [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-testing-strategy]
[ ] Verify dependencies: Check all cells referenced in formulas exist [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-testing-strategy]
[ ] Test edge cases: Include zero, negative, and very large values [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-testing-strategy]

Interpreting scripts/recalc.py Output

The script returns JSON with error details: [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#interpreting-scriptsrecalcpy-output]

Library Selection

pandas: Best for data analysis, bulk operations, and simple data export [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#library-selection]
openpyxl: Best for complex formatting, formulas, and Excel-specific features [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#library-selection]

Working with openpyxl

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

Working with pandas

Specify data types to avoid inference issues: pd.read_excel('file.xlsx', dtype={'id': str}) [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#working-with-pandas]
For large files, read specific columns: pd.read_excel('file.xlsx', usecols=['A', 'C', 'E']) [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#working-with-pandas]
Handle dates properly: pd.read_excel('file.xlsx', parse_dates=['date_column']) [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#working-with-pandas]

Code Style Guidelines

IMPORTANT: When generating Python code for Excel operations: [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#code-style-guidelines]
- Write minimal, concise Python code without unnecessary comments [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#code-style-guidelines]
- Avoid verbose variable names and redundant operations [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#code-style-guidelines]
- Avoid unnecessary print statements [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#code-style-guidelines]
For Excel files themselves: [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#code-style-guidelines]
- Add comments to cells with complex formulas or important assumptions [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#code-style-guidelines]
- Document data sources for hardcoded values [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#code-style-guidelines]
- Include notes for key calculations and model sections [archived source: https://llms-explorer.com/sources/mdb-context-hub/xlsx/#code-style-guidelines]

Where this helps

Delivering a financial model or budget workbook that a client will inspect for professional conventions — blue hardcoded inputs, black formulas, consistent currency and zero formatting. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Updating an existing Excel template programmatically without breaking its established formatting conventions, where 'match the existing pattern' matters more than applying a fresh house style. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Building a workbook where formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?) must be verified at zero before delivery, not just visually spot-checked. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Automating spreadsheet generation from a data source (pandas) while keeping the output as live formulas the client can audit and adjust, rather than hardcoded computed values. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.

Project ideas

Build a pandas-to-Excel pipeline that writes source data into cells and generates the downstream calculations as real Excel formulas (via openpyxl) rather than pre-computed values. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Build a formula-error scanner that opens a workbook after recalculation and flags every cell showing #REF!, #DIV/0!, #VALUE!, #N/A, or #NAME? before the file is considered deliverable. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Build a template-preserving updater that reads an existing workbook's color-coding and number-formatting conventions and replicates them exactly when adding new rows or sheets. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Build a recalculation step using a LibreOffice-based script (scripts/recalc.py-style) that forces formula recalculation headlessly as part of a CI or delivery pipeline. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.

Antipatterns

Hardcoding a computed value into a cell instead of writing the formula, which breaks the model the moment an upstream assumption changes and the user expects a spreadsheet, not a snapshot. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Imposing a fresh color-coding or formatting standard onto an existing client template instead of studying and exactly matching its established conventions. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Mixing an assumption (a growth rate, a margin) directly into a formula instead of placing it in its own labeled assumption cell and referencing it — this is exactly the =B5*(1+$B$6) vs =B5*1.05 distinction the pack calls out. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Delivering a workbook without verifying zero formula errors — treating a visually clean-looking sheet as proof there are no #REF!/#DIV/0!/#VALUE! cells hiding off-screen. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.

Known issues

Formula recalculation via a LibreOffice-based script is a workaround for the fact that Excel formulas aren't recalculated just by writing them with a library like openpyxl — the file needs an actual recalculation pass. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
openpyxl and pandas serve different halves of the job (formula/formatting control vs. data analysis); using only one where the task needs both leads to either dead formulas or unformatted dumps. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Industry-standard color conventions (blue inputs, black formulas) are a convention, not an enforced Excel feature — nothing stops inconsistent coloring from creeping back in without an explicit check. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.
Number formatting choices (e.g., displaying zeros as '-') are cosmetic; they don't change the underlying cell value, so downstream automation reading raw values must not assume the display format reflects the stored data type. [source] — AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.

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