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