xlsx-creator

name: xlsx-creator description: > Create, edit, and analyze Excel spreadsheets (.xlsx, .xlsm, .csv, .tsv files). Covers building spreadsheets with openpyxl and pandas, writing Excel formulas, applying professional formatting and color conventions, creating charts, and validating formula correctness. Use when the user wants to create an Excel file, analyze spreadsheet data, build a financial model, generate a report as XLSX, or edit an existing spreadsheet. license: Apache-2.0 metadata: author: anthropics version: "1.0" tags: [excel, xlsx, spreadsheet, openpyxl, pandas, formulas, financial-model, data] platforms: [claude-code, cursor, windsurf, any] triggers: - create an Excel file - build a spreadsheet - write Excel formulas - create an XLSX file - analyze spreadsheet data - financial model - generate a report in Excel - edit a CSV - openpyxl - pandas to Excel - Excel chart

Excel Spreadsheet Skill (XLSX)

Professional Standards

• Zero formula errors - no #REF!, #DIV/0!, #VALUE!, #N/A, or #NAME? errors allowed
• Always use formulas instead of calculating values in Python and hardcoding them - spreadsheets must remain dynamic
• Consistent fonts throughout - pick one font family (Calibri 11 or Aptos 11 for Excel defaults)
• Document hardcoded values with comments including source, date, and reference URL

Tool Selection

Step 1: Setup

pip install openpyxl pandas xlsxwriter

Step 2: Create a Spreadsheet with openpyxl

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers
from openpyxl.utils import get_column_letter
from openpyxl.chart import BarChart, Reference

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

# ─── STYLING HELPERS ─────────────────────────────────────────────────────────
HEADER_FONT = Font(name="Calibri", bold=True, size=11, color="FFFFFF")
HEADER_FILL = PatternFill(fill_type="solid", fgColor="1F4E79")  # dark blue
INPUT_FILL = PatternFill(fill_type="solid", fgColor="DDEEFF")   # light blue (inputs)
FORMULA_FILL = PatternFill(fill_type="solid", fgColor="FFFFFF")  # white (formulas)
ASSUMPTION_FILL = PatternFill(fill_type="solid", fgColor="FFFF99") # yellow (key assumptions)

thin = Side(style="thin", color="000000")
BORDER = Border(left=thin, right=thin, top=thin, bottom=thin)

def style_header(cell):
    cell.font = HEADER_FONT
    cell.fill = HEADER_FILL
    cell.alignment = Alignment(horizontal="center", vertical="center")
    cell.border = BORDER

def style_input(cell):
    cell.fill = INPUT_FILL  # blue = hardcoded input (industry standard)
    cell.border = BORDER

def style_formula(cell):
    cell.fill = FORMULA_FILL  # white/black = formula
    cell.border = BORDER

# ─── HEADERS ─────────────────────────────────────────────────────────────────
headers = ["Month", "Units Sold", "Unit Price ($)", "Revenue ($)", "COGS ($)", "Gross Profit ($)"]
for col, h in enumerate(headers, start=1):
    cell = ws.cell(row=1, column=col, value=h)
    style_header(cell)
    ws.column_dimensions[get_column_letter(col)].width = max(len(h) + 2, 14)

# ─── INPUT DATA ──────────────────────────────────────────────────────────────
months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun"]
units = [1200, 1350, 1280, 1500, 1420, 1650]
price = 29.99
cogs_pct = 0.40  # key assumption - put in assumption row

for i, (month, unit) in enumerate(zip(months, units), start=2):
    row = i
    # Month
    ws.cell(row=row, column=1, value=month)
    # Units (input - blue)
    cell_units = ws.cell(row=row, column=2, value=unit)
    style_input(cell_units)
    # Unit Price (input - blue)
    cell_price = ws.cell(row=row, column=3, value=price)
    cell_price.number_format = '"$"#,##0.00'
    style_input(cell_price)
    # Revenue (formula - always use formula, not Python calculation)
    cell_rev = ws.cell(row=row, column=4, value=f"=B{row}*C{row}")
    cell_rev.number_format = '"$"#,##0.00'
    style_formula(cell_rev)
    # COGS (formula referencing assumption)
    # Assume COGS % is in cell H2
    cell_cogs = ws.cell(row=row, column=5, value=f"=D{row}*$H$2")
    cell_cogs.number_format = '"$"#,##0.00'
    style_formula(cell_cogs)
    # Gross Profit (formula)
    cell_gp = ws.cell(row=row, column=6, value=f"=D{row}-E{row}")
    cell_gp.number_format = '"$"#,##0.00'
    style_formula(cell_gp)

# ─── ASSUMPTIONS BOX ─────────────────────────────────────────────────────────
# Yellow = key assumptions
ws["G1"] = "Key Assumptions"
ws["G1"].font = Font(bold=True)
ws["G2"] = "COGS % of Revenue"
ws["H2"] = cogs_pct
ws["H2"].number_format = "0%"
ws["H2"].comment = "Source: Management estimate, April 2026"
cell_h2 = ws["H2"]
cell_h2.fill = ASSUMPTION_FILL  # yellow = key assumption

# ─── TOTALS ROW ──────────────────────────────────────────────────────────────
total_row = len(months) + 2
ws.cell(row=total_row, column=1, value="TOTAL")
ws.cell(row=total_row, column=1).font = Font(bold=True)
for col in range(2, 7):
    col_letter = get_column_letter(col)
    formula = f"=SUM({col_letter}2:{col_letter}{total_row-1})"
    cell = ws.cell(row=total_row, column=col, value=formula)
    if col >= 4:
        cell.number_format = '"$"#,##0.00'
    cell.font = Font(bold=True)
    cell.border = BORDER

# ─── CHART ───────────────────────────────────────────────────────────────────
chart = BarChart()
chart.title = "Monthly Revenue"
chart.style = 10
chart.y_axis.title = "Revenue ($)"
chart.x_axis.title = "Month"

data = Reference(ws, min_col=4, min_row=1, max_row=len(months) + 1)
categories = Reference(ws, min_col=1, min_row=2, max_row=len(months) + 1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.shape = 4
ws.add_chart(chart, "A10")

# ─── SAVE ────────────────────────────────────────────────────────────────────
wb.save("revenue-model.xlsx")
print("Saved: revenue-model.xlsx")

Step 3: Read / Analyze Existing Spreadsheets with pandas

import pandas as pd

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

# Or CSV
df = pd.read_csv("data.csv")

# Analysis
print(df.describe())
print(df.groupby("category")["revenue"].sum())

# Write back to Excel with formatting
with pd.ExcelWriter("output.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Data", index=False)
    summary = df.groupby("category").agg({"revenue": ["sum", "mean", "count"]})
    summary.to_excel(writer, sheet_name="Summary")

Financial Model Color Conventions (Industry Standard)

Formula Best Practices

# ✅ Always write Excel formulas - NOT Python calculations
cell.value = "=B2*C2"           # revenue formula
cell.value = "=SUM(D2:D13)"     # total
cell.value = "=IFERROR(D2/E2,0)" # safe division

# ❌ Never hardcode computed values
cell.value = 35988              # static - breaks when inputs change

Lock references for constants:

# $H$2 = absolute reference to assumption cell
cell.value = f"=D{row}*$H$2"   # COGS % stays fixed as formula copies down

Common Mistakes

• Hardcoding calculated values - always use Excel formulas; Python is for setup only
• Missing number_format - always set "$#,##0.00" for currency, "0%" for percentages
• Not validating formula results - open the file and check for #REF!, #VALUE!, #DIV/0!
• Inconsistent fonts - pick one font and use it throughout
• No documentation for assumptions - add cell.comment with source and date for hardcoded numbers
• Absolute vs relative references - use $A$1 when copying formulas that reference fixed cells