XLSX

Excel / Spreadsheet Skill

Professional Standards

• Consistent, professional font (Arial, Times New Roman)
• Zero formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?)
• Preserve existing template formats when updating files

Financial Model Color Coding

• Blue text: Hardcoded inputs
• Black text: Formulas and calculations
• Green text: Internal worksheet links
• Red text: External file links
• Yellow background: Key assumptions

Number Formatting

• Years as text ("2024")
• Currency with units in headers ("Revenue ($mm)")
• Zeros displayed as "-"
• Percentages at 0.0%
• Negative numbers in parentheses (123)

CRITICAL: Use Formulas, Not Hardcoded Values

Always use Excel formulas instead of calculating values and hardcoding them.

WRONG

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

CORRECT

sheet['B10'] = '=SUM(B2:B9)'

Tool Selection

pandas — Data analysis and bulk operations

import pandas as pd
df = pd.read_excel('file.xlsx')
df.to_excel('output.xlsx', index=False)

openpyxl — Complex formatting and formulas

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

wb = Workbook()
sheet = wb.active
sheet['A1'] = 'Hello'
sheet['B2'] = '=SUM(A1:A10)'
sheet['A1'].font = Font(bold=True, color='FF0000')
sheet.column_dimensions['A'].width = 20
wb.save('output.xlsx')

SheetJS (JavaScript — used in this project)

import * as XLSX from 'xlsx'

// Export data to Excel
const ws = XLSX.utils.json_to_sheet(data)
const wb = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(wb, ws, 'Laporan')
XLSX.writeFile(wb, 'laporan.xlsx')

// Read Excel
const workbook = XLSX.read(buffer, { type: 'buffer' })
const sheet = workbook.Sheets[workbook.SheetNames[0]]
const jsonData = XLSX.utils.sheet_to_json(sheet)

Editing Existing Files

from openpyxl import load_workbook

wb = load_workbook('existing.xlsx')
sheet = wb.active
sheet['A1'] = 'New Value'
sheet.insert_rows(2)
wb.save('modified.xlsx')

Best Practices

• Cell indices are 1-based in openpyxl
• Use data_only=True to read calculated values
• Warning: data_only=True + save = formulas permanently lost
• For large files: read_only=True or write_only=True
• Always verify formulas with edge cases (zero, negative values)
• Document data sources for hardcoded values