Xlsx

Zero Formula Errors

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

Preserve Existing Templates (when updating templates)

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

Formulas Over Hardcoded Values

Every derived value must be an Excel formula, not a Python-computed constant. The spreadsheet must recalculate when inputs change.

# WRONG — value dies when inputs change
margin = (revenue - cogs) / revenue
ws["D5"] = margin

# RIGHT — formula stays live
ws["D5"] = "=(B5-C5)/B5"
ws["D5"].number_format = "0.0%"

# WRONG — snapshot of a sum
ws["F20"] = df["Amount"].sum()

# RIGHT — Excel does the aggregation
ws["F20"] = "=SUM(F2:F19)"
ws["F20"].number_format = "#,##0"

This applies to totals, ratios, growth rates, averages, ranks — anything Excel can compute. Hardcoded numbers are acceptable only for raw input data and sourced assumptions.

Financial models

Color Coding Standards

Unless otherwise stated by the user or existing template

Industry-Standard Color Conventions

• Blue text (RGB: 0,0,255): Hardcoded inputs, and numbers users will change for scenarios
• Black text (RGB: 0,0,0): ALL formulas and calculations
• Green text (RGB: 0,128,0): Links pulling from other worksheets within same workbook
• Red text (RGB: 255,0,0): External links to other files
• Yellow background (RGB: 255,255,0): Key assumptions needing attention or cells that need to be updated

Number Formatting Standards

Required Format Rules

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

Formula Construction Rules

Assumptions Placement

• Place ALL assumptions (growth rates, margins, multiples, etc.) in separate assumption cells
• Use cell references instead of hardcoded values in formulas
• Example: Use =B5*(1+$B$6) instead of =B5*1.05

Formula Error Prevention

• Verify all cell references are correct
• Check for off-by-one errors in ranges
• Ensure consistent formulas across all projection periods
• Test with edge cases (zero values, negative numbers)
• Verify no unintended circular references

Documentation Requirements for Hardcodes

• Comment or in cells beside (if end of table). Format: "Source: [System/Document], [Date], [Specific Reference], [URL if applicable]"
• Examples:
  • "Source: Company 10-K, FY2024, Page 45, Revenue Note, [SEC EDGAR URL]"
  • "Source: Company 10-Q, Q2 2025, Exhibit 99.1, [SEC EDGAR URL]"
  • "Source: Bloomberg Terminal, 8/15/2025, AAPL US Equity"
  • "Source: FactSet, 8/20/2025, Consensus Estimates Screen"

Structure & Usability

Sheet Organization

Information architecture:

• Overview sheet should stand alone — user understands the main message without opening other sheets
• Progressive disclosure: summary first, details available for those who want to dig deeper
• Consistent structure across sheets: same layout patterns, same starting positions

Layout Rules

Charts must never overlap each other or tables.

ws.column_dimensions['A'].width = 3

Standalone Text Rows

For rows with a single text cell (titles, descriptions, notes), text naturally extends into empty cells to the right. However, text is clipped if right cells contain any content (including spaces).

Common cases requiring merge:

• Titles and subtitles (usually span full content width)
• Section headers (span width of related table)
• Long bullet points or insight text
• Notes and disclaimers

from openpyxl.utils import get_column_letter

# Merge title across content width
last_col = 8  # Match table width
ws.merge_cells(f'B2:{get_column_letter(last_col)}2')
ws['B2'] = "Report Title"

# Wrapped text with manual row height
ws['B20'].alignment = Alignment(wrap_text=True)
ws.row_dimensions[20].height = 30  # Adjust based on content

Navigation

For workbooks with 3+ sheets, add a sheet index with hyperlinks on the Overview.

Internal links (cross-sheet references) — use Hyperlink class for reliability:

from openpyxl.worksheet.hyperlink import Hyperlink

cell = ws.cell(row=6, column=2, value="Revenue Data")
cell.hyperlink = Hyperlink(ref=cell.coordinate, location="'Revenue Data'!A1")
cell.font = Font(color='0000FF', underline='single')

External links (source documents):

cell.hyperlink = "https://example.com/source"
cell.font = Font(color='0000FF', underline='single')

Freeze Panes

For tables with >10 rows, freeze below the header row:

ws.freeze_panes = f'A{header_row + 1}'

Filters

For tables with >20 rows, enable auto-filter to allow users to explore data:

from openpyxl.utils import get_column_letter

# Apply filter to entire data range
ws.auto_filter.ref = f"A{header_row}:{get_column_letter(last_col)}{last_row}"

Excel Tables

For any contiguous data range with one header row + data rows, always create a formal Excel Table object instead of manual formatting. Tables provide automatic row banding, filters, structured references (e.g., =SUM(Table1[Revenue])), and auto-updating styles when rows are added or deleted. This makes manual alternating-row fills, manual auto-filter setup, and manual header styling unnecessary. Each sheet can have its own Table (use unique displayName values).

When the sheet is purely a data table, data should start at A1 — the B2 layout rule applies to dashboards/reports with titles, not raw data tables. Use openpyxl.worksheet.table.Table with TableStyleInfo to create the table.

When editing an existing file, check for Table objects (ws.tables) before writing formulas. If tables exist, use structured table references in all formulas instead of raw cell ranges. For example, use =AVERAGE(PeopleData[Salary]) instead of =AVERAGE('Sheet1'!N2:N500). For VLOOKUP, use TableName[#All] as the lookup array: =VLOOKUP(A2,PeopleData[#All],3,FALSE). Structured references auto-adjust when rows are added or removed.

Pre-sorting

Pre-sort by most meaningful dimension:

• Rankings → by value descending
• Time series → by date ascending
• Alphabetical → when no clear priority

df = df.sort_values('revenue', ascending=False)

Data Context

Every dataset needs context for the user to trust and understand it:

# Add data context in footer area
ws.cell(row=last_row + 3, column=1, value="Source: Company Annual Report 2024")
ws.cell(row=last_row + 4, column=1, value=f"Generated: {datetime.now().strftime('%Y-%m-%d')}")

Content Completeness

Number Formatting

Critical: Formula cells need number_format too — they display raw precision unless explicitly formatted.

# WRONG: Formula cell without number_format
ws['C10'] = '=C7-C9'  # Displays 14.123456789

# CORRECT: Always set number_format for formula cells
ws['C10'] = '=C7-C9'
ws['C10'].number_format = '#,##0.0'  # Displays 14.1

Apply consistent formatting to entire columns (both values and formulas):

Alignment

# Numbers right-aligned
cell.alignment = Alignment(horizontal='right', vertical='center')

# Text with padding
cell.alignment = Alignment(horizontal='left', vertical='center', indent=1)

Column Width

Calculate width based on content. Only consider data cells, not titles or notes:

def set_column_width(ws, col, min_width=12, max_width=50, padding=2):
    max_len = 0
    for row in ws.iter_rows(min_col=col, max_col=col):
        for cell in row:
            if cell.value:
                max_len = max(max_len, len(str(cell.value)))

    width = min(max(max_len + padding, min_width), max_width)
    ws.column_dimensions[get_column_letter(col)].width = width

Guidelines:

Row Height

Set row heights explicitly for consistency (openpyxl doesn't auto-adjust):

ws.row_dimensions[1].height = 30   # Title row
ws.row_dimensions[2].height = 20   # Subtitle row
ws.row_dimensions[3].height = 25   # Header row
# Data rows: default 15-18 is usually fine

Data Visualization

Data Bars — compare magnitude within a column without leaving the cell:

from openpyxl.formatting.rule import DataBarRule

# Blue data bars (default Excel blue)
rule = DataBarRule(
    start_type='min',
    end_type='max',
    color='4472C4'  # Excel default blue
)
ws.conditional_formatting.add('C5:C50', rule)

Color Scale — heatmap effect for matrices and ranges:

from openpyxl.formatting.rule import ColorScaleRule

# White to blue gradient
rule = ColorScaleRule(
    start_type='min', start_color='FFFFFF',
    end_type='max', end_color='4472C4'
)
ws.conditional_formatting.add('D5:H20', rule)

# Three-color scale (low-mid-high)
rule = ColorScaleRule(
    start_type='min', start_color='F8696B',     # Red
    mid_type='percentile', mid_value=50, mid_color='FFEB84',  # Yellow
    end_type='max', end_color='63BE7B'          # Green
)

When to use:

Conditional Formatting Rules

When a user asks to "highlight", "color", or "conditionally format" cells based on value thresholds, always use Excel conditional formatting rules (CellIsRule, FormulaRule from openpyxl.formatting.rule) instead of looping through cells and setting PatternFill directly. Static fills look the same visually but are not real conditional formatting — they don't update when values change, don't appear in Excel's conditional formatting manager, and can't be edited by the user.

Charts

Place charts below tables with a 2-row gap, left-aligned with content:

from openpyxl.chart import BarChart, LineChart, Reference

# Create chart
chart = BarChart()
chart.title = "Revenue by Region"
chart.style = 10  # Built-in style

# Set data and categories
data = Reference(ws, min_col=2, min_row=header_row, max_row=last_row)
cats = Reference(ws, min_col=1, min_row=header_row + 1, max_row=last_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

# Size and position
chart.width = 15  # inches
chart.height = 7.5
ws.add_chart(chart, f"A{last_row + 3}")  # 2 rows below data

Chart type selection:

Preventing overlap: Chart width and height are in centimeters, not rows. To place content after a chart without overlap:

from math import ceil

# ~2 rows per cm of chart height (at default ~15pt row height)
rows_for_chart = ceil(chart.height * 2)
next_content_row = chart_row + rows_for_chart + 2  # 2-row gap

Comparison Columns

For analytical reports, add calculated columns that surface insights:

# Add YoY growth column
for row in range(data_start, data_end + 1):
    current = ws.cell(row=row, column=current_year_col).coordinate
    prior = ws.cell(row=row, column=prior_year_col).coordinate
    growth_cell = ws.cell(row=row, column=growth_col)
    growth_cell.value = f"=({current}-{prior})/{prior}"
    growth_cell.number_format = '0.0%'

Scripts

LibreOffice is pre-installed. Both scripts configure it automatically on first run.

Use pandas for data analysis and bulk operations. Use openpyxl for formulas, formatting, and Excel-specific features. After saving, always recalculate:

Recalculating Formulas

openpyxl writes formulas as strings but does not evaluate them. The skills/GENERAL-KNOWLEDGE-WORKER/xlsx/scripts/recalc.py script drives LibreOffice headless to recalculate all formulas and then scans every cell for Excel errors.

python skills/GENERAL-KNOWLEDGE-WORKER/xlsx/scripts/recalc.py <excel_file> [timeout_seconds]

On success:

{"status": "success", "total_errors": 0, "total_formulas": 42, "error_summary": {}}

When errors remain:

{
  "status": "errors_found",
  "total_errors": 2,
  "total_formulas": 42,
  "error_summary": {
    "#REF!": {"count": 2, "locations": ["Sheet1!B5", "Sheet1!C10"]}
  }
}

If errors_found, fix the referenced cells and re-run. Common errors: #REF! (bad cell reference), #DIV/0! (division by zero), #VALUE! (wrong type), #NAME? (unknown function).

Pivot Tables

openpyxl cannot create pivot tables. Use skills/GENERAL-KNOWLEDGE-WORKER/xlsx/scripts/pivot_table.py, which creates real, interactive Excel pivot tables via LibreOffice's DataPilot engine.

# Create a pivot table
python skills/GENERAL-KNOWLEDGE-WORKER/xlsx/scripts/pivot_table.py create output.xlsx '{
    "source_sheet": "Data",
    "target_sheet": "Revenue Pivot",
    "pivot_name": "RevPivot",
    "row_fields": ["Region", "Product"],
    "column_fields": ["Quarter"],
    "data_fields": [{"name": "Revenue", "function": "SUM"}]
}'

# Delete a pivot table
python skills/GENERAL-KNOWLEDGE-WORKER/xlsx/scripts/pivot_table.py delete output.xlsx "Data" "RevPivot"

Config fields:

• source_sheet: Sheet containing the source data (must have headers in row 1)
• target_sheet: Sheet where the pivot table will be created (created automatically if it doesn't exist)
• pivot_name: Unique name for the pivot table
• source_range: Optional, e.g. "A1:E100". Defaults to the full used area of the source sheet
• row_fields: Fields to use as row labels
• column_fields: Fields to use as column labels
• data_fields: Fields to aggregate, each with name and function (SUM, COUNT, AVERAGE, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR, VARP). Each field name can only appear once — for multiple aggregations on the same column, create separate pivot tables
• page_fields: Optional filter fields

The resulting pivot tables are fully interactive in Excel — users can drag fields, filter, and refresh.

To edit a pivot table, recreate it with the new configuration using a new pivot_name.

Workflow with pivot tables:

1. Create/modify the spreadsheet with openpyxl (data, formulas, formatting)
2. Save the file
3. Run pivot_table.py create to add each pivot table
4. Continue modifying with openpyxl if needed — existing pivots are preserved
5. Run recalc.py to recalculate formulas

Multiple pivot tables can be added by running the script multiple times with different configs.

Formula Verification Checklist

Quick checks to ensure formulas work correctly:

Essential Verification

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

Common Pitfalls

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

Formula Testing Strategy

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

Pitfalls

openpyxl

• data_only=True destroys formulas on save — opening with data_only=True replaces formula strings with cached values. Never save a workbook opened this way; use it only for reading computed results.
• Cell indices are 1-based — row=1, column=1 is cell A1. DataFrame row 5 = Excel row 6.
• Formulas are stored as strings, not evaluated — openpyxl does not compute formula results. Always run recalc.py after writing formulas.
• Large files — use read_only=True for reading or write_only=True for writing to avoid loading the entire file into memory.

pandas

• Type inference — specify dtypes to avoid silent coercion: pd.read_excel('file.xlsx', dtype={'id': str})
• Large files — read only needed columns: pd.read_excel('file.xlsx', usecols=['A', 'C', 'E'])
• Dates — parse explicitly: pd.read_excel('file.xlsx', parse_dates=['date_column'])