Excel XLSX

Excel / XLSX Skill

Overview

Expert skill for creating professional Excel spreadsheets. Core principle: enrich visuals while ensuring content clarity. Every visual element should serve the content, not decorate it.

When to Use

• Creating Excel reports with formatting
• Data analysis and manipulation
• Financial reports and dashboards
• Converting data to/from Excel
• Multi-sheet workbooks
• Formulas and calculations

Dependencies

pip install openpyxl pandas xlsxwriter

Part 1: User Needs & Feature Matching

Before creating any Excel, think through what the user needs and match features to user value.

Help Users Understand Data

Help Users Find What Matters

Help Users Save Time

Help Users Use Directly

Help Users Trust the Data

Help Users Gain Insights

Part 2: Four-Layer Implementation

Layer 1: Structure

Sheet Organization

Layout Rules

Standalone Text Rows

Use standalone text rows (outside tables) for:

• Document title
• Section headers
• KEY INSIGHTS text
• Source attribution and generation date
• Notes and definitions

Do NOT put these inside data tables. They break filters and sorting.

Navigation (Sheet Index)

For workbooks with 3+ sheets, add a clickable index on the Overview sheet:

def add_sheet_index(wb, overview_ws, start_row=5, col=2):
    """Add clickable sheet index to Overview sheet."""
    overview_ws.cell(row=start_row, column=col, value="Sheet Index")
    overview_ws.cell(row=start_row, column=col).font = Font(
        name='Source Serif Pro', bold=True, size=13
    )
    for i, sheet_name in enumerate(wb.sheetnames):
        row = start_row + 1 + i
        cell = overview_ws.cell(row=row, column=col, value=sheet_name)
        cell.hyperlink = f"#'{sheet_name}'!A1"
        cell.font = Font(name='Source Sans Pro', color="0563C1", underline="single", size=11)

Layer 2: Information

Number Formats

CRITICAL: Every numeric cell MUST have number_format set, including formula result cells.

# WRONG: formula cell has no format
cell = ws.cell(row=10, column=3)
cell.value = "=SUM(C2:C9)"

# CORRECT: formula cell has explicit format
cell = ws.cell(row=10, column=3)
cell.value = "=SUM(C2:C9)"
cell.number_format = '#,##0.00'

Data Context

Always include these metadata elements, typically on the Overview sheet:

KEY INSIGHTS Section

Add to the Overview sheet, below the title and before detailed data:

def add_key_insights(ws, insights: list[str], start_row=3, col=2):
    """Add KEY INSIGHTS section to a worksheet."""
    cell = ws.cell(row=start_row, column=col, value="KEY INSIGHTS")
    cell.font = Font(name='Source Serif Pro', bold=True, size=12, color="1A1A1A")

    for i, insight in enumerate(insights):
        row = start_row + 1 + i
        cell = ws.cell(row=row, column=col, value=f"  {insight}")
        cell.font = Font(name='Source Sans Pro', size=11, color="333333")

Content Completeness

Before finalizing, verify:

• All numeric cells have number_format
• All tables have headers
• Source and date are present
• No empty columns in the middle of data
• Totals/summaries where expected

Layer 3: Visual

Essential Setup

Apply to every worksheet before adding content:

def setup_worksheet(ws):
    """Essential setup for every worksheet."""
    ws.sheet_view.showGridLines = False       # Hide gridlines
    ws.column_dimensions['A'].width = 3       # Left margin

Theme System

12 built-in themes. Default: Elegant Black. Choose based on context.

THEMES = {
    "elegant_black": {
        "header_bg": "1A1A1A",
        "header_text": "FFFFFF",
        "accent": "2C2C2C",
        "alt_row": "F5F5F5",
        "border": "D0D0D0",
        "chart_colors": ["1A1A1A", "5B5B5B", "8C8C8C", "B0B0B0", "D4D4D4", "E8E8E8"],
    },
    "corporate_blue": {
        "header_bg": "1F4E79",
        "header_text": "FFFFFF",
        "accent": "2E75B6",
        "alt_row": "D6E4F0",
        "border": "9DC3E6",
        "chart_colors": ["1F4E79", "2E75B6", "5B9BD5", "9DC3E6", "BDD7EE", "DEEBF7"],
    },
    "forest_green": {
        "header_bg": "1B5E20",
        "header_text": "FFFFFF",
        "accent": "2E7D32",
        "alt_row": "E8F5E9",
        "border": "A5D6A7",
        "chart_colors": ["1B5E20", "2E7D32", "43A047", "66BB6A", "81C784", "A5D6A7"],
    },
    "burgundy": {
        "header_bg": "7B1F3A",
        "header_text": "FFFFFF",
        "accent": "A0153E",
        "alt_row": "FCE4EC",
        "border": "E57373",
        "chart_colors": ["7B1F3A", "A0153E", "C62828", "E53935", "EF5350", "E57373"],
    },
    "slate_gray": {
        "header_bg": "37474F",
        "header_text": "FFFFFF",
        "accent": "546E7A",
        "alt_row": "ECEFF1",
        "border": "B0BEC5",
        "chart_colors": ["37474F", "546E7A", "78909C", "90A4AE", "B0BEC5", "CFD8DC"],
    },
    "navy": {
        "header_bg": "0D1B2A",
        "header_text": "FFFFFF",
        "accent": "1B3A5C",
        "alt_row": "E3F2FD",
        "border": "90CAF9",
        "chart_colors": ["0D1B2A", "1B3A5C", "1565C0", "1E88E5", "42A5F5", "90CAF9"],
    },
    "charcoal": {
        "header_bg": "2C2C2C",
        "header_text": "FFFFFF",
        "accent": "404040",
        "alt_row": "F5F5F5",
        "border": "BDBDBD",
        "chart_colors": ["2C2C2C", "404040", "616161", "757575", "9E9E9E", "BDBDBD"],
    },
    "deep_purple": {
        "header_bg": "311B92",
        "header_text": "FFFFFF",
        "accent": "4527A0",
        "alt_row": "EDE7F6",
        "border": "B39DDB",
        "chart_colors": ["311B92", "4527A0", "512DA8", "673AB7", "7E57C2", "B39DDB"],
    },
    "teal": {
        "header_bg": "004D40",
        "header_text": "FFFFFF",
        "accent": "00695C",
        "alt_row": "E0F2F1",
        "border": "80CBC4",
        "chart_colors": ["004D40", "00695C", "00897B", "009688", "26A69A", "80CBC4"],
    },
    "warm_brown": {
        "header_bg": "3E2723",
        "header_text": "FFFFFF",
        "accent": "5D4037",
        "alt_row": "EFEBE9",
        "border": "BCAAA4",
        "chart_colors": ["3E2723", "5D4037", "6D4C41", "795548", "8D6E63", "BCAAA4"],
    },
    "royal_blue": {
        "header_bg": "1A237E",
        "header_text": "FFFFFF",
        "accent": "283593",
        "alt_row": "E8EAF6",
        "border": "9FA8DA",
        "chart_colors": ["1A237E", "283593", "303F9F", "3949AB", "5C6BC0", "9FA8DA"],
    },
    "olive": {
        "header_bg": "33691E",
        "header_text": "FFFFFF",
        "accent": "558B2F",
        "alt_row": "F1F8E9",
        "border": "AED581",
        "chart_colors": ["33691E", "558B2F", "689F38", "7CB342", "8BC34A", "AED581"],
    },
}

def get_theme(name: str = "elegant_black") -> dict:
    """Get theme configuration by name."""
    return THEMES.get(name, THEMES["elegant_black"])

How Theme Colors Apply

Semantic Colors (Theme-Independent)

Row Highlight Colors

Typography

Use a serif + sans-serif font pairing. Serif for headers and titles (authority, structure), sans-serif for data and body text (readability).

Recommended Pairings:

Typography Hierarchy:

Data Block Definition

A "Data Block" is any rectangular range of cells that forms a logical unit: a table, a summary block, a key metrics panel. Identify data blocks by:

• Contiguous cells with related content
• Common header row
• Consistent column structure

Border Rules (Horizontal-Only Style)

Use horizontal-only borders within data blocks. This creates a clean, modern look.

from openpyxl.styles import Border, Side

def apply_data_block_borders(ws, min_row, max_row, min_col, max_col, theme_name="elegant_black"):
    """Apply horizontal-only border style to a data block.

    Args:
        ws: Worksheet object
        min_row: First row of the block (header row)
        max_row: Last row of the block
        min_col: First column of the block
        max_col: Last column of the block
        theme_name: Theme to use for border color
    """
    theme = get_theme(theme_name)
    border_color = theme["border"]

    thin = Side(style='thin', color=border_color)
    medium = Side(style='medium', color=border_color)
    no_side = Side(style=None)

    for row in range(min_row, max_row + 1):
        for col in range(min_col, max_col + 1):
            cell = ws.cell(row=row, column=col)

            # Determine border sides
            top = thin if row == min_row else no_side
            left = thin if col == min_col else no_side
            right = thin if col == max_col else no_side

            if row == min_row:
                # Header row: medium bottom
                bottom = medium
            elif row == max_row:
                # Last row: thin bottom (outer frame)
                bottom = thin
            else:
                # Internal rows: thin bottom only
                bottom = thin

            cell.border = Border(top=top, bottom=bottom, left=left, right=right)

Alignment Rules

Column Width

Calculate based on content length with padding:

def calculate_column_width(ws, col_idx, min_width=8, max_width=50, padding=3):
    """Calculate optimal column width based on content.

    Formula: max_content_length + padding, clamped to [min_width, max_width].
    """
    max_length = 0
    for row in ws.iter_rows(min_col=col_idx, max_col=col_idx):
        for cell in row:
            if cell.value:
                cell_length = len(str(cell.value))
                # Bold text needs ~10% more space
                if cell.font and cell.font.bold:
                    cell_length = int(cell_length * 1.1)
                max_length = max(max_length, cell_length)

    width = max_length + padding
    return max(min_width, min(width, max_width))

Row Heights

Merge Cells Guidelines

• DO merge for document titles spanning the content area
• DO merge for section headers
• DO NOT merge within data tables (breaks sorting and filtering)
• When merging, always set alignment to center-left or center

Data Visualization

Data Bars: Add to numeric columns for in-cell comparison:

from openpyxl.formatting.rule import DataBarRule

rule = DataBarRule(start_type='min', end_type='max',
                   color="5B9BD5", showValue=True)
ws.conditional_formatting.add("C2:C100", rule)

Color Scale: For heatmap effect on dense data:

from openpyxl.formatting.rule import ColorScaleRule

rule = ColorScaleRule(
    start_type='min', start_color='63BE7B',
    mid_type='percentile', mid_value=50, mid_color='FFEB84',
    end_type='max', end_color='F8696B'
)
ws.conditional_formatting.add("B2:F20", rule)

Charts: Always use theme chart_colors for series:

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

theme = get_theme("corporate_blue")
# Apply chart_colors to chart series
for i, series in enumerate(chart.series):
    color_idx = i % len(theme["chart_colors"])
    series.graphicalProperties.solidFill = theme["chart_colors"][color_idx]

Layer 4: Interaction

Freeze Panes

Apply when table has more than 10 rows:

# Freeze below header row (row 1 = header)
ws.freeze_panes = 'A2'

# If content starts at B5 with header in row 5
ws.freeze_panes = 'B6'

Filters

Apply when table has more than 20 rows:

ws.auto_filter.ref = f"B5:{get_column_letter(max_col)}{max_row}"

Hyperlinks

# Internal link (to another sheet)
cell.hyperlink = "#'Detail Sheet'!A1"
cell.font = Font(color="0563C1", underline="single")

# External link
cell.hyperlink = "https://example.com/report"
cell.font = Font(color="0563C1", underline="single")

Pre-sorting Rules

Editability Guidelines

• Mark input cells with yellow background (FFFDE7)
• Lock all non-input cells with sheet protection
• Add data validation dropdowns for constrained inputs
• Include instructions for editable fields

Core Operations

1. Create New Workbook

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.chart import BarChart, PieChart, LineChart, Reference
import pandas as pd

def create_styled_workbook(data: dict, output_path: str, theme_name: str = "elegant_black"):
    """Create a professionally styled Excel workbook using the theme system."""
    theme = get_theme(theme_name)
    wb = Workbook()
    ws = wb.active
    ws.title = "Report"
    setup_worksheet(ws)

    # Styles from theme
    header_font = Font(
        name='Source Serif Pro', bold=True,
        color=theme["header_text"], size=11
    )
    header_fill = PatternFill(
        start_color=theme["header_bg"],
        end_color=theme["header_bg"],
        fill_type="solid"
    )
    data_font = Font(name='Source Sans Pro', size=11, color="1A1A1A")

    # Write headers
    headers = list(data.keys())
    for col, header in enumerate(headers, 2):  # Start at col B
        cell = ws.cell(row=2, column=col, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal='center')

    # Write data
    max_rows = max(len(v) for v in data.values())
    alt_fill = PatternFill(start_color=theme["alt_row"], end_color=theme["alt_row"], fill_type="solid")

    for row in range(max_rows):
        for col, header in enumerate(headers, 2):
            values = data[header]
            value = values[row] if row < len(values) else ""
            cell = ws.cell(row=row + 3, column=col, value=value)
            cell.font = data_font
            if row % 2 == 1:
                cell.fill = alt_fill

    # Apply borders
    apply_data_block_borders(ws, 2, max_rows + 2, 2, len(headers) + 1, theme_name)

    # Auto-adjust column widths
    for col_idx in range(2, len(headers) + 2):
        width = calculate_column_width(ws, col_idx)
        ws.column_dimensions[ws.cell(row=2, column=col_idx).column_letter].width = width

    wb.save(output_path)
    return output_path

2. Read Excel File

import pandas as pd

def read_excel_smart(file_path: str, sheet_name=None):
    """Read Excel file with automatic type detection."""
    if sheet_name is None:
        xl = pd.ExcelFile(file_path)
        sheets = {name: pd.read_excel(xl, sheet_name=name) for name in xl.sheet_names}
        return sheets

    return pd.read_excel(file_path, sheet_name=sheet_name)

def read_excel_with_headers(file_path: str, header_row: int = 0):
    """Read Excel with specified header row."""
    return pd.read_excel(file_path, header=header_row)

3. Add Charts

from openpyxl.chart import BarChart, LineChart, PieChart, Reference

def add_bar_chart(ws, data_range: str, title: str, position: str = "E2"):
    """Add a bar chart to worksheet."""
    chart = BarChart()
    chart.type = "col"
    chart.title = title
    chart.style = 10

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

    ws.add_chart(chart, position)

def add_pie_chart(ws, labels_col: int, data_col: int,
                  min_row: int, max_row: int, title: str, position: str = "E2"):
    """Add a pie chart."""
    chart = PieChart()
    chart.title = title

    labels = Reference(ws, min_col=labels_col, min_row=min_row, max_row=max_row)
    data = Reference(ws, min_col=data_col, min_row=min_row-1, max_row=max_row)

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

    ws.add_chart(chart, position)

def add_line_chart(ws, data_range: str, title: str, position: str = "E2"):
    """Add a line chart."""
    chart = LineChart()
    chart.title = title
    chart.style = 10
    chart.y_axis.title = "Value"
    chart.x_axis.title = "Period"

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

    ws.add_chart(chart, position)

4. Formulas & Calculations

def add_formulas(ws, start_row: int, end_row: int, formula_col: int,
                 data_col: int, formula_type: str = "SUM"):
    """Add formulas to a column. Always set number_format on formula cells."""
    col_letter = ws.cell(row=1, column=data_col).column_letter

    if formula_type == "SUM":
        cell = ws.cell(row=end_row+1, column=formula_col)
        cell.value = f"=SUM({col_letter}{start_row}:{col_letter}{end_row})"
        cell.number_format = '#,##0.00'
    elif formula_type == "AVERAGE":
        cell = ws.cell(row=end_row+1, column=formula_col)
        cell.value = f"=AVERAGE({col_letter}{start_row}:{col_letter}{end_row})"
        cell.number_format = '#,##0.00'
    elif formula_type == "ROW_CALC":
        for row in range(start_row, end_row+1):
            cell = ws.cell(row=row, column=formula_col)
            cell.value = f"=A{row}*B{row}"  # Customize formula
            cell.number_format = '#,##0.00'

def add_conditional_formatting(ws, cell_range: str, threshold: float):
    """Add conditional formatting with color scale and formula-based rules."""
    from openpyxl.formatting.rule import ColorScaleRule, FormulaRule
    from openpyxl.styles import PatternFill

    # Color scale (green to red)
    rule = ColorScaleRule(
        start_type='min', start_color='63BE7B',
        mid_type='percentile', mid_value=50, mid_color='FFEB84',
        end_type='max', end_color='F8696B'
    )
    ws.conditional_formatting.add(cell_range, rule)

    # Formula-based rule
    red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
    rule = FormulaRule(formula=[f'A1>{threshold}'], fill=red_fill)
    ws.conditional_formatting.add(cell_range, rule)

5. DataFrame to Excel with Styling

def dataframe_to_styled_excel(df: pd.DataFrame, output_path: str,
                               sheet_name: str = "Data",
                               theme_name: str = "elegant_black"):
    """Convert DataFrame to styled Excel using the theme system."""
    theme = get_theme(theme_name)

    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name=sheet_name, index=False, startrow=1, startcol=1)

        workbook = writer.book
        worksheet = writer.sheets[sheet_name]
        setup_worksheet(worksheet)

        # Style headers
        header_font = Font(name='Source Serif Pro', bold=True, color=theme["header_text"], size=11)
        header_fill = PatternFill(start_color=theme["header_bg"], fill_type="solid")

        for col_num, column_title in enumerate(df.columns, 2):
            cell = worksheet.cell(row=2, column=col_num)
            cell.font = header_font
            cell.fill = header_fill

        # Style data cells
        data_font = Font(name='Source Sans Pro', size=11)
        for row in range(3, len(df) + 3):
            for col in range(2, len(df.columns) + 2):
                cell = worksheet.cell(row=row, column=col)
                cell.font = data_font

        # Auto-fit columns
        for col_idx in range(2, len(df.columns) + 2):
            width = calculate_column_width(worksheet, col_idx)
            col_letter = worksheet.cell(row=2, column=col_idx).column_letter
            worksheet.column_dimensions[col_letter].width = width

    return output_path

6. Multi-Sheet Report

def create_multi_sheet_report(data_dict: dict, output_path: str,
                               theme_name: str = "elegant_black"):
    """Create workbook with multiple themed sheets and an Overview index."""
    theme = get_theme(theme_name)
    wb = Workbook()

    # Create Overview sheet
    overview = wb.active
    overview.title = "Overview"
    setup_worksheet(overview)

    overview.cell(row=2, column=2, value="Report Overview")
    overview.cell(row=2, column=2).font = Font(
        name='Source Serif Pro', bold=True, size=18, color=theme["header_bg"]
    )

    # Create data sheets
    for sheet_name, df in data_dict.items():
        ws = wb.create_sheet(title=sheet_name[:31])  # Excel limit: 31 chars
        setup_worksheet(ws)

        # Write DataFrame
        for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=True), 2):
            for c_idx, value in enumerate(row, 2):
                ws.cell(row=r_idx, column=c_idx, value=value)

        # Style header row
        for col in range(2, len(df.columns) + 2):
            cell = ws.cell(row=2, column=col)
            cell.font = Font(name='Source Serif Pro', bold=True, color=theme["header_text"])
            cell.fill = PatternFill(start_color=theme["header_bg"], fill_type="solid")

        # Style data with sans-serif font
        for row in range(3, len(df) + 3):
            for col in range(2, len(df.columns) + 2):
                ws.cell(row=row, column=col).font = Font(name='Source Sans Pro', size=11)

    # Add sheet index to Overview
    add_sheet_index(wb, overview, start_row=5, col=2)

    wb.save(output_path)
    return output_path

Quick Reference

Common Patterns

Financial Report

# Create P&L statement with theme
data = {
    "Category": ["Revenue", "COGS", "Gross Profit", "OpEx", "Net Income"],
    "Q1": [100000, 40000, 60000, 30000, 30000],
    "Q2": [120000, 48000, 72000, 35000, 37000],
    "Q3": [110000, 44000, 66000, 32000, 34000],
    "Q4": [130000, 52000, 78000, 38000, 40000]
}
create_styled_workbook(data, "financial_report.xlsx", theme_name="navy")

Data Dashboard

# Summary statistics sheet + detail sheet + charts
sheets = {
    "Summary": summary_df,
    "Details": detail_df,
}
create_multi_sheet_report(sheets, "dashboard.xlsx", theme_name="corporate_blue")

Tips

1. Always close workbooks after use
2. Use with statement for ExcelWriter
3. Limit sheet names to 31 characters
4. Use openpyxl for .xlsx, xlrd for .xls (read-only)
5. For large files, use write_only mode
6. Always apply a theme -- default to elegant_black for formal reports
7. Use apply_data_block_borders() on every data table for consistent horizontal-only borders
8. Set number_format on EVERY numeric cell, including formula results
9. Pair serif fonts (titles) with sans-serif fonts (data) for professional typography
10. Add KEY INSIGHTS section on Overview sheet so users get value immediately