Pro XLSX

A professional-grade skill for creating Excel workbooks with zero formula errors, proper number formatting, validated data structures, and presentation-ready layouts. Focused on financial models, reports, and data-driven spreadsheets.

When to Use This Skill

Choose this skill when:

• Building financial models with complex formula chains
• Creating reports that must be error-free (no #REF!, #DIV/0!)
• Generating Excel files for executive audiences with polished formatting
• Implementing data validation, dropdowns, and protected ranges
• Building dashboards with charts, conditional formatting, and pivot summaries

Consider alternatives when:

• Quick data export to CSV → use pandas to_csv()
• Simple spreadsheet creation → use a basic spreadsheet skill
• Web-based data visualization → use a charting library
• Working with Google Sheets → use the Google Sheets API

Quick Start

Copy
import openpyxl
from openpyxl.utils import get_column_letter

def create_financial_model(output_path: str, data: dict):
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = 'Financial Model'

    # Styles
    currency_fmt = '#,##0.00'
    pct_fmt = '0.0%'
    header_font = Font(bold=True, size=11, color='FFFFFF')
    header_fill = PatternFill('solid', fgColor='2F5496')

    # Never use string concatenation for formulas
    # Always use cell references to prevent #REF! errors
    for row in range(2, len(data['revenue']) + 2):
        ws.cell(row=row, column=4).value = f'=B{row}-C{row}'  # Profit
        ws.cell(row=row, column=4).number_format = currency_fmt
        # Safe division with IFERROR
        ws.cell(row=row, column=5).value = f'=IFERROR(D{row}/B{row},0)'
        ws.cell(row=row, column=5).number_format = pct_fmt

    wb.save(output_path)

Core Concepts

Excel Quality Checklist

Check	Requirement	How
No #REF! errors	All cell references valid	Validate range references
No #DIV/0! errors	Division by zero handled	Wrap with IFERROR()
No #VALUE! errors	Type mismatches handled	Validate input types
Number formatting	Currencies, percentages, dates	Apply explicit formats
Data validation	Constrained input ranges	Add dropdown lists
Print layout	Headers repeat, margins set	Configure page setup
Protection	Lock formula cells	Protect sheet, unlock input cells

Safe Formula Patterns

Copy
# ALWAYS use IFERROR for division formulas
safe_margin = f'=IFERROR(D{row}/B{row},0)'

# ALWAYS use absolute references for constants
tax_rate = f'=B{row}*$B$1'  # B1 is fixed tax rate cell

# ALWAYS validate VLOOKUP with IFERROR
lookup = f'=IFERROR(VLOOKUP(A{row},Data!A:C,3,FALSE),"Not Found")'

# Use SUMPRODUCT for conditional sums (more robust than SUMIFS)
weighted = '=SUMPRODUCT(B2:B100,C2:C100)/SUM(C2:C100)'

# Use INDEX/MATCH instead of VLOOKUP for flexibility
flexible_lookup = f'=INDEX(Data!C:C,MATCH(A{row},Data!A:A,0))'

Data Validation and Protection

Copy
from openpyxl.worksheet.datavalidation import DataValidation

# Dropdown validation
dv = DataValidation(
    type='list',
    formula1='"Active,Inactive,Pending"',
    allow_blank=True,
    showErrorMessage=True,
    errorTitle='Invalid Status',
    error='Please select from the dropdown.'
)
ws.add_data_validation(dv)
dv.add('D2:D100')

# Number range validation
num_dv = DataValidation(
    type='decimal',
    operator='between',
    formula1='0',
    formula2='1000000',
    errorTitle='Invalid Amount',
    error='Amount must be between 0 and 1,000,000.'
)
ws.add_data_validation(num_dv)
num_dv.add('B2:B100')

# Sheet protection (lock formulas, allow input)
ws.protection.sheet = True
ws.protection.password = 'review'
# Unlock input cells
for row in range(2, 100):
    for col in [1, 2, 3]:  # Input columns
        ws.cell(row=row, column=col).protection = Protection(locked=False)

Configuration

Parameter	Type	Default	Description
currencyFormat	string	'#,##0.00'	Currency number format
percentFormat	string	'0.0%'	Percentage number format
errorHandling	string	'iferror'	Formula errors: iferror, ifna, or none
sheetProtection	boolean	true	Lock formula cells by default
printLayout	boolean	true	Configure print headers and margins
formulaAudit	boolean	true	Validate all formulas before save

Best Practices

1. Wrap every division formula with IFERROR — Division by zero produces #DIV/0! errors that propagate through the entire model. =IFERROR(A1/B1, 0) handles this gracefully. Never deliver a model with formula errors.

2. Use absolute references ($A$1) for fixed values — When copying formulas across rows, relative references shift and break. Anchor cells that reference fixed inputs (tax rates, exchange rates, constants) with absolute references.

3. Apply number formatting to every numeric cell — A cell showing 0.15 could be 15% or $0.15 depending on formatting. Explicit formatting (#,##0.00 for currency, 0.0% for percentage) removes ambiguity.

4. Add data validation to all input cells — Dropdown lists prevent typos in category fields. Number range validation catches data entry errors. Validation saves hours of debugging downstream formula errors.

5. Separate inputs, calculations, and outputs onto different sheets — An "Inputs" sheet with unlocked cells, a "Calculations" sheet with locked formulas, and an "Output" sheet with the presentation view. This makes models auditable and maintainable.

Common Issues

Formulas break when rows are inserted or deleted — Dynamic ranges using OFFSET or table references survive row insertions better than fixed ranges (B2:B100). Use Excel tables for automatic range expansion.

Currency formatting shows wrong number of decimals — The format #,##0 shows integers only. Use #,##0.00 for cents. For accounting format (negative in parentheses): #,##0.00;(#,##0.00).

Protected sheet prevents users from entering data — Sheet protection locks all cells by default. Explicitly unlock input cells with cell.protection = Protection(locked=False) before enabling sheet protection.