Create with openpyxl

wb = Workbook() ws = wb.active ws["A1"] = "Hello" ws["B1"] = "World" wb.save("output.xlsx")


## When to Use

- Reading and analyzing Excel data with pandas
- Creating formatted spreadsheets programmatically
- Building financial models with formulas
- Generating reports with charts and graphs
- Automating data entry and updates
- Converting between Excel and other formats
- Batch processing multiple spreadsheets
- Creating templates for repeated use

## Requirements for All Output

- **Zero formula errors mandatory**
- Preserve existing templates when updating
- Always use Excel formulas instead of calculating values in Python and hardcoding them
- Follow industry-standard color coding for financial models

### Financial Model Color Standards
- **Blue**: Input cells (hardcoded values)
- **Black**: Formula cells (calculated values)
- **Green**: Links to other worksheets
- **Red**: Links to external files

### Number Formatting Rules
- Years displayed as text (no commas)
- Currency with appropriate units (K, M, B)
- Zeros displayed as "-"

## Reading Excel Files

### With Pandas
```python
import pandas as pd

# Read entire file
df = pd.read_excel("data.xlsx")

# Read specific sheet
df = pd.read_excel("data.xlsx", sheet_name="Sheet2")

# Read with options
df = pd.read_excel(
    "data.xlsx",
    sheet_name=0,
    header=0,
    usecols="A:D",
    skiprows=2
)

# Read all sheets
dfs = pd.read_excel("data.xlsx", sheet_name=None)
for sheet_name, df in dfs.items():
    print(f"Sheet: {sheet_name}")
    print(df.head())

With Openpyxl

from openpyxl import load_workbook

wb = load_workbook("data.xlsx")
ws = wb.active

# Read cell values
for row in ws.iter_rows(min_row=1, max_row=10, values_only=True):
    print(row)

# Read specific cell
value = ws["A1"].value

Creating Excel Files

With Pandas

import pandas as pd

df = pd.DataFrame({
    'Name': ['Alice', 'Bob', 'Charlie'],
    'Sales': [1000, 1500, 1200],
    'Region': ['East', 'West', 'North']
})

# Simple export
df.to_excel("output.xlsx", index=False)

# Multiple sheets
with pd.ExcelWriter("output.xlsx") as writer:
    df.to_excel(writer, sheet_name="Sales", index=False)
    df.to_excel(writer, sheet_name="Backup", index=False)

With Openpyxl (Formulas)

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

wb = Workbook()
ws = wb.active
ws.title = "Financial Model"

# Headers
headers = ["Item", "Q1", "Q2", "Q3", "Q4", "Total"]
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True)
    cell.alignment = Alignment(horizontal="center")

# Data with formulas
data = [
    ("Revenue", 1000, 1200, 1100, 1400),
    ("Expenses", 800, 900, 850, 1000),
]

for row, (item, *values) in enumerate(data, 2):
    ws.cell(row=row, column=1, value=item)
    for col, value in enumerate(values, 2):
        ws.cell(row=row, column=col, value=value)
    # Total formula
    ws.cell(row=row, column=6, value=f"=SUM(B{row}:E{row})")

# Profit row with formula
ws.cell(row=4, column=1, value="Profit")
for col in range(2, 7):
    col_letter = get_column_letter(col)
    ws.cell(row=4, column=col, value=f"={col_letter}2-{col_letter}3")

wb.save("financial_model.xlsx")

Formatting

Cell Styles

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.styles.numbers import FORMAT_CURRENCY_USD_SIMPLE

wb = Workbook()
ws = wb.active

# Font styling
ws["A1"] = "Styled Cell"
ws["A1"].font = Font(
    name="Arial",
    size=14,
    bold=True,
    italic=False,
    color="2E74B5"
)

# Fill color
ws["A2"] = "Blue Background"
ws["A2"].fill = PatternFill(
    start_color="0000FF",
    end_color="0000FF",
    fill_type="solid"
)

# Number format
ws["A3"] = 1234567.89
ws["A3"].number_format = FORMAT_CURRENCY_USD_SIMPLE

# Alignment
ws["A4"] = "Centered"
ws["A4"].alignment = Alignment(
    horizontal="center",
    vertical="center"
)

# Borders
thin_border = Border(
    left=Side(style="thin"),
    right=Side(style="thin"),
    top=Side(style="thin"),
    bottom=Side(style="thin")
)
ws["A5"].border = thin_border

wb.save("formatted.xlsx")

Column Width and Row Height

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# Set column width
ws.column_dimensions["A"].width = 20
ws.column_dimensions["B"].width = 15

# Set row height
ws.row_dimensions[1].height = 30

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

wb.save("sized.xlsx")

Charts

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

wb = Workbook()
ws = wb.active

# Data
data = [
    ["Month", "Sales"],
    ["Jan", 100],
    ["Feb", 120],
    ["Mar", 140],
    ["Apr", 110],
]
for row in data:
    ws.append(row)

# Create chart
chart = BarChart()
chart.type = "col"
chart.title = "Monthly Sales"
chart.y_axis.title = "Sales ($)"
chart.x_axis.title = "Month"

data = Reference(ws, min_col=2, min_row=1, max_row=5)
cats = Reference(ws, min_col=1, min_row=2, max_row=5)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 4  # Rectangle

ws.add_chart(chart, "D2")
wb.save("with_chart.xlsx")

Formula Verification

Check for Errors

from openpyxl import load_workbook

wb = load_workbook("model.xlsx", data_only=False)
ws = wb.active

errors = ["#REF!", "#DIV/0!", "#VALUE!", "#N/A", "#NAME?", "#NULL!", "#NUM!"]

for row in ws.iter_rows():
    for cell in row:
        if cell.value and isinstance(cell.value, str):
            for error in errors:
                if error in str(cell.value):
                    print(f"Error {error} in cell {cell.coordinate}")

Validate Formulas

Before saving, always:

Check cell references are correct
Avoid off-by-one errors
Test edge cases (empty cells, zeros)
Verify formula logic

Execution Checklist

Choose appropriate tool (pandas vs openpyxl)
Verify input file exists and is valid
Test formulas with sample data
Apply consistent formatting
Validate all formulas produce correct results
Check for Excel errors (#REF!, #DIV/0!, etc.)
Verify charts display correctly
Test in Excel/LibreOffice before delivery

Error Handling

Common Errors

Error: InvalidFileException

Cause: File is not a valid .xlsx (possibly .xls)
Solution: Convert to .xlsx or use xlrd for .xls files

Error: Circular reference

Cause: Formula references itself
Solution: Review formula logic and break the cycle

Error: #REF! in formulas

Cause: Cell reference is invalid (deleted row/column)
Solution: Use named ranges or validate references

Error: Memory issues with large files

Cause: Loading entire file into memory
Solution: Use read_only=True or write_only=True mode

Metrics

Metric	Typical Value
Read speed (pandas)	~50,000 rows/second
Write speed (pandas)	~30,000 rows/second
Formula cells	~10,000 cells/second
Chart creation	~5 charts/second
Memory usage	~100MB per 100K rows

Workflow

Choose appropriate tool (pandas or openpyxl)
Create or load workbook
Modify as needed
Save file
For formula-based files: Run formula recalculation
Verify and fix errors

Quick Reference

Task	Tool
Data analysis	pandas
Simple read/write	pandas
Formulas	openpyxl
Formatting	openpyxl
Charts	openpyxl
Pivot tables	Use Excel or xlwings

Dependencies

pip install pandas openpyxl xlrd

Optional:

xlwings (Windows/Mac Excel automation)
xlsxwriter (alternative writer)

Version History

1.1.0 (2026-01-02): Added Quick Start, When to Use, Execution Checklist, Error Handling, Metrics sections; updated frontmatter with version, category, related_skills
1.0.0 (2024-10-15): Initial release with pandas, openpyxl, financial model standards

xlsx

XLSX Processing Skill

Overview

Quick Start