# Excel and Spreadsheet Automation > - Use a consistent, professional font (e.g., Arial, Times New Roman) for all deliverables unless otherwise instructed by the user Parent: [Document & File Formats](https://llms-explorer.com/tree/document-file-formats/) · 26 facets · 108 facts · page: https://llms-explorer.com/tree/excel-and-spreadsheet-automation/ ## Professional Font - Use a consistent, professional font (e.g., Arial, Times New Roman) for all deliverables unless otherwise instructed by the user — 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?) — 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 — 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 — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#preserve-existing-templates-when-updating-templates` - Existing template conventions ALWAYS override these guidelines — 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 — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#color-coding-standards` - #### Industry-Standard Color Conventions — 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 — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#color-coding-standards` - Black text (RGB: 0,0,0): ALL formulas and calculations — 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 — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#color-coding-standards` - Red text (RGB: 255,0,0): External links to other files — 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 — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#color-coding-standards` ## Number Formatting Standards - #### Required Format Rules — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#number-formatting-standards` - Years: Format as text strings (e.g., "2024" not "2,024") — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#number-formatting-standards` - Currency: Use $#,##0 format; ALWAYS specify units in headers ("Revenue ($mm)") — 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);-") — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#number-formatting-standards` - Percentages: Default to 0.0% format (one decimal) — 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) — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#number-formatting-standards` - Negative numbers: Use parentheses (123) not minus -123 — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#number-formatting-standards` ## Formula Construction Rules - #### Assumptions Placement — 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 — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-construction-rules` - Use cell references instead of hardcoded values in formulas — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-construction-rules` - Example: Use =B5(1+$B$6) instead of =B51.05 — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-construction-rules` - #### Formula Error Prevention — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-construction-rules` - Verify all cell references are correct — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-construction-rules` - Check for off-by-one errors in ranges — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-construction-rules` - Ensure consistent formulas across all projection periods — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-construction-rules` - Test with edge cases (zero values, negative numbers) — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-construction-rules` - Verify no unintended circular references — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-construction-rules` - #### Documentation Requirements for Hardcodes — 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]" — 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]" — 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]" — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-construction-rules` - "Source: Bloomberg Terminal, 8/15/2025, AAPL US Equity" — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-construction-rules` - "Source: FactSet, 8/20/2025, Consensus Estimates Screen" — 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. — 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) — 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: — 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. — 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. — 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 — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-workflow` - Create/Load: Create new workbook or load existing file — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-workflow` - Modify: Add/edit data, formulas, and formatting — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-workflow` - Save: Write to file — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-workflow` - Recalculate formulas (MANDATORY IF USING FORMULAS): Use the scripts/recalc.py script — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-workflow` - Verify and fix any errors: — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-workflow` - The script returns JSON with error details — 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 — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-workflow` - Fix the identified errors and recalculate again — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-workflow` - Common errors to fix: — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-workflow` - #REF!: Invalid cell references — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-workflow` - #DIV/0!: Division by zero — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-workflow` - #VALUE!: Wrong data type in formula — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-workflow` - #NAME?: Unrecognized formula name — 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: — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#recalculating-formulas` - Automatically sets up LibreOffice macro on first run — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#recalculating-formulas` - Recalculates all formulas in all sheets — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#recalculating-formulas` - Scans ALL cells for Excel errors (#REF!, #DIV/0!, etc.) — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#recalculating-formulas` - Returns JSON with detailed error locations and counts — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#recalculating-formulas` - Works on both Linux and macOS — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#recalculating-formulas` ## Formula Verification Checklist - Quick checks to ensure formulas work correctly: — 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 — 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) — 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) — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#essential-verification` ## Common Pitfalls - [ ] NaN handling: Check for null values with pd.notna() — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-pitfalls` - [ ] Far-right columns: FY data often in columns 50+ — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-pitfalls` - [ ] Multiple matches: Search all occurrences, not just first — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-pitfalls` - [ ] Division by zero: Check denominators before using / in formulas (#DIV/0!) — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-pitfalls` - [ ] Wrong references: Verify all cell references point to intended cells (#REF!) — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#common-pitfalls` - [ ] Cross-sheet references: Use correct format (Sheet1!A1) for linking sheets — 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 — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-testing-strategy` - [ ] Verify dependencies: Check all cells referenced in formulas exist — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#formula-testing-strategy` - [ ] Test edge cases: Include zero, negative, and very large values — 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: — 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 — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#library-selection` - openpyxl: Best for complex formatting, formulas, and Excel-specific features — 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) — 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) — 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 — 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 — 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 — 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}) — 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']) — 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']) — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#working-with-pandas` ## Code Style Guidelines - IMPORTANT: When generating Python code for Excel operations: — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#code-style-guidelines` - Write minimal, concise Python code without unnecessary comments — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#code-style-guidelines` - Avoid verbose variable names and redundant operations — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#code-style-guidelines` - Avoid unnecessary print statements — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#code-style-guidelines` - For Excel files themselves: — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#code-style-guidelines` - Add comments to cells with complex formulas or important assumptions — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#code-style-guidelines` - Document data sources for hardcoded values — source: `https://llms-explorer.com/sources/mdb-context-hub/xlsx/#code-style-guidelines` - Include notes for key calculations and model sections — 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](https://llms-explorer.com/tree/excel-and-spreadsheet-automation/) *(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](https://llms-explorer.com/tree/excel-and-spreadsheet-automation/) *(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](https://llms-explorer.com/tree/excel-and-spreadsheet-automation/) *(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](https://llms-explorer.com/tree/excel-and-spreadsheet-automation/) *(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](https://llms-explorer.com/tree/excel-and-spreadsheet-automation/) *(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](https://llms-explorer.com/tree/excel-and-spreadsheet-automation/) *(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](https://llms-explorer.com/tree/excel-and-spreadsheet-automation/) *(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](https://llms-explorer.com/tree/excel-and-spreadsheet-automation/) *(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](https://llms-explorer.com/tree/excel-and-spreadsheet-automation/) *(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](https://llms-explorer.com/tree/excel-and-spreadsheet-automation/) *(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](https://llms-explorer.com/tree/excel-and-spreadsheet-automation/) *(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](https://llms-explorer.com/tree/excel-and-spreadsheet-automation/) *(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](https://llms-explorer.com/tree/excel-and-spreadsheet-automation/) *(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](https://llms-explorer.com/tree/excel-and-spreadsheet-automation/) *(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](https://llms-explorer.com/tree/excel-and-spreadsheet-automation/) *(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](https://llms-explorer.com/tree/excel-and-spreadsheet-automation/) *(AI-suggested, synthesized from this pack's existing facts — not extracted from a source document.)*