Document Xlsx

Document XLSX Skill — Quick Reference

This skill enables creation, editing, and analysis of Excel spreadsheets programmatically. Claude should apply these patterns when users need to generate data reports, financial models, automate Excel workflows, or process spreadsheet data.

Modern Best Practices (Jan 2026):

• Treat spreadsheets as software: clear inputs/outputs, auditability, and versioning.
• Protect data integrity: control totals, validation, and traceability to sources.
• Accessibility: labels, contrast, structure; use Excel's Accessibility Checker; meet procurement/regulatory requirements when distributing externally.
• If distributing in the EU or regulated contexts, follow applicable accessibility requirements (often aligned with EN 301 549 / WCAG).
• Ship with a review loop and an owner (avoid "mystery models").
• Security: treat untrusted input/workbooks as hostile (formula injection, external links, hidden content, macros).

---

Quick Reference

Guardrails and Caveats

• Formula calculation: libraries write formulas; Excel computes results when opened. If you need computed values server-side, calculate in code and write values (or use a dedicated formula engine).
• Pivot tables: programmatic creation is limited. Prefer pandas summaries (pivot tables as data) or Excel automation (xlwings/Office Scripts/VBA) if you truly need native pivots.
• Macros: openpyxl can preserve existing VBA (keep_vba=True) but does not author macros; never generate or execute macros from untrusted input.
• Spreadsheet injection: never put untrusted strings into formula fields; write them as text values and validate/sanitize user-provided data used in exports.

---

Core Operations

Create Spreadsheet (Node.js - exceljs)

import ExcelJS from 'exceljs';

const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('Sales Report');

// Headers with styling
sheet.columns = [
  { header: 'Product', key: 'product', width: 20 },
  { header: 'Quantity', key: 'qty', width: 12 },
  { header: 'Price', key: 'price', width: 12 },
  { header: 'Total', key: 'total', width: 15 },
];

// Style header row
sheet.getRow(1).font = { bold: true };
sheet.getRow(1).fill = {
  type: 'pattern',
  pattern: 'solid',
  fgColor: { argb: 'FF4472C4' }
};

// Add data
const data = [
  { product: 'Widget A', qty: 100, price: 10 },
  { product: 'Widget B', qty: 50, price: 25 },
];

data.forEach((item, index) => {
  sheet.addRow({
    product: item.product,
    qty: item.qty,
    price: item.price,
    total: { formula: `B${index + 2}*C${index + 2}` }
  });
});

// Add totals row
const lastRow = sheet.rowCount + 1;
sheet.addRow({
  product: 'TOTAL',
  total: { formula: `SUM(D2:D${lastRow - 1})` }
});

// Currency formatting
sheet.getColumn('price').numFmt = '$#,##0.00';
sheet.getColumn('total').numFmt = '$#,##0.00';

await workbook.xlsx.writeFile('report.xlsx');

Create Spreadsheet (Python - openpyxl)

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active
ws.title = 'Sales Report'

# Headers
headers = ['Product', 'Quantity', 'Price', 'Total']
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True, color='FFFFFF')
    cell.fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')

# Data
data = [
    ('Widget A', 100, 10),
    ('Widget B', 50, 25),
    ('Widget C', 75, 15),
]

for row_idx, (product, qty, price) in enumerate(data, 2):
    ws.cell(row=row_idx, column=1, value=product)
    ws.cell(row=row_idx, column=2, value=qty)
    ws.cell(row=row_idx, column=3, value=price)
    ws.cell(row=row_idx, column=4, value=f'=B{row_idx}*C{row_idx}')

# Totals row
total_row = len(data) + 2
ws.cell(row=total_row, column=1, value='TOTAL')
ws.cell(row=total_row, column=4, value=f'=SUM(D2:D{total_row-1})')

# Number formatting
for row in range(2, total_row + 1):
    ws.cell(row=row, column=3).number_format = '$#,##0.00'
    ws.cell(row=row, column=4).number_format = '$#,##0.00'

wb.save('report.xlsx')

Read and Analyze (Python - pandas)

import pandas as pd

# Read Excel file
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# Analysis
summary = df.groupby('Category').agg({
    'Sales': 'sum',
    'Quantity': 'mean'
}).round(2)

# Write to Excel with formatting
with pd.ExcelWriter('analysis.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Raw Data', index=False)
    summary.to_excel(writer, sheet_name='Summary')

    # Auto-adjust column widths
    for sheet in writer.sheets.values():
        for column in sheet.columns:
            max_length = max(len(str(cell.value)) for cell in column)
            sheet.column_dimensions[column[0].column_letter].width = max_length + 2

Add Charts (Python)

from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.title = 'Sales by Product'
chart.x_axis.title = 'Product'
chart.y_axis.title = 'Sales'

# Data range (assumes column D contains the series and row 1 is headers)
max_row = ws.max_row
data_ref = Reference(ws, min_col=4, min_row=1, max_row=max_row, max_col=4)
categories = Reference(ws, min_col=1, min_row=2, max_row=max_row)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories)
chart.shape = 4

ws.add_chart(chart, 'F2')

Conditional Formatting

from openpyxl.formatting.rule import ColorScaleRule, FormulaRule
from openpyxl.styles import PatternFill

# Color scale (heatmap)
ws.conditional_formatting.add(
    'D2:D100',
    ColorScaleRule(
        start_type='min', start_color='FF0000',
        end_type='max', end_color='00FF00'
    )
)

# Highlight cells above threshold
red_fill = PatternFill(start_color='FFCCCC', fill_type='solid')
ws.conditional_formatting.add(
    'D2:D100',
    FormulaRule(formula=['D2>1000'], fill=red_fill)
)

---

Common Formulas Reference

---

Decision Tree

Excel Task: [What do you need?]
    ├─ Create new spreadsheet?
    │   ├─ Simple data export → pandas to_excel()
    │   ├─ Formatted report → exceljs or openpyxl
    │   └─ With charts → openpyxl charts module
    │
    ├─ Read/analyze existing?
    │   ├─ Data analysis → pandas read_excel()
    │   ├─ Preserve formatting → openpyxl load_workbook()
    │   └─ Fast parsing → xlsx (SheetJS)
    │
    ├─ Modify existing?
    │   ├─ Add data → openpyxl (preserves formatting)
    │   └─ Update formulas → openpyxl
    │
    └─ Complex features?
        ├─ Pivot tables → pandas summary tables or xlwings (native pivots)
        ├─ Data validation → openpyxl DataValidation
        └─ Macros → preserve only; use xlwings for Excel automation

---

Do / Avoid (Jan 2026)

Do

• Separate Inputs / Calculations / Outputs (tabs or clear sections).
• Keep assumptions explicit (value + unit + source + date).
• Add control totals and reconciliation checks for imported data.

Avoid

• Hardcoded constants inside formulas without a documented assumption.
• Hidden rows/columns that change results without documentation.
• Sharing sheets with customer PII or secrets.

What Good Looks Like

• Structure: clear Inputs/Assumptions, Calculations, and Outputs separation (tabs or sections).
• Integrity: no #REF!, broken named ranges, or hardcoded constants hidden in formulas.
• Traceability: every key output ties back to labeled inputs (units + source + date).
• Checks: control totals, reconciliations, and error flags that fail loudly.
• Review: independent review pass using assets/spreadsheet-model-review-checklist.md.

Optional: AI / Automation

Use only when explicitly requested and policy-compliant.

• Generate first-pass formulas/charts; humans verify correctness and edge cases.
• Draft documentation tabs (assumptions, glossary); do not invent source data.

Navigation

Resources

• references/excel-formulas.md — Formula reference and patterns
• references/excel-formatting.md — Styling, conditional formatting
• references/excel-charts.md — Chart types and customization
• references/excel-data-validation.md — Dropdowns, input constraints, cascading validation
• references/excel-pivot-tables.md — Pivot workarounds, summary patterns, pandas
• references/excel-security-protection.md — Sheet protection, formula injection prevention
• data/sources.json — Library documentation links

Templates

• assets/financial-report.md — Financial statement template
• assets/data-dashboard.md — Dashboard with charts
• assets/spreadsheet-model-review-checklist.md — Model QA checklist (assumptions, formulas, traceability)

Related Skills

• ../document-pdf/SKILL.md — PDF generation from data
• ../ai-ml-data-science/SKILL.md — Data analysis patterns
• ../data-sql-optimization/SKILL.md — Database to Excel workflows

Fact-Checking

• Use web search/web fetch to verify current external facts, versions, pricing, deadlines, regulations, or platform behavior before final answers.
• Prefer primary sources; report source links and dates for volatile information.
• If web access is unavailable, state the limitation and mark guidance as unverified.

Data Dashboard Template

Copy-paste template for generating Excel dashboards with charts, KPIs, and data tables.

---

KPI Dashboard

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.chart import BarChart, LineChart, PieChart, Reference
from openpyxl.chart.label import DataLabelList
from openpyxl.utils import get_column_letter

def create_kpi_dashboard(data: dict, output_path: str = 'dashboard.xlsx'):
    """
    Generate a KPI dashboard with charts and metrics.

    Args:
        data: Dictionary with KPIs, trends, and breakdowns
        output_path: Output file path

    Example data:
        {
            'title': 'Q4 2024 Sales Dashboard',
            'kpis': [
                {'name': 'Total Revenue', 'value': 1250000, 'target': 1200000, 'format': 'currency'},
                {'name': 'Orders', 'value': 3420, 'target': 3000, 'format': 'number'},
                {'name': 'Conversion Rate', 'value': 0.032, 'target': 0.03, 'format': 'percent'},
                {'name': 'Avg Order Value', 'value': 365.50, 'target': 350, 'format': 'currency'},
            ],
            'monthly_trend': {
                'labels': ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
                          'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
                'revenue': [85000, 92000, 88000, 95000, 102000, 98000,
                           105000, 112000, 108000, 115000, 125000, 125000],
                'orders': [240, 260, 250, 270, 290, 280, 300, 320, 310, 330, 360, 360],
            },
            'category_breakdown': [
                ('Electronics', 450000),
                ('Clothing', 380000),
                ('Home & Garden', 250000),
                ('Sports', 170000),
            ],
            'top_products': [
                ('Widget Pro X', 2500, 125000),
                ('Smart Watch Elite', 1800, 89000),
                ('Wireless Earbuds', 3200, 64000),
                ('Laptop Stand', 2100, 52000),
                ('Phone Case Premium', 4500, 45000),
            ]
        }
    """
    wb = Workbook()
    ws = wb.active
    ws.title = 'Dashboard'

    # Styles
    title_font = Font(bold=True, size=16, color='FFFFFF')
    header_font = Font(bold=True, size=11)
    kpi_value_font = Font(bold=True, size=24)
    kpi_label_font = Font(size=10, color='666666')
    section_fill = PatternFill(start_color='4472C4', fill_type='solid')
    kpi_positive_fill = PatternFill(start_color='C6EFCE', fill_type='solid')
    kpi_negative_fill = PatternFill(start_color='FFC7CE', fill_type='solid')

    # Page setup
    ws.sheet_view.showGridLines = False
    for col in range(1, 15):
        ws.column_dimensions[get_column_letter(col)].width = 12

    row = 1

    # ═══════════════════════════════════════════════════════════
    # TITLE SECTION
    # ═══════════════════════════════════════════════════════════

    ws.merge_cells('A1:N1')
    ws['A1'] = data['title']
    ws['A1'].font = title_font
    ws['A1'].fill = section_fill
    ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
    ws.row_dimensions[1].height = 35
    row = 3

    # ═══════════════════════════════════════════════════════════
    # KPI CARDS
    # ═══════════════════════════════════════════════════════════

    kpi_start_col = 1
    for i, kpi in enumerate(data['kpis']):
        col = kpi_start_col + (i * 3)

        # KPI Card background
        for r in range(row, row + 4):
            for c in range(col, col + 3):
                cell = ws.cell(row=r, column=c)
                cell.fill = PatternFill(start_color='F2F2F2', fill_type='solid')

        # KPI Name
        ws.merge_cells(start_row=row, start_column=col, end_row=row, end_column=col+2)
        cell = ws.cell(row=row, column=col)
        cell.value = kpi['name']
        cell.font = kpi_label_font
        cell.alignment = Alignment(horizontal='center')

        # KPI Value
        ws.merge_cells(start_row=row+1, start_column=col, end_row=row+1, end_column=col+2)
        cell = ws.cell(row=row+1, column=col)

        if kpi['format'] == 'currency':
            cell.value = kpi['value']
            cell.number_format = '$#,##0'
        elif kpi['format'] == 'percent':
            cell.value = kpi['value']
            cell.number_format = '0.0%'
        else:
            cell.value = kpi['value']
            cell.number_format = '#,##0'

        cell.font = kpi_value_font
        cell.alignment = Alignment(horizontal='center')

        # Target comparison
        ws.merge_cells(start_row=row+2, start_column=col, end_row=row+2, end_column=col+2)
        cell = ws.cell(row=row+2, column=col)

        if kpi['value'] >= kpi['target']:
            variance = (kpi['value'] - kpi['target']) / kpi['target']
            cell.value = f"+{variance:.1%} vs target"
            cell.font = Font(color='006400', size=10)
        else:
            variance = (kpi['target'] - kpi['value']) / kpi['target']
            cell.value = f"-{variance:.1%} vs target"
            cell.font = Font(color='8B0000', size=10)

        cell.alignment = Alignment(horizontal='center')

    row += 5

    # ═══════════════════════════════════════════════════════════
    # TREND CHART (Line)
    # ═══════════════════════════════════════════════════════════

    # Write trend data
    trend_data_row = row
    ws.cell(row=row, column=1, value='Month')
    ws.cell(row=row, column=2, value='Revenue')
    ws.cell(row=row, column=3, value='Orders')

    for i, label in enumerate(data['monthly_trend']['labels']):
        ws.cell(row=row+1+i, column=1, value=label)
        ws.cell(row=row+1+i, column=2, value=data['monthly_trend']['revenue'][i])
        ws.cell(row=row+1+i, column=3, value=data['monthly_trend']['orders'][i])

    # Create line chart
    chart = LineChart()
    chart.title = 'Monthly Revenue Trend'
    chart.style = 10
    chart.y_axis.title = 'Revenue ($)'
    chart.x_axis.title = 'Month'

    data_ref = Reference(ws, min_col=2, min_row=trend_data_row,
                         max_row=trend_data_row+12, max_col=2)
    cats = Reference(ws, min_col=1, min_row=trend_data_row+1,
                     max_row=trend_data_row+12)

    chart.add_data(data_ref, titles_from_data=True)
    chart.set_categories(cats)
    chart.series[0].smooth = True
    chart.width = 18
    chart.height = 10

    ws.add_chart(chart, 'E8')

    row += 15

    # ═══════════════════════════════════════════════════════════
    # CATEGORY BREAKDOWN (Pie Chart)
    # ═══════════════════════════════════════════════════════════

    cat_data_row = row
    ws.cell(row=row, column=1, value='Category')
    ws.cell(row=row, column=2, value='Revenue')

    for i, (category, revenue) in enumerate(data['category_breakdown']):
        ws.cell(row=row+1+i, column=1, value=category)
        ws.cell(row=row+1+i, column=2, value=revenue)

    pie_chart = PieChart()
    pie_chart.title = 'Revenue by Category'

    data_ref = Reference(ws, min_col=2, min_row=cat_data_row,
                         max_row=cat_data_row+len(data['category_breakdown']))
    labels = Reference(ws, min_col=1, min_row=cat_data_row+1,
                       max_row=cat_data_row+len(data['category_breakdown']))

    pie_chart.add_data(data_ref, titles_from_data=True)
    pie_chart.set_categories(labels)

    pie_chart.dataLabels = DataLabelList()
    pie_chart.dataLabels.showPercent = True
    pie_chart.dataLabels.showCatName = True
    pie_chart.dataLabels.showVal = False

    pie_chart.width = 10
    pie_chart.height = 10

    ws.add_chart(pie_chart, 'A23')

    # ═══════════════════════════════════════════════════════════
    # TOP PRODUCTS TABLE
    # ═══════════════════════════════════════════════════════════

    table_row = row
    table_col = 5

    # Headers
    headers = ['Product', 'Units Sold', 'Revenue']
    for i, header in enumerate(headers):
        cell = ws.cell(row=table_row, column=table_col+i)
        cell.value = header
        cell.font = Font(bold=True, color='FFFFFF')
        cell.fill = PatternFill(start_color='4472C4', fill_type='solid')
        cell.alignment = Alignment(horizontal='center')

    # Data rows
    for i, (product, units, revenue) in enumerate(data['top_products']):
        row_num = table_row + 1 + i
        ws.cell(row=row_num, column=table_col, value=product)
        ws.cell(row=row_num, column=table_col+1, value=units).number_format = '#,##0'
        ws.cell(row=row_num, column=table_col+2, value=revenue).number_format = '$#,##0'

        # Alternating row colors
        if i % 2 == 0:
            for c in range(table_col, table_col+3):
                ws.cell(row=row_num, column=c).fill = PatternFill(
                    start_color='F2F2F2', fill_type='solid'
                )

    # Adjust column widths for table
    ws.column_dimensions[get_column_letter(table_col)].width = 20
    ws.column_dimensions[get_column_letter(table_col+1)].width = 12
    ws.column_dimensions[get_column_letter(table_col+2)].width = 12

    # Hide raw data columns
    ws.column_dimensions['A'].hidden = False
    ws.column_dimensions['B'].hidden = False
    ws.column_dimensions['C'].hidden = False

    wb.save(output_path)
    return output_path


# Example usage
if __name__ == '__main__':
    sample_data = {
        'title': 'Q4 2024 Sales Dashboard',
        'kpis': [
            {'name': 'Total Revenue', 'value': 1250000, 'target': 1200000, 'format': 'currency'},
            {'name': 'Orders', 'value': 3420, 'target': 3000, 'format': 'number'},
            {'name': 'Conversion Rate', 'value': 0.032, 'target': 0.03, 'format': 'percent'},
            {'name': 'Avg Order Value', 'value': 365.50, 'target': 350, 'format': 'currency'},
        ],
        'monthly_trend': {
            'labels': ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
                      'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
            'revenue': [85000, 92000, 88000, 95000, 102000, 98000,
                       105000, 112000, 108000, 115000, 125000, 125000],
            'orders': [240, 260, 250, 270, 290, 280, 300, 320, 310, 330, 360, 360],
        },
        'category_breakdown': [
            ('Electronics', 450000),
            ('Clothing', 380000),
            ('Home & Garden', 250000),
            ('Sports', 170000),
        ],
        'top_products': [
            ('Widget Pro X', 2500, 125000),
            ('Smart Watch Elite', 1800, 89000),
            ('Wireless Earbuds', 3200, 64000),
            ('Laptop Stand', 2100, 52000),
            ('Phone Case Premium', 4500, 45000),
        ]
    }

    create_kpi_dashboard(sample_data)

---

Sales Report Dashboard

def create_sales_dashboard(sales_data: list, output_path: str = 'sales_dashboard.xlsx'):
    """
    Generate sales dashboard from transaction data.

    Args:
        sales_data: List of sales records
        output_path: Output file path

    Example sales_data:
        [
            {'date': '2024-01-15', 'product': 'Widget A', 'category': 'Electronics',
             'quantity': 5, 'unit_price': 99.99, 'region': 'North'},
            ...
        ]
    """
    import pandas as pd
    from datetime import datetime

    # Convert to DataFrame for analysis
    df = pd.DataFrame(sales_data)
    df['date'] = pd.to_datetime(df['date'])
    df['revenue'] = df['quantity'] * df['unit_price']
    df['month'] = df['date'].dt.strftime('%Y-%m')

    wb = Workbook()

    # ═══════════════════════════════════════════════════════════
    # SUMMARY SHEET
    # ═══════════════════════════════════════════════════════════

    ws_summary = wb.active
    ws_summary.title = 'Summary'

    # KPIs
    total_revenue = df['revenue'].sum()
    total_orders = len(df)
    avg_order_value = df['revenue'].mean()
    top_category = df.groupby('category')['revenue'].sum().idxmax()

    kpis = [
        ('Total Revenue', total_revenue, '$#,##0.00'),
        ('Total Orders', total_orders, '#,##0'),
        ('Avg Order Value', avg_order_value, '$#,##0.00'),
        ('Top Category', top_category, '@'),
    ]

    ws_summary['A1'] = 'Sales Dashboard Summary'
    ws_summary['A1'].font = Font(bold=True, size=16)

    for i, (label, value, fmt) in enumerate(kpis):
        ws_summary.cell(row=3+i, column=1, value=label)
        cell = ws_summary.cell(row=3+i, column=2, value=value)
        if fmt != '@':
            cell.number_format = fmt

    # Monthly trend
    monthly = df.groupby('month')['revenue'].sum().reset_index()

    row = 10
    ws_summary.cell(row=row, column=1, value='Month')
    ws_summary.cell(row=row, column=2, value='Revenue')

    for i, (_, month_row) in enumerate(monthly.iterrows()):
        ws_summary.cell(row=row+1+i, column=1, value=month_row['month'])
        ws_summary.cell(row=row+1+i, column=2, value=month_row['revenue'])

    # Add trend chart
    chart = BarChart()
    chart.title = 'Monthly Revenue'
    chart.type = 'col'

    data_ref = Reference(ws_summary, min_col=2, min_row=row,
                         max_row=row+len(monthly))
    cats = Reference(ws_summary, min_col=1, min_row=row+1,
                     max_row=row+len(monthly))

    chart.add_data(data_ref, titles_from_data=True)
    chart.set_categories(cats)
    chart.width = 15
    chart.height = 8

    ws_summary.add_chart(chart, 'D3')

    # ═══════════════════════════════════════════════════════════
    # RAW DATA SHEET
    # ═══════════════════════════════════════════════════════════

    ws_data = wb.create_sheet('Raw Data')

    # Headers
    headers = ['Date', 'Product', 'Category', 'Quantity', 'Unit Price', 'Revenue', 'Region']
    for i, header in enumerate(headers, 1):
        cell = ws_data.cell(row=1, column=i, value=header)
        cell.font = Font(bold=True)
        cell.fill = PatternFill(start_color='4472C4', fill_type='solid')
        cell.font = Font(bold=True, color='FFFFFF')

    # Data
    for i, record in enumerate(sales_data, 2):
        ws_data.cell(row=i, column=1, value=record['date'])
        ws_data.cell(row=i, column=2, value=record['product'])
        ws_data.cell(row=i, column=3, value=record['category'])
        ws_data.cell(row=i, column=4, value=record['quantity'])
        ws_data.cell(row=i, column=5, value=record['unit_price']).number_format = '$#,##0.00'
        revenue = record['quantity'] * record['unit_price']
        ws_data.cell(row=i, column=6, value=revenue).number_format = '$#,##0.00'
        ws_data.cell(row=i, column=7, value=record['region'])

    # Auto-filter
    ws_data.auto_filter.ref = f'A1:G{len(sales_data)+1}'

    # Freeze header row
    ws_data.freeze_panes = 'A2'

    wb.save(output_path)
    return output_path

---

Project Status Dashboard

def create_project_dashboard(projects: list, output_path: str = 'project_dashboard.xlsx'):
    """
    Generate project status dashboard.

    Args:
        projects: List of project dictionaries

    Example:
        [
            {
                'name': 'Website Redesign',
                'status': 'In Progress',
                'progress': 0.65,
                'budget': 50000,
                'spent': 32000,
                'due_date': '2024-03-15',
                'owner': 'John Smith'
            },
            ...
        ]
    """
    from openpyxl.formatting.rule import DataBarRule, FormulaRule

    wb = Workbook()
    ws = wb.active
    ws.title = 'Projects'

    # Styles
    header_fill = PatternFill(start_color='4472C4', fill_type='solid')
    header_font = Font(bold=True, color='FFFFFF')

    # Status colors
    status_colors = {
        'Completed': 'C6EFCE',
        'In Progress': 'FFEB9C',
        'At Risk': 'FFC7CE',
        'Not Started': 'F2F2F2',
    }

    # Headers
    headers = ['Project', 'Status', 'Progress', 'Budget', 'Spent', 'Remaining', 'Due Date', 'Owner']
    ws.column_dimensions['A'].width = 25
    ws.column_dimensions['B'].width = 12
    ws.column_dimensions['C'].width = 12
    ws.column_dimensions['D'].width = 12
    ws.column_dimensions['E'].width = 12
    ws.column_dimensions['F'].width = 12
    ws.column_dimensions['G'].width = 12
    ws.column_dimensions['H'].width = 15

    for i, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=i, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal='center')

    # Data
    for i, project in enumerate(projects, 2):
        ws.cell(row=i, column=1, value=project['name'])

        # Status with color
        status_cell = ws.cell(row=i, column=2, value=project['status'])
        status_cell.fill = PatternFill(
            start_color=status_colors.get(project['status'], 'FFFFFF'),
            fill_type='solid'
        )
        status_cell.alignment = Alignment(horizontal='center')

        # Progress
        ws.cell(row=i, column=3, value=project['progress']).number_format = '0%'

        # Budget
        ws.cell(row=i, column=4, value=project['budget']).number_format = '$#,##0'
        ws.cell(row=i, column=5, value=project['spent']).number_format = '$#,##0'

        # Remaining (formula)
        ws.cell(row=i, column=6, value=f'=D{i}-E{i}').number_format = '$#,##0'

        # Due date
        ws.cell(row=i, column=7, value=project['due_date'])

        # Owner
        ws.cell(row=i, column=8, value=project['owner'])

    # Add data bars for progress
    ws.conditional_formatting.add(
        f'C2:C{len(projects)+1}',
        DataBarRule(
            start_type='num', start_value=0,
            end_type='num', end_value=1,
            color='4472C4'
        )
    )

    # Highlight overbudget projects
    over_budget_fill = PatternFill(start_color='FFC7CE', fill_type='solid')
    ws.conditional_formatting.add(
        f'F2:F{len(projects)+1}',
        FormulaRule(formula=['F2<0'], fill=over_budget_fill)
    )

    # Freeze header
    ws.freeze_panes = 'A2'

    # Auto-filter
    ws.auto_filter.ref = f'A1:H{len(projects)+1}'

    wb.save(output_path)
    return output_path

---

Usage Pattern

# Import templates
from dashboard_templates import (
    create_kpi_dashboard,
    create_sales_dashboard,
    create_project_dashboard
)

# Generate KPI dashboard
kpi_data = fetch_kpi_data()  # From your data source
create_kpi_dashboard(kpi_data, 'reports/kpi_dashboard.xlsx')

# Generate sales dashboard from transactions
sales_records = fetch_sales_data()
create_sales_dashboard(sales_records, 'reports/sales_dashboard.xlsx')

# Generate project status
projects = fetch_project_status()
create_project_dashboard(projects, 'reports/project_status.xlsx')

Financial Report Template

Copy-paste template for generating financial statements and reports in Excel.

---

Income Statement

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.utils import get_column_letter

def create_income_statement(data: dict, output_path: str = 'income_statement.xlsx'):
    """
    Generate a professional income statement.

    Args:
        data: Dictionary with revenue, expenses, and metadata
        output_path: Output file path

    Example data:
        {
            'company': 'Acme Corp',
            'period': 'Q4 2024',
            'revenue': [
                ('Product Sales', 150000),
                ('Service Revenue', 50000),
                ('Other Income', 5000),
            ],
            'cogs': [
                ('Cost of Goods Sold', 80000),
            ],
            'operating_expenses': [
                ('Salaries & Wages', 45000),
                ('Rent', 12000),
                ('Utilities', 3000),
                ('Marketing', 8000),
                ('Depreciation', 5000),
            ],
            'other_expenses': [
                ('Interest Expense', 2000),
            ],
            'tax_rate': 0.25
        }
    """
    wb = Workbook()
    ws = wb.active
    ws.title = 'Income Statement'

    # Styles
    title_font = Font(bold=True, size=14)
    header_font = Font(bold=True, size=11)
    section_fill = PatternFill(start_color='E7E6E6', fill_type='solid')
    currency_format = '$#,##0.00'
    border = Border(bottom=Side(style='thin'))
    double_border = Border(bottom=Side(style='double'))

    row = 1

    # Title
    ws.merge_cells(f'A{row}:C{row}')
    ws[f'A{row}'] = f"{data['company']} - Income Statement"
    ws[f'A{row}'].font = title_font
    row += 1

    ws.merge_cells(f'A{row}:C{row}')
    ws[f'A{row}'] = f"Period: {data['period']}"
    row += 2

    # Column widths
    ws.column_dimensions['A'].width = 35
    ws.column_dimensions['B'].width = 15
    ws.column_dimensions['C'].width = 15

    # Revenue Section
    ws[f'A{row}'] = 'REVENUE'
    ws[f'A{row}'].font = header_font
    ws[f'A{row}'].fill = section_fill
    row += 1

    total_revenue = 0
    for item, amount in data['revenue']:
        ws[f'A{row}'] = f'  {item}'
        ws[f'B{row}'] = amount
        ws[f'B{row}'].number_format = currency_format
        total_revenue += amount
        row += 1

    ws[f'A{row}'] = 'Total Revenue'
    ws[f'A{row}'].font = header_font
    ws[f'B{row}'] = total_revenue
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].font = header_font
    ws[f'B{row}'].border = border
    row += 2

    # COGS
    ws[f'A{row}'] = 'COST OF GOODS SOLD'
    ws[f'A{row}'].font = header_font
    ws[f'A{row}'].fill = section_fill
    row += 1

    total_cogs = 0
    for item, amount in data['cogs']:
        ws[f'A{row}'] = f'  {item}'
        ws[f'B{row}'] = amount
        ws[f'B{row}'].number_format = currency_format
        total_cogs += amount
        row += 1

    ws[f'A{row}'] = 'Total COGS'
    ws[f'A{row}'].font = header_font
    ws[f'B{row}'] = total_cogs
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].font = header_font
    ws[f'B{row}'].border = border
    row += 2

    # Gross Profit
    gross_profit = total_revenue - total_cogs
    ws[f'A{row}'] = 'GROSS PROFIT'
    ws[f'A{row}'].font = Font(bold=True, size=12)
    ws[f'B{row}'] = gross_profit
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].font = Font(bold=True, size=12)
    row += 2

    # Operating Expenses
    ws[f'A{row}'] = 'OPERATING EXPENSES'
    ws[f'A{row}'].font = header_font
    ws[f'A{row}'].fill = section_fill
    row += 1

    total_opex = 0
    for item, amount in data['operating_expenses']:
        ws[f'A{row}'] = f'  {item}'
        ws[f'B{row}'] = amount
        ws[f'B{row}'].number_format = currency_format
        total_opex += amount
        row += 1

    ws[f'A{row}'] = 'Total Operating Expenses'
    ws[f'A{row}'].font = header_font
    ws[f'B{row}'] = total_opex
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].font = header_font
    ws[f'B{row}'].border = border
    row += 2

    # Operating Income
    operating_income = gross_profit - total_opex
    ws[f'A{row}'] = 'OPERATING INCOME'
    ws[f'A{row}'].font = Font(bold=True, size=12)
    ws[f'B{row}'] = operating_income
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].font = Font(bold=True, size=12)
    row += 2

    # Other Expenses
    total_other = sum(amount for _, amount in data.get('other_expenses', []))
    if total_other > 0:
        ws[f'A{row}'] = 'OTHER EXPENSES'
        ws[f'A{row}'].font = header_font
        ws[f'A{row}'].fill = section_fill
        row += 1

        for item, amount in data['other_expenses']:
            ws[f'A{row}'] = f'  {item}'
            ws[f'B{row}'] = amount
            ws[f'B{row}'].number_format = currency_format
            row += 1
        row += 1

    # Income Before Tax
    income_before_tax = operating_income - total_other
    ws[f'A{row}'] = 'INCOME BEFORE TAX'
    ws[f'A{row}'].font = header_font
    ws[f'B{row}'] = income_before_tax
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].font = header_font
    row += 1

    # Tax
    tax = income_before_tax * data.get('tax_rate', 0.25)
    ws[f'A{row}'] = f"  Income Tax ({data.get('tax_rate', 0.25):.0%})"
    ws[f'B{row}'] = tax
    ws[f'B{row}'].number_format = currency_format
    row += 2

    # Net Income
    net_income = income_before_tax - tax
    ws[f'A{row}'] = 'NET INCOME'
    ws[f'A{row}'].font = Font(bold=True, size=14)
    ws[f'B{row}'] = net_income
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].font = Font(bold=True, size=14)
    ws[f'B{row}'].border = double_border

    wb.save(output_path)
    return output_path


# Example usage
if __name__ == '__main__':
    sample_data = {
        'company': 'Acme Corporation',
        'period': 'Q4 2024',
        'revenue': [
            ('Product Sales', 150000),
            ('Service Revenue', 50000),
            ('Other Income', 5000),
        ],
        'cogs': [
            ('Cost of Goods Sold', 80000),
        ],
        'operating_expenses': [
            ('Salaries & Wages', 45000),
            ('Rent', 12000),
            ('Utilities', 3000),
            ('Marketing', 8000),
            ('Depreciation', 5000),
        ],
        'other_expenses': [
            ('Interest Expense', 2000),
        ],
        'tax_rate': 0.25
    }

    create_income_statement(sample_data)

---

Balance Sheet

def create_balance_sheet(data: dict, output_path: str = 'balance_sheet.xlsx'):
    """
    Generate a professional balance sheet.

    Args:
        data: Dictionary with assets, liabilities, and equity
        output_path: Output file path

    Example data:
        {
            'company': 'Acme Corp',
            'as_of': 'December 31, 2024',
            'current_assets': [
                ('Cash & Equivalents', 50000),
                ('Accounts Receivable', 35000),
                ('Inventory', 25000),
                ('Prepaid Expenses', 5000),
            ],
            'non_current_assets': [
                ('Property & Equipment', 150000),
                ('Less: Accumulated Depreciation', -30000),
                ('Intangible Assets', 20000),
            ],
            'current_liabilities': [
                ('Accounts Payable', 25000),
                ('Accrued Expenses', 10000),
                ('Short-term Debt', 15000),
            ],
            'non_current_liabilities': [
                ('Long-term Debt', 80000),
            ],
            'equity': [
                ('Common Stock', 50000),
                ('Retained Earnings', 75000),
            ]
        }
    """
    wb = Workbook()
    ws = wb.active
    ws.title = 'Balance Sheet'

    # Styles
    title_font = Font(bold=True, size=14)
    header_font = Font(bold=True, size=11)
    section_fill = PatternFill(start_color='E7E6E6', fill_type='solid')
    currency_format = '$#,##0.00'
    border = Border(bottom=Side(style='thin'))
    double_border = Border(bottom=Side(style='double'))

    row = 1

    # Title
    ws.merge_cells(f'A{row}:C{row}')
    ws[f'A{row}'] = f"{data['company']} - Balance Sheet"
    ws[f'A{row}'].font = title_font
    row += 1

    ws.merge_cells(f'A{row}:C{row}')
    ws[f'A{row}'] = f"As of: {data['as_of']}"
    row += 2

    # Column widths
    ws.column_dimensions['A'].width = 35
    ws.column_dimensions['B'].width = 18

    # ASSETS
    ws[f'A{row}'] = 'ASSETS'
    ws[f'A{row}'].font = Font(bold=True, size=12)
    row += 1

    # Current Assets
    ws[f'A{row}'] = 'Current Assets'
    ws[f'A{row}'].font = header_font
    ws[f'A{row}'].fill = section_fill
    row += 1

    total_current_assets = 0
    for item, amount in data['current_assets']:
        ws[f'A{row}'] = f'  {item}'
        ws[f'B{row}'] = amount
        ws[f'B{row}'].number_format = currency_format
        total_current_assets += amount
        row += 1

    ws[f'A{row}'] = 'Total Current Assets'
    ws[f'A{row}'].font = header_font
    ws[f'B{row}'] = total_current_assets
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].border = border
    row += 2

    # Non-Current Assets
    ws[f'A{row}'] = 'Non-Current Assets'
    ws[f'A{row}'].font = header_font
    ws[f'A{row}'].fill = section_fill
    row += 1

    total_non_current_assets = 0
    for item, amount in data['non_current_assets']:
        ws[f'A{row}'] = f'  {item}'
        ws[f'B{row}'] = amount
        ws[f'B{row}'].number_format = currency_format
        total_non_current_assets += amount
        row += 1

    ws[f'A{row}'] = 'Total Non-Current Assets'
    ws[f'A{row}'].font = header_font
    ws[f'B{row}'] = total_non_current_assets
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].border = border
    row += 2

    # Total Assets
    total_assets = total_current_assets + total_non_current_assets
    ws[f'A{row}'] = 'TOTAL ASSETS'
    ws[f'A{row}'].font = Font(bold=True, size=12)
    ws[f'B{row}'] = total_assets
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].font = Font(bold=True, size=12)
    ws[f'B{row}'].border = double_border
    row += 3

    # LIABILITIES
    ws[f'A{row}'] = 'LIABILITIES'
    ws[f'A{row}'].font = Font(bold=True, size=12)
    row += 1

    # Current Liabilities
    ws[f'A{row}'] = 'Current Liabilities'
    ws[f'A{row}'].font = header_font
    ws[f'A{row}'].fill = section_fill
    row += 1

    total_current_liab = 0
    for item, amount in data['current_liabilities']:
        ws[f'A{row}'] = f'  {item}'
        ws[f'B{row}'] = amount
        ws[f'B{row}'].number_format = currency_format
        total_current_liab += amount
        row += 1

    ws[f'A{row}'] = 'Total Current Liabilities'
    ws[f'A{row}'].font = header_font
    ws[f'B{row}'] = total_current_liab
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].border = border
    row += 2

    # Non-Current Liabilities
    ws[f'A{row}'] = 'Non-Current Liabilities'
    ws[f'A{row}'].font = header_font
    ws[f'A{row}'].fill = section_fill
    row += 1

    total_non_current_liab = 0
    for item, amount in data['non_current_liabilities']:
        ws[f'A{row}'] = f'  {item}'
        ws[f'B{row}'] = amount
        ws[f'B{row}'].number_format = currency_format
        total_non_current_liab += amount
        row += 1

    ws[f'A{row}'] = 'Total Non-Current Liabilities'
    ws[f'A{row}'].font = header_font
    ws[f'B{row}'] = total_non_current_liab
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].border = border
    row += 2

    total_liabilities = total_current_liab + total_non_current_liab
    ws[f'A{row}'] = 'TOTAL LIABILITIES'
    ws[f'A{row}'].font = Font(bold=True, size=12)
    ws[f'B{row}'] = total_liabilities
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].font = Font(bold=True, size=12)
    ws[f'B{row}'].border = border
    row += 3

    # EQUITY
    ws[f'A{row}'] = "SHAREHOLDERS' EQUITY"
    ws[f'A{row}'].font = Font(bold=True, size=12)
    ws[f'A{row}'].fill = section_fill
    row += 1

    total_equity = 0
    for item, amount in data['equity']:
        ws[f'A{row}'] = f'  {item}'
        ws[f'B{row}'] = amount
        ws[f'B{row}'].number_format = currency_format
        total_equity += amount
        row += 1

    ws[f'A{row}'] = 'TOTAL EQUITY'
    ws[f'A{row}'].font = Font(bold=True, size=12)
    ws[f'B{row}'] = total_equity
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].font = Font(bold=True, size=12)
    ws[f'B{row}'].border = border
    row += 2

    # Total Liabilities + Equity
    ws[f'A{row}'] = 'TOTAL LIABILITIES + EQUITY'
    ws[f'A{row}'].font = Font(bold=True, size=12)
    ws[f'B{row}'] = total_liabilities + total_equity
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].font = Font(bold=True, size=12)
    ws[f'B{row}'].border = double_border

    wb.save(output_path)
    return output_path

---

Cash Flow Statement

def create_cash_flow_statement(data: dict, output_path: str = 'cash_flow.xlsx'):
    """
    Generate cash flow statement.

    Args:
        data: Dictionary with operating, investing, financing activities

    Example data:
        {
            'company': 'Acme Corp',
            'period': 'Year Ended December 31, 2024',
            'beginning_cash': 30000,
            'operating': [
                ('Net Income', 45000),
                ('Depreciation', 5000),
                ('Changes in Receivables', -5000),
                ('Changes in Payables', 3000),
            ],
            'investing': [
                ('Purchase of Equipment', -25000),
                ('Sale of Investments', 10000),
            ],
            'financing': [
                ('Proceeds from Debt', 20000),
                ('Dividends Paid', -10000),
            ]
        }
    """
    wb = Workbook()
    ws = wb.active
    ws.title = 'Cash Flow'

    # Styles
    title_font = Font(bold=True, size=14)
    header_font = Font(bold=True, size=11)
    section_fill = PatternFill(start_color='E7E6E6', fill_type='solid')
    currency_format = '$#,##0.00'
    border = Border(bottom=Side(style='thin'))
    double_border = Border(bottom=Side(style='double'))

    row = 1
    ws.column_dimensions['A'].width = 40
    ws.column_dimensions['B'].width = 18

    # Title
    ws[f'A{row}'] = f"{data['company']} - Statement of Cash Flows"
    ws[f'A{row}'].font = title_font
    row += 1
    ws[f'A{row}'] = data['period']
    row += 2

    # Operating Activities
    ws[f'A{row}'] = 'OPERATING ACTIVITIES'
    ws[f'A{row}'].font = header_font
    ws[f'A{row}'].fill = section_fill
    row += 1

    total_operating = 0
    for item, amount in data['operating']:
        ws[f'A{row}'] = f'  {item}'
        ws[f'B{row}'] = amount
        ws[f'B{row}'].number_format = currency_format
        total_operating += amount
        row += 1

    ws[f'A{row}'] = 'Net Cash from Operating Activities'
    ws[f'A{row}'].font = header_font
    ws[f'B{row}'] = total_operating
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].border = border
    row += 2

    # Investing Activities
    ws[f'A{row}'] = 'INVESTING ACTIVITIES'
    ws[f'A{row}'].font = header_font
    ws[f'A{row}'].fill = section_fill
    row += 1

    total_investing = 0
    for item, amount in data['investing']:
        ws[f'A{row}'] = f'  {item}'
        ws[f'B{row}'] = amount
        ws[f'B{row}'].number_format = currency_format
        total_investing += amount
        row += 1

    ws[f'A{row}'] = 'Net Cash from Investing Activities'
    ws[f'A{row}'].font = header_font
    ws[f'B{row}'] = total_investing
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].border = border
    row += 2

    # Financing Activities
    ws[f'A{row}'] = 'FINANCING ACTIVITIES'
    ws[f'A{row}'].font = header_font
    ws[f'A{row}'].fill = section_fill
    row += 1

    total_financing = 0
    for item, amount in data['financing']:
        ws[f'A{row}'] = f'  {item}'
        ws[f'B{row}'] = amount
        ws[f'B{row}'].number_format = currency_format
        total_financing += amount
        row += 1

    ws[f'A{row}'] = 'Net Cash from Financing Activities'
    ws[f'A{row}'].font = header_font
    ws[f'B{row}'] = total_financing
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].border = border
    row += 2

    # Summary
    net_change = total_operating + total_investing + total_financing
    ws[f'A{row}'] = 'NET CHANGE IN CASH'
    ws[f'A{row}'].font = Font(bold=True, size=12)
    ws[f'B{row}'] = net_change
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].font = Font(bold=True, size=12)
    row += 2

    ws[f'A{row}'] = 'Beginning Cash Balance'
    ws[f'B{row}'] = data['beginning_cash']
    ws[f'B{row}'].number_format = currency_format
    row += 1

    ws[f'A{row}'] = 'ENDING CASH BALANCE'
    ws[f'A{row}'].font = Font(bold=True, size=12)
    ws[f'B{row}'] = data['beginning_cash'] + net_change
    ws[f'B{row}'].number_format = currency_format
    ws[f'B{row}'].font = Font(bold=True, size=12)
    ws[f'B{row}'].border = double_border

    wb.save(output_path)
    return output_path

---

Usage Pattern

# Generate all three financial statements
from financial_templates import (
    create_income_statement,
    create_balance_sheet,
    create_cash_flow_statement
)

# Load your data from database/API
company_data = load_financial_data('ACME', '2024-Q4')

# Generate reports
create_income_statement(company_data['income'], 'reports/income_2024q4.xlsx')
create_balance_sheet(company_data['balance'], 'reports/balance_2024q4.xlsx')
create_cash_flow_statement(company_data['cashflow'], 'reports/cashflow_2024q4.xlsx')

Spreadsheet Model Review Checklist (Core, Non-AI)

Purpose: review a spreadsheet model for correctness, traceability, and decision usefulness.

Inputs

• Spreadsheet file + change log (who changed what, when)
• Source data references (exports, databases, reports)
• Business question the model supports (decision + timeline)

Outputs

• Review findings (issues, severity, owner, fix-by date)
• “Ship/No-ship” decision for using the model in decisions

Core

A) Structure and Readability

• [ ] Clear separation: Inputs / Calculations / Outputs (tabs or sections)
• [ ] Consistent units and time granularity (daily/weekly/monthly)
• [ ] Named ranges or clearly labeled tables (avoid magic cells)
• [ ] No hidden rows/columns that change meaning (or documented if used)

B) Inputs and Assumptions

• [ ] Every assumption is explicit (value + unit + source + date)
• [ ] Assumptions are grouped in one place (single “Inputs” area)
• [ ] Scenario controls are obvious (base/best/worst) and not duplicated

C) Formula Integrity

• [ ] No hardcoded constants inside formulas where an input should exist
• [ ] No inconsistent formulas across a range (spot-check rows/columns)
• [ ] Avoid volatile functions unless justified (INDIRECT, OFFSET, TODAY, RAND)
• [ ] Error handling is intentional (IFERROR used only with a documented fallback)

D) Traceability and Auditability

• [ ] Key outputs can be traced to inputs in ≤ 3 clicks
• [ ] Source links/notes exist for imported data (file, query, timestamp)
• [ ] Complex logic has a short explanation (“why”, not “what”)

E) Data Quality Checks

• [ ] Totals reconcile to known sources (control totals)
• [ ] Duplicate/blank/outlier checks exist for key fields
• [ ] Date ranges and filters are explicit (no silent exclusions)

F) Charts and Outputs

• [ ] Each chart has: title, units, timeframe, and data source note
• [ ] Avoid misleading scales (truncated axes, mixed units)
• [ ] Executive summary tab answers the decision question in 60 seconds

G) Versioning and Change Control

• [ ] File naming includes date/version (e.g., Model_2025-12-18_v3.xlsx)
• [ ] Change log tab exists for material edits (assumptions, formulas, structure)
• [ ] Review/approval owner is named

H) Accessibility (baseline)

• [ ] Meaning is not color-only (labels/legends present)
• [ ] Sufficient contrast for key charts/tables
• [ ] Sheet/tab names are descriptive

Decision Rules

• No-ship if: key outputs are not traceable, assumptions are implicit, or formulas are inconsistent.
• Re-review after: changing inputs structure, adding new tabs, or altering core logic.

Risks

• Silent errors (range drift, broken links, copy/paste mistakes)
• Untraceable logic leads to unreviewable decisions
• Data leakage (customer PII embedded in shared files)

Optional: AI / Automation

Use only if allowed by policy and data handling rules.

• Generate a “model audit summary” (tabs, key formulas, dependencies); human spot-checks.
• Suggest tests (control totals, anomaly checks); do not auto-fix formulas without review.

Excel Charts Reference

Chart creation and customization for data visualization in spreadsheets.

---

Chart Types Overview

---

Bar/Column Charts

Basic Bar Chart (Python)

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active

# Sample data
data = [
    ['Product', 'Sales'],
    ['Widget A', 1200],
    ['Widget B', 800],
    ['Widget C', 1500],
    ['Widget D', 950],
]
for row in data:
    ws.append(row)

# Create chart
chart = BarChart()
chart.type = 'col'  # 'col' for vertical, 'bar' for horizontal
chart.style = 10
chart.title = 'Product Sales'
chart.x_axis.title = 'Product'
chart.y_axis.title = 'Sales ($)'

# Data references
data_ref = Reference(ws, min_col=2, min_row=1, max_row=5, max_col=2)
categories = Reference(ws, min_col=1, min_row=2, max_row=5)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories)
chart.shape = 4  # Rounded corners

# Position chart
ws.add_chart(chart, 'D2')
wb.save('bar_chart.xlsx')

Stacked Bar Chart

chart = BarChart()
chart.type = 'col'
chart.grouping = 'stacked'  # 'standard', 'stacked', 'percentStacked'

# Multiple data series
data_ref = Reference(ws, min_col=2, min_row=1, max_row=10, max_col=4)
chart.add_data(data_ref, titles_from_data=True)

Clustered Bar with Custom Colors

from openpyxl.chart.series import DataPoint
from openpyxl.drawing.fill import PatternFillProperties, ColorChoice

chart = BarChart()
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories)

# Custom series colors
colors = ['4472C4', 'ED7D31', '70AD47']
for i, series in enumerate(chart.series):
    series.graphicalProperties.solidFill = colors[i % len(colors)]

---

Line Charts

Basic Line Chart

from openpyxl.chart import LineChart, Reference

chart = LineChart()
chart.style = 10
chart.title = 'Monthly Trend'
chart.x_axis.title = 'Month'
chart.y_axis.title = 'Value'

data_ref = Reference(ws, min_col=2, min_row=1, max_row=13, max_col=2)
categories = Reference(ws, min_col=1, min_row=2, max_row=13)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories)

ws.add_chart(chart, 'D2')

Multi-Series Line Chart

chart = LineChart()
chart.title = 'Year over Year Comparison'

# Add multiple data columns
data_ref = Reference(ws, min_col=2, min_row=1, max_row=13, max_col=4)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories)

# Customize line styles
for i, series in enumerate(chart.series):
    series.graphicalProperties.line.width = 25000  # EMUs (25000 = ~2pt)
    series.smooth = True  # Smooth lines

Line with Markers

from openpyxl.chart.marker import Marker

chart = LineChart()
chart.add_data(data_ref, titles_from_data=True)

for series in chart.series:
    series.marker = Marker(symbol='circle', size=7)
    series.graphicalProperties.line.width = 20000

---

Pie Charts

Basic Pie Chart

from openpyxl.chart import PieChart, Reference

chart = PieChart()
chart.title = 'Market Share'

data_ref = Reference(ws, min_col=2, min_row=1, max_row=5)
labels = Reference(ws, min_col=1, min_row=2, max_row=5)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(labels)

ws.add_chart(chart, 'D2')

Pie Chart with Data Labels

from openpyxl.chart.label import DataLabelList

chart = PieChart()
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(labels)

# Data labels
chart.dataLabels = DataLabelList()
chart.dataLabels.showCatName = True
chart.dataLabels.showPercent = True
chart.dataLabels.showVal = False

Exploded Pie

from openpyxl.chart.series import DataPoint

chart = PieChart()
chart.add_data(data_ref, titles_from_data=True)

# Explode first slice
slice = DataPoint(idx=0, explosion=10)  # 10% explosion
chart.series[0].data_points = [slice]

Doughnut Chart

from openpyxl.chart import DoughnutChart

chart = DoughnutChart()
chart.title = 'Budget Allocation'
chart.holeSize = 50  # Inner hole size (percentage)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(labels)

---

Area Charts

from openpyxl.chart import AreaChart

chart = AreaChart()
chart.style = 10
chart.title = 'Cumulative Growth'
chart.grouping = 'stacked'  # 'standard', 'stacked', 'percentStacked'

data_ref = Reference(ws, min_col=2, min_row=1, max_row=13, max_col=4)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories)

ws.add_chart(chart, 'D2')

---

Scatter Charts

Basic Scatter Plot

from openpyxl.chart import ScatterChart, Reference, Series

chart = ScatterChart()
chart.style = 13
chart.title = 'Correlation Analysis'
chart.x_axis.title = 'Variable X'
chart.y_axis.title = 'Variable Y'

xvalues = Reference(ws, min_col=1, min_row=2, max_row=50)
yvalues = Reference(ws, min_col=2, min_row=2, max_row=50)

series = Series(yvalues, xvalues, title='Data Points')
chart.series.append(series)

ws.add_chart(chart, 'D2')

Scatter with Trendline

from openpyxl.chart.trendline import Trendline

chart = ScatterChart()
# ... add data ...

# Add linear trendline
trendline = Trendline(trendlineType='linear')
chart.series[0].trendline = trendline

# Other types: 'exp', 'log', 'poly', 'power', 'movingAvg'

---

Combo Charts

Column + Line Combination

from openpyxl.chart import BarChart, LineChart, Reference

# Primary chart (columns)
bar_chart = BarChart()
bar_chart.type = 'col'
bar_data = Reference(ws, min_col=2, min_row=1, max_row=13)
bar_chart.add_data(bar_data, titles_from_data=True)
bar_chart.set_categories(categories)

# Secondary chart (line)
line_chart = LineChart()
line_data = Reference(ws, min_col=3, min_row=1, max_row=13)
line_chart.add_data(line_data, titles_from_data=True)

# Use secondary Y axis
line_chart.y_axis.axId = 200
line_chart.y_axis.crosses = 'max'

# Combine charts
bar_chart += line_chart

ws.add_chart(bar_chart, 'D2')

---

Chart Customization

Size and Position

chart.width = 15  # Width in cm
chart.height = 10  # Height in cm

# Anchor position
ws.add_chart(chart, 'D2')  # Top-left cell

# Alternative: absolute positioning
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker
chart.anchor = 'D2'  # Or use TwoCellAnchor for resizing with cells

Legend Position

from openpyxl.chart.legend import Legend

chart.legend = Legend()
chart.legend.position = 'b'  # 'b'=bottom, 't'=top, 'l'=left, 'r'=right, 'tr'=top-right
chart.legend.overlay = False

# Hide legend
chart.legend = None

Axis Formatting

# Number format
chart.y_axis.numFmt = '$#,##0'

# Axis bounds
chart.y_axis.scaling.min = 0
chart.y_axis.scaling.max = 10000

# Axis title
chart.x_axis.title = 'Quarter'
chart.y_axis.title = 'Revenue ($)'

# Hide axis
chart.x_axis.delete = True

Gridlines

from openpyxl.chart.axis import ChartLines

# Major gridlines
chart.y_axis.majorGridlines = ChartLines()

# Hide gridlines
chart.y_axis.majorGridlines = None
chart.y_axis.minorGridlines = None

Title Formatting

from openpyxl.chart.text import RichText
from openpyxl.drawing.text import Paragraph, ParagraphProperties, CharacterProperties

chart.title = 'Sales Report'

# Styled title
props = CharacterProperties(b=True, sz=1400)  # Bold, 14pt
para = Paragraph(pPr=ParagraphProperties(defRPr=props), r=[])
chart.title.tx.rich.p = [para]

---

Chart Templates

Dashboard KPI Chart

def create_kpi_chart(ws, data_range, title, position):
    """Create a clean KPI column chart."""
    chart = BarChart()
    chart.type = 'col'
    chart.style = 10
    chart.title = title

    data = Reference(ws, **data_range)
    chart.add_data(data, titles_from_data=True)

    # Clean styling
    chart.legend = None
    chart.y_axis.majorGridlines = ChartLines()
    chart.y_axis.numFmt = '#,##0'

    # Color scheme
    chart.series[0].graphicalProperties.solidFill = '4472C4'

    chart.width = 10
    chart.height = 6

    ws.add_chart(chart, position)
    return chart

Trend Analysis Chart

def create_trend_chart(ws, data_range, categories_range, title, position):
    """Create line chart with trendline."""
    chart = LineChart()
    chart.style = 10
    chart.title = title

    data = Reference(ws, **data_range)
    cats = Reference(ws, **categories_range)

    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)

    # Smooth lines with markers
    for series in chart.series:
        series.smooth = True
        series.marker = Marker(symbol='circle', size=5)

    # Add trendline
    chart.series[0].trendline = Trendline(trendlineType='linear')

    chart.width = 12
    chart.height = 7

    ws.add_chart(chart, position)
    return chart

Comparison Pie Chart

def create_comparison_pie(ws, data_range, labels_range, title, position):
    """Create pie chart with percentage labels."""
    chart = PieChart()
    chart.title = title

    data = Reference(ws, **data_range)
    labels = Reference(ws, **labels_range)

    chart.add_data(data, titles_from_data=True)
    chart.set_categories(labels)

    # Show percentages
    chart.dataLabels = DataLabelList()
    chart.dataLabels.showPercent = True
    chart.dataLabels.showCatName = True
    chart.dataLabels.showVal = False

    chart.width = 10
    chart.height = 8

    ws.add_chart(chart, position)
    return chart

---

ExcelJS Charts (Node.js)

ExcelJS has limited native chart support. For complex charts, consider:

1. Template approach: Create chart in Excel, use as template 2. Hybrid: Generate data with ExcelJS, open in Excel for charts 3. Alternative: Use xlsx-chart or chart.js for image export

// ExcelJS basic image embedding (for pre-rendered charts)
import ExcelJS from 'exceljs';

const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('Report');

// Add image (chart exported as PNG)
const imageId = workbook.addImage({
  filename: 'chart.png',
  extension: 'png',
});

sheet.addImage(imageId, {
  tl: { col: 4, row: 1 },
  ext: { width: 500, height: 300 }
});

---

Chart Styles Reference

Use chart.style = N to apply built-in Excel chart styles.

Excel Data Validation Reference

Patterns for enforcing input constraints in generated spreadsheets.

---

Contents

• Validation types overview
• openpyxl and ExcelJS code examples
• Named ranges for maintainable lists
• Cascading (dependent) dropdowns
• Error/input messages and protection
• Do / Avoid and common pitfalls

---

Validation Types

---

openpyxl Examples

from openpyxl.worksheet.datavalidation import DataValidation

# Dropdown list
dv = DataValidation(type="list", formula1='"Open,In Progress,Closed"', allow_blank=True)
dv.error = "Pick a valid status."
dv.prompt = "Select a status from the list."
ws.add_data_validation(dv)
dv.add("B2:B500")

# Whole number range
dv_num = DataValidation(type="whole", operator="between", formula1=1, formula2=100)
dv_num.error = "Enter a number between 1 and 100."
ws.add_data_validation(dv_num)
dv_num.add("C2:C500")

# Custom formula (unique values only)
dv_uniq = DataValidation(type="custom", formula1="=COUNTIF($D:$D,D2)<=1")
dv_uniq.error = "Duplicate value."
ws.add_data_validation(dv_uniq)
dv_uniq.add("D2:D500")

ExcelJS Example

worksheet.getCell('B2').dataValidation = {
  type: 'list',
  allowBlank: true,
  formulae: ['"Open,In Progress,Closed"'],
  showErrorMessage: true,
  error: 'Pick a valid status.',
  showInputMessage: true,
  prompt: 'Select a status from the list.'
};

ExcelJS requires setting dataValidation per-cell or looping over a range; there is no range-based add method.

---

Named Ranges for Validation Lists

Hardcoded comma-separated strings break when lists exceed ~255 characters. Use named ranges instead.

from openpyxl.workbook.defined_name import DefinedName

ws_lists = wb.create_sheet("Lists")
statuses = ["Open", "In Progress", "Closed", "Blocked"]
for i, val in enumerate(statuses, start=1):
    ws_lists.cell(row=i, column=1, value=val)
ws_lists.sheet_state = "hidden"

ref = f"Lists!$A$1:$A${len(statuses)}"
defn = DefinedName("StatusList", attr_text=ref)
wb.defined_names.add(defn)

dv = DataValidation(type="list", formula1="StatusList")
ws.add_data_validation(dv)
dv.add("B2:B500")

---

Cascading Dropdowns

Dependent validation (Country -> City) uses INDIRECT with named ranges per parent value.

# Named ranges: "USA" -> Lists!$B$1:$B$3, "UK" -> Lists!$C$1:$C$2

dv_country = DataValidation(type="list", formula1='"USA,UK"')
ws.add_data_validation(dv_country)
dv_country.add("A2:A100")

dv_city = DataValidation(type="list", formula1="=INDIRECT(A2)")
ws.add_data_validation(dv_city)
dv_city.add("B2:B100")

Limitation: INDIRECT is volatile and only resolves in Excel. LibreOffice and Google Sheets have inconsistent support.

---

Error Messages and Input Messages

Set errorStyle to warning when soft guidance is acceptable.

Combining Validation with Sheet Protection

Validation alone does not prevent paste-over. Combine with protection:

from openpyxl.styles import Protection

for row in ws.iter_rows(min_row=2, max_row=500, min_col=2, max_col=2):
    for cell in row:
        cell.protection = Protection(locked=False)  # unlock input cells

ws.protection.sheet = True
ws.protection.password = "edit123"

---

Do / Avoid

Do:

• Use named ranges on a hidden sheet for lists longer than 5 items
• Set both prompt and error messages for every validation rule
• Combine validation with sheet protection on template workbooks
• Test generated files in Excel, LibreOffice, and Google Sheets

Avoid:

• Comma-separated strings over ~200 characters (Excel truncates at 255)
• More than 65,534 validation objects per sheet (Excel hard limit)
• INDIRECT-based cascading dropdowns if the file will be consumed outside Excel
• Validating entire columns (A:A) -- use bounded ranges (A2:A5000)

---

Common Pitfalls

Excel Formatting Reference

Styling, conditional formatting, and visual presentation for spreadsheets.

---

Cell Styling

Font Properties

# openpyxl
from openpyxl.styles import Font

cell.font = Font(
    name='Calibri',
    size=11,
    bold=True,
    italic=False,
    underline='single',  # 'single', 'double', 'singleAccounting', 'doubleAccounting'
    strike=False,
    color='FF0000'  # ARGB hex (no #)
)

// ExcelJS
cell.font = {
  name: 'Calibri',
  size: 11,
  bold: true,
  italic: false,
  underline: true,
  strike: false,
  color: { argb: 'FFFF0000' }
};

Fill (Background Color)

# openpyxl
from openpyxl.styles import PatternFill

# Solid fill
cell.fill = PatternFill(
    start_color='4472C4',
    end_color='4472C4',
    fill_type='solid'
)

# Gradient fill
from openpyxl.styles import GradientFill
cell.fill = GradientFill(
    type='linear',
    degree=90,
    stop=['4472C4', 'FFFFFF']
)

// ExcelJS
cell.fill = {
  type: 'pattern',
  pattern: 'solid',
  fgColor: { argb: 'FF4472C4' }
};

// Gradient
cell.fill = {
  type: 'gradient',
  gradient: 'angle',
  degree: 90,
  stops: [
    { position: 0, color: { argb: 'FF4472C4' } },
    { position: 1, color: { argb: 'FFFFFFFF' } }
  ]
};

Borders

# openpyxl
from openpyxl.styles import Border, Side

thin_border = Border(
    left=Side(style='thin', color='000000'),
    right=Side(style='thin', color='000000'),
    top=Side(style='thin', color='000000'),
    bottom=Side(style='thin', color='000000')
)
cell.border = thin_border

# Border styles: 'thin', 'medium', 'thick', 'double', 'dotted', 'dashed'

// ExcelJS
cell.border = {
  top: { style: 'thin', color: { argb: 'FF000000' } },
  left: { style: 'thin', color: { argb: 'FF000000' } },
  bottom: { style: 'thin', color: { argb: 'FF000000' } },
  right: { style: 'thin', color: { argb: 'FF000000' } }
};

Alignment

# openpyxl
from openpyxl.styles import Alignment

cell.alignment = Alignment(
    horizontal='center',  # 'left', 'center', 'right', 'justify'
    vertical='center',    # 'top', 'center', 'bottom'
    wrap_text=True,
    shrink_to_fit=False,
    indent=0,
    text_rotation=0  # -90 to 90 degrees
)

// ExcelJS
cell.alignment = {
  horizontal: 'center',
  vertical: 'middle',
  wrapText: true,
  shrinkToFit: false,
  indent: 0,
  textRotation: 0
};

---

Number Formatting

Common Formats

Implementation

# openpyxl
cell.number_format = '$#,##0.00'
cell.number_format = 'yyyy-mm-dd'
cell.number_format = '0.00%'

# Custom format with color
cell.number_format = '[Green]$#,##0.00;[Red]-$#,##0.00'

// ExcelJS
cell.numFmt = '$#,##0.00';
cell.numFmt = 'yyyy-mm-dd';
cell.numFmt = '0.00%';

---

Conditional Formatting

Color Scales (Heatmaps)

from openpyxl.formatting.rule import ColorScaleRule

# 2-color scale (red to green)
rule = ColorScaleRule(
    start_type='min', start_color='FF0000',
    end_type='max', end_color='00FF00'
)
ws.conditional_formatting.add('B2:B100', rule)

# 3-color scale
rule = ColorScaleRule(
    start_type='min', start_color='FF0000',
    mid_type='percentile', mid_value=50, mid_color='FFFF00',
    end_type='max', end_color='00FF00'
)
ws.conditional_formatting.add('C2:C100', rule)

// ExcelJS
sheet.addConditionalFormatting({
  ref: 'B2:B100',
  rules: [{
    type: 'colorScale',
    cfvo: [
      { type: 'min' },
      { type: 'max' }
    ],
    color: [
      { argb: 'FFFF0000' },
      { argb: 'FF00FF00' }
    ]
  }]
});

Data Bars

from openpyxl.formatting.rule import DataBarRule

rule = DataBarRule(
    start_type='min',
    end_type='max',
    color='4472C4',
    showValue=True,
    minLength=None,
    maxLength=None
)
ws.conditional_formatting.add('D2:D100', rule)

Icon Sets

from openpyxl.formatting.rule import IconSetRule

# Traffic lights
rule = IconSetRule(
    icon_style='3TrafficLights1',
    type='percent',
    values=[0, 33, 67],
    showValue=True,
    reverse=False
)
ws.conditional_formatting.add('E2:E100', rule)

# Icon styles: '3Arrows', '3TrafficLights1', '4Rating', '5Quarters'

Formula-Based Rules

from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import PatternFill

# Highlight rows where status = "Overdue"
red_fill = PatternFill(start_color='FFCCCC', fill_type='solid')
rule = FormulaRule(
    formula=['$C2="Overdue"'],
    fill=red_fill
)
ws.conditional_formatting.add('A2:E100', rule)

# Highlight duplicates
rule = FormulaRule(
    formula=['COUNTIF($A:$A,A2)>1'],
    fill=PatternFill(start_color='FFFFCC', fill_type='solid')
)
ws.conditional_formatting.add('A2:A100', rule)

Cell Value Rules

from openpyxl.formatting.rule import CellIsRule

# Greater than
rule = CellIsRule(
    operator='greaterThan',
    formula=['100'],
    fill=PatternFill(start_color='C6EFCE', fill_type='solid')
)
ws.conditional_formatting.add('B2:B100', rule)

# Between
rule = CellIsRule(
    operator='between',
    formula=['50', '100'],
    fill=PatternFill(start_color='FFEB9C', fill_type='solid')
)
ws.conditional_formatting.add('B2:B100', rule)

# Operators: 'lessThan', 'lessThanOrEqual', 'greaterThan',
#            'greaterThanOrEqual', 'equal', 'notEqual', 'between'

---

Row and Column Formatting

Column Width

# openpyxl
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 15

# Auto-fit (approximate)
for column in ws.columns:
    max_length = max(len(str(cell.value or '')) for cell in column)
    ws.column_dimensions[column[0].column_letter].width = max_length + 2

// ExcelJS
sheet.getColumn('A').width = 20;

// Set via column definition
sheet.columns = [
  { header: 'Name', key: 'name', width: 20 },
  { header: 'Value', key: 'value', width: 15 }
];

Row Height

# openpyxl
ws.row_dimensions[1].height = 30  # Header row

# All rows
for row in range(1, 101):
    ws.row_dimensions[row].height = 20

// ExcelJS
sheet.getRow(1).height = 30;

Freeze Panes

# openpyxl - Freeze first row
ws.freeze_panes = 'A2'

# Freeze first column
ws.freeze_panes = 'B1'

# Freeze both
ws.freeze_panes = 'B2'

// ExcelJS
sheet.views = [{ state: 'frozen', xSplit: 1, ySplit: 1 }];

Hide Rows/Columns

# openpyxl
ws.column_dimensions['C'].hidden = True
ws.row_dimensions[5].hidden = True

// ExcelJS
sheet.getColumn('C').hidden = true;
sheet.getRow(5).hidden = true;

---

Merged Cells

# openpyxl
ws.merge_cells('A1:D1')  # Merge range
ws['A1'] = 'Report Title'
ws['A1'].alignment = Alignment(horizontal='center')

# Unmerge
ws.unmerge_cells('A1:D1')

// ExcelJS
sheet.mergeCells('A1:D1');
sheet.getCell('A1').value = 'Report Title';
sheet.getCell('A1').alignment = { horizontal: 'center' };

// Unmerge
sheet.unMergeCells('A1:D1');

---

Named Styles

from openpyxl.styles import NamedStyle, Font, Border, Side, PatternFill

# Create reusable style
header_style = NamedStyle(name='header')
header_style.font = Font(bold=True, color='FFFFFF', size=12)
header_style.fill = PatternFill(start_color='4472C4', fill_type='solid')
header_style.border = Border(
    bottom=Side(style='medium', color='000000')
)

# Register style
wb.add_named_style(header_style)

# Apply to cells
for cell in ws[1]:
    cell.style = 'header'

---

Page Setup (Print)

# openpyxl
ws.page_setup.orientation = 'landscape'
ws.page_setup.paperSize = ws.PAPERSIZE_A4
ws.page_setup.fitToPage = True
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0  # Auto

# Print titles (repeat rows/columns)
ws.print_title_rows = '1:1'  # Repeat row 1
ws.print_title_cols = 'A:A'  # Repeat column A

# Print area
ws.print_area = 'A1:F50'

# Headers/footers
ws.oddHeader.center.text = 'Monthly Report'
ws.oddFooter.center.text = 'Page &P of &N'

---

Style Presets

Header Style

def style_header_row(ws, row=1):
    """Apply professional header styling."""
    header_fill = PatternFill(start_color='4472C4', fill_type='solid')
    header_font = Font(bold=True, color='FFFFFF', size=11)
    header_border = Border(bottom=Side(style='medium', color='000000'))

    for cell in ws[row]:
        cell.fill = header_fill
        cell.font = header_font
        cell.border = header_border
        cell.alignment = Alignment(horizontal='center', vertical='center')

Alternating Row Colors

def style_alternating_rows(ws, start_row=2, end_row=100):
    """Apply zebra striping."""
    light_fill = PatternFill(start_color='F2F2F2', fill_type='solid')

    for row in range(start_row, end_row + 1):
        if row % 2 == 0:
            for cell in ws[row]:
                cell.fill = light_fill

Currency Column

def style_currency_column(ws, col, start_row=2, end_row=100):
    """Format column as currency with conditional colors."""
    for row in range(start_row, end_row + 1):
        cell = ws.cell(row=row, column=col)
        cell.number_format = '$#,##0.00'

        if cell.value and cell.value < 0:
            cell.font = Font(color='FF0000')

---

Color Reference

Excel Theme Colors

Status Colors

Excel Formulas Reference

Comprehensive formula patterns for spreadsheet generation with openpyxl and ExcelJS.

---

Basic Formulas

---

Conditional Formulas

SUMIF / SUMIFS

# openpyxl - Single condition
ws['F1'] = '=SUMIF(A:A,"Product A",B:B)'

# Multiple conditions
ws['F2'] = '=SUMIFS(C:C,A:A,"Region1",B:B,">100")'

// ExcelJS
sheet.getCell('F1').value = { formula: 'SUMIF(A:A,"Product A",B:B)' };

COUNTIF / COUNTIFS

AVERAGEIF

# Average sales for specific product
ws['G1'] = '=AVERAGEIF(A:A,"Widget",C:C)'

---

Lookup Formulas

VLOOKUP

# Syntax: VLOOKUP(lookup_value, table_array, col_index, [range_lookup])
ws['D2'] = '=VLOOKUP(A2,Products!A:C,3,FALSE)'

Parameters:

• lookup_value: Value to find
• table_array: Range containing data
• col_index: Column number to return (1-based)
• range_lookup: FALSE for exact match

XLOOKUP (Excel 365+)

# More flexible than VLOOKUP
ws['D2'] = '=XLOOKUP(A2,Products!A:A,Products!C:C,"Not Found")'

INDEX/MATCH (Most Flexible)

# Syntax: INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
ws['D2'] = '=INDEX(C:C,MATCH(A2,A:A,0))'

Advantages over VLOOKUP:

• Can look left (not just right)
• More performant on large datasets
• Column insertions don't break formula

---

Date Formulas

Date Calculations

# Days until deadline
ws['C2'] = '=B2-TODAY()'

# Age calculation
ws['D2'] = '=DATEDIF(A2,TODAY(),"Y")'

# Next month same day
ws['E2'] = '=EDATE(A2,1)'

---

Text Formulas

Text Extraction

# Extract domain from email
ws['B2'] = '=MID(A2,FIND("@",A2)+1,100)'

# First name from full name
ws['C2'] = '=LEFT(A2,FIND(" ",A2)-1)'

---

Logical Formulas

IF Statements

# Simple IF
ws['C2'] = '=IF(B2>100,"High","Low")'

# Nested IF
ws['C2'] = '=IF(B2>100,"High",IF(B2>50,"Medium","Low"))'

# IFS (Excel 365+)
ws['C2'] = '=IFS(B2>100,"High",B2>50,"Medium",TRUE,"Low")'

AND / OR

# Multiple conditions
ws['D2'] = '=IF(AND(B2>100,C2="Active"),"Priority","Normal")'
ws['E2'] = '=IF(OR(B2>1000,C2="VIP"),"Premium","Standard")'

IFERROR

# Handle division by zero, lookup failures
ws['F2'] = '=IFERROR(A2/B2,0)'
ws['G2'] = '=IFERROR(VLOOKUP(A2,Data!A:B,2,FALSE),"Not Found")'

---

Financial Formulas

---

Array Formulas (Dynamic Arrays)

FILTER

# Filter rows where column B > 100
ws['E1'] = '=FILTER(A:C,B:B>100,"No results")'

UNIQUE

# Get unique values
ws['F1'] = '=UNIQUE(A:A)'

SORT

# Sort by column, descending
ws['G1'] = '=SORT(A1:C100,2,-1)'

SEQUENCE

# Generate number sequence
ws['A1'] = '=SEQUENCE(10,1,1,1)'  # 1 to 10

---

Implementation Patterns

openpyxl (Python)

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# Add formula
ws['C2'] = '=A2*B2'

# Named ranges in formulas
wb.defined_names.add('SalesData', 'Sheet1!$A$1:$C$100')
ws['D1'] = '=SUM(SalesData)'

# Array formula (legacy)
ws['E1'] = '=SUM(A1:A10*B1:B10)'
ws['E1'].data_type = 'a'  # Mark as array formula

ExcelJS (Node.js)

import ExcelJS from 'exceljs';

const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('Data');

// Simple formula
sheet.getCell('C2').value = { formula: 'A2*B2' };

// Formula with result hint
sheet.getCell('D2').value = {
  formula: 'SUM(A:A)',
  result: 1000  // Optional: cached result
};

// Shared formula (efficient for repeated formulas)
sheet.getCell('C2').value = { sharedFormula: 'A2*B2' };
for (let row = 3; row <= 100; row++) {
  sheet.getCell(`C${row}`).value = { sharedFormula: 'C2' };
}

---

Common Pitfalls

---

Performance Tips

1. Avoid volatile functions in large sheets: NOW(), TODAY(), RAND(), INDIRECT() 2. Use structured references with Tables instead of A1 notation 3. Prefer XLOOKUP/INDEX-MATCH over VLOOKUP for large datasets 4. Limit whole-column references (A:A) when possible 5. Use helper columns instead of complex nested formulas

Excel Pivot Tables and Summary Data Reference

Patterns for generating pivot-style summaries using Python and Node.js libraries.

---

Contents

• Library limitations for native pivot tables
• pandas pivot_table to Excel workflow
• xlwings native pivot table creation
• Summary table patterns (cross-tab, running totals, YoY)
• Structuring data for pivot-readiness
• Do / Avoid

---

Library Limitations

If the runtime has no Excel installation, generate pre-computed summary tables instead.

---

pandas pivot_table to Excel

import pandas as pd

df = pd.DataFrame({
    "Region": ["East", "East", "West", "West", "East", "West"],
    "Product": ["A", "B", "A", "B", "A", "B"],
    "Revenue": [100, 200, 150, 250, 120, 180],
    "Units": [10, 20, 15, 25, 12, 18],
})

# Single aggregation with grand totals
summary = pd.pivot_table(
    df, values="Revenue", index="Region", columns="Product",
    aggfunc="sum", margins=True, margins_name="Total"
)

# Multiple aggregations
detail = pd.pivot_table(
    df, values=["Revenue", "Units"], index="Region", columns="Product",
    aggfunc={"Revenue": "sum", "Units": "mean"}, fill_value=0
)

with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="Summary")
    detail.to_excel(writer, sheet_name="Detail")
    df.to_excel(writer, sheet_name="Raw Data", index=False)

---

xlwings Native Pivot (Requires Excel)

import xlwings as xw

app = xw.App(visible=False)
wb = app.books.open("data.xlsx")
ws_data = wb.sheets["Raw Data"]
ws_pivot = wb.sheets.add("PivotReport")

src = ws_data.range("A1").expand()
pt = wb.api.PivotCaches().Create(
    SourceType=1, SourceData=src.api
).CreatePivotTable(
    TableDestination=ws_pivot.range("A3").api, TableName="SalesPivot"
)
pt.PivotFields("Region").Orientation = 1   # xlRowField
pt.PivotFields("Product").Orientation = 2  # xlColumnField
pt.AddDataField(pt.PivotFields("Revenue"), "Sum of Revenue", -4157)

wb.save("report_with_pivot.xlsx")
wb.close()
app.quit()

Not suitable for headless Linux CI -- COM/AppleScript bridge required.

---

Summary Table Patterns

# Cross-tab
cross = pd.crosstab(df["Region"], df["Product"],
                     values=df["Revenue"], aggfunc="sum", margins=True)

# Running totals
monthly = df.groupby("Month")["Revenue"].sum().reset_index()
monthly["Cumulative"] = monthly["Revenue"].cumsum()

# Year-over-year comparison
yoy = df.pivot_table(values="Revenue", index="Month", columns="Year", aggfunc="sum")
yoy["YoY Change"] = yoy[2025] - yoy[2024]
yoy["YoY %"] = ((yoy[2025] - yoy[2024]) / yoy[2024] * 100).round(1)

---

Structuring Data for Pivot-Readiness

Pivots require tidy, flat data. Verify these properties before generating:

1. One header row -- no merged cells in the header 2. Every column has a unique, non-empty name 3. No blank rows or columns within the data block 4. Consistent types per column (no mixed text/numbers) 5. Dates stored as date objects, not strings 6. No subtotals or totals mixed into data rows

df.columns = df.columns.str.strip()
df = df.dropna(how="all")
df["Date"] = pd.to_datetime(df["Date"], errors="coerce")
df["Amount"] = pd.to_numeric(df["Amount"], errors="coerce").fillna(0)

---

When Native Pivots vs Pre-Computed

---

Do / Avoid

Do:

• Include a "Raw Data" sheet so recipients can build their own pivots
• Use margins=True in pandas to add Grand Total rows/columns
• Format summary output with openpyxl styles after writing
• Name the data range as an Excel Table for self-expanding pivot sources
• Validate aggregation results against source totals before saving

Avoid:

• Merged cells in pivot source data (breaks field detection)
• Multi-level column headers from pandas MultiIndex without flattening
• Assuming openpyxl or XlsxWriter can create native pivot tables
• Leaving NaN in numeric columns (use fill_value=0)
• Running xlwings in CI without a licensed Excel installation

Excel Security and Protection Reference

Sheet protection, cell locking, and injection prevention for generated spreadsheets.

---

Contents

• Sheet protection (openpyxl, ExcelJS) and workbook structure protection
• Cell locking patterns and password limitations
• Formula injection prevention and hidden sheets
• Do / Avoid and pre-distribution checklist

---

Sheet Protection

openpyxl

from openpyxl.worksheet.protection import SheetProtection

ws.protection = SheetProtection(
    sheet=True, password="review2025",
    formatCells=False, insertRows=False, deleteRows=False,
    sort=True, autoFilter=True,
    selectLockedCells=True, selectUnlockedCells=True
)

ExcelJS

await worksheet.protect('review2025', {
  selectLockedCells: true, selectUnlockedCells: true,
  formatCells: false, insertRows: false, deleteRows: false,
  sort: true, autoFilter: true
});

---

Workbook Protection

Prevents adding, deleting, renaming, or reordering sheets. Does not protect cell contents.

wb.security.workbookPassword = "struct2025"
wb.security.lockStructure = True

ExcelJS has no native workbook protection API. Use a pre-protected template.

---

Cell Locking Patterns

All cells default to "locked" in Excel, but locking activates only when the sheet is protected.

from openpyxl.styles import Protection

# Unlock input cells
for row in ws.iter_rows(min_row=2, max_row=200, min_col=2, max_col=4):
    for cell in row:
        cell.protection = Protection(locked=False)

# Lock and hide formula cells (hidden=True hides from formula bar)
for row in ws.iter_rows(min_row=2, max_row=200, min_col=5, max_col=8):
    for cell in row:
        cell.protection = Protection(locked=True, hidden=True)

ws.protection.sheet = True
ws.protection.password = "edit2025"

// ExcelJS equivalent
for (let r = 2; r <= 200; r++) {
  for (let c = 2; c <= 4; c++)
    worksheet.getCell(r, c).protection = { locked: false };
  for (let c = 5; c <= 8; c++)
    worksheet.getCell(r, c).protection = { locked: true, hidden: true };
}
await worksheet.protect('edit2025');

---

Password Limitations

Sheet/workbook protection passwords are not encryption. They are a UI deterrent only.

import msoffcrypto
with open("report.xlsx", "rb") as f:
    file = msoffcrypto.OfficeFile(f)
    file.load_key(password="Str0ngP@ss!")
    with open("report_encrypted.xlsx", "wb") as out:
        file.encrypt("Str0ngP@ss!", out)

---

Formula Injection Prevention

User-supplied strings can trigger formula execution when written to cells.

Dangerous Prefixes

=, +, -, @, \t (tab), \r (carriage return)

Sanitization

DANGEROUS_PREFIXES = ("=", "+", "-", "@", "\t", "\r", "\n")

def sanitize_cell_value(value):
    if isinstance(value, str) and value.startswith(DANGEROUS_PREFIXES):
        return "'" + value   # leading quote forces text interpretation
    return value

const DANGEROUS = /^[=+\-@\t\r\n]/;
function sanitize(v: unknown): unknown {
  return typeof v === 'string' && DANGEROUS.test(v) ? "'" + v : v;
}

The leading single quote is not displayed in the cell.

---

Hidden Sheets for Audit Trails

ws_meta = wb.create_sheet("_Audit")
ws_meta.sheet_state = "veryHidden"  # only accessible via VBA editor
ws_meta["A1"], ws_meta["B1"] = "Generated", datetime.now().isoformat()
ws_meta["A2"], ws_meta["B2"] = "Source Hash", data_hash

hidden = users can unhide via right-click. veryHidden = requires VBA or XML editing.

Do / Avoid

Do: sanitize all user-supplied strings before cell writes. Use file-level AES encryption for sensitive data. Unlock only specific input ranges. Hide formulas in protected sheets. Document editable cells on an Instructions sheet.

Avoid: relying on sheet protection passwords as a security boundary. Writing raw user input without injection checks. Protecting sheets without setting locked/unlocked patterns first. Storing secrets or PII in cells, even on hidden sheets.

---

Checklist: Pre-Distribution Security Review

• [ ] User-supplied values pass through injection sanitization
• [ ] Input cells unlocked; all others locked; sheet protection enabled
• [ ] Formula cells have hidden=True if logic is confidential
• [ ] Workbook structure protection is on
• [ ] File-level encryption applied if data is sensitive or regulated
• [ ] Hidden sheets contain no credentials or tokens
• [ ] Tested in Excel, LibreOffice, and Google Sheets