xlsx

XLSX Skill

Decision Tree

Task	Tool
Data analysis, bulk operations	pandas
Formulas, formatting, styles	openpyxl
High-performance chart creation	xlsxwriter
Read calculated values (no formulas)	openpyxl with data_only=True

CRITICAL: Use Excel Formulas, Not Hardcoded Values

# ❌ WRONG — calculating in Python
total = df['Sales'].sum()
sheet['B10'] = total

# ✅ CORRECT — let Excel calculate
sheet['B10'] = '=SUM(B2:B9)'
sheet['C5'] = '=(C4-C2)/C2'
sheet['D20'] = '=AVERAGE(D2:D19)'

Always use Excel formulas so spreadsheets remain dynamic and updateable.

Reading & Analyzing

pandas (data analysis)

import pandas as pd

df = pd.read_excel('file.xlsx')                          # first sheet
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)  # all sheets as dict

df.describe()                         # statistics
df.groupby('Category')['Amount'].sum()  # aggregation
df.to_excel('output.xlsx', index=False)

openpyxl (preserve formulas/formatting)

from openpyxl import load_workbook

wb = load_workbook('file.xlsx')
sheet = wb.active
for row in sheet.iter_rows(min_row=2, values_only=True):
    print(row)

Creating New Spreadsheets

openpyxl with formulas and formatting

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, numbers

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

# Headers
headers = ["Quarter", "Revenue", "COGS", "Gross Profit", "Margin"]
for col, h in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=h)
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="333333")
    cell.alignment = Alignment(horizontal="center")

# Data with formulas
quarters = ["Q1", "Q2", "Q3", "Q4"]
revenues = [120000, 145000, 168000, 192000]
cogs_pct = 0.35

for i, (q, rev) in enumerate(zip(quarters, revenues), 2):
    ws.cell(row=i, column=1, value=q)
    ws.cell(row=i, column=2, value=rev).number_format = '$#,##0'
    ws.cell(row=i, column=3).value = f'=B{i}*{cogs_pct}'
    ws.cell(row=i, column=3).number_format = '$#,##0'
    ws.cell(row=i, column=4).value = f'=B{i}-C{i}'
    ws.cell(row=i, column=4).number_format = '$#,##0'
    ws.cell(row=i, column=5).value = f'=D{i}/B{i}'
    ws.cell(row=i, column=5).number_format = '0.0%'

# Totals row
total_row = len(quarters) + 2
ws.cell(row=total_row, column=1, value="Total").font = Font(bold=True)
for col in [2, 3, 4]:
    cell = ws.cell(row=total_row, column=col)
    cell.value = f'=SUM({chr(64+col)}2:{chr(64+col)}{total_row-1})'
    cell.font = Font(bold=True)
    cell.number_format = '$#,##0'

# Column widths
for col_letter, width in [("A", 12), ("B", 15), ("C", 15), ("D", 15), ("E", 12)]:
    ws.column_dimensions[col_letter].width = width

wb.save("revenue_model.xlsx")