XLSX Creation, Editing, and Analysis

Use this skill any time a spreadsheet file is the primary input or output: opening, reading, editing, creating .xlsx/.csv/.tsv files, adding columns, computing formulas, formatting, charting, or cleaning messy data.

Requirements for Outputs

All Excel Files

• Professional Font: Use Arial or Times New Roman unless instructed otherwise
• Zero Formula Errors: Every model MUST have ZERO formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?)
• Preserve Existing Templates: When modifying files, EXACTLY match existing format and conventions

Financial Models

Color Coding Standards

• Blue text (0,0,255): Hardcoded inputs
• Black text (0,0,0): ALL formulas and calculations
• Green text (0,128,0): Links from other worksheets
• Red text (255,0,0): External links
• Yellow background (255,255,0): Key assumptions needing attention

Number Formatting

• Years: Format as text strings ("2024" not "2,024")
• Currency: Use $#,##0 format; specify units in headers ("Revenue ($mm)")
• Zeros: Format to show "-"
• Percentages: Default 0.0% (one decimal)
• Multiples: 0.0x for valuation multiples
• Negative numbers: Use parentheses (123) not -123

Reading and Analyzing Data

Data Analysis with pandas

import pandas as pd

df = pd.read_excel('file.xlsx')
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)

df.head()
df.info()
df.describe()

df.to_excel('output.xlsx', index=False)

CRITICAL: Use Formulas, Not Hardcoded Values

Always use Excel formulas instead of calculating in Python and hardcoding them.

WRONG

total = df['Sales'].sum()
sheet['B10'] = total  # Hardcodes 5000

CORRECT

sheet['B10'] = '=SUM(B2:B9)'
sheet['C5'] = '=(C4-C2)/C2'
sheet['D20'] = '=AVERAGE(D2:D19)'

Common Workflow

1. Choose tool: pandas for data, openpyxl for formulas/formatting
2. Create/Load: Create new workbook or load exis...