xlsx-creator

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

Use Case	Tool
Data analysis, bulk operations, DataFrame export	pandas
Complex formatting, formulas, cell styling	openpyxl
Both formatting AND data manipulation	Use pandas to build data, openpyxl to format

---

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")