# XLSX

> Use this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .csv, or .tsv file; create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path and wants something done to it or produced from it. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, or Google Sheets API integration.

- Skill: `jr2804/xlsx` (Agent Skill, multi-file: 53 files)
- Install (CLI): `npx skillmds@latest add jr2804/xlsx`
- Raw SKILL.md: https://api.skillmd.com/api/skills/jr2804/xlsx/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Web & Frontend
- License: Proprietary. LICENSE.txt has complete terms
- Author: jr2804 (https://skillmd.com/u/jr2804)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/jr2804/xlsx

---


# Requirements for Outputs

## All Excel files

### Professional Font

- Use a consistent, professional font (e.g., Arial, Times New Roman) for all deliverables unless otherwise instructed by the user

### Zero Formula Errors

- Every Excel model MUST be delivered with ZERO formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?)

### Preserve Existing Templates (when updating templates)

- Study and EXACTLY match existing format, style, and conventions when modifying files
- Never impose standardized formatting on files with established patterns
- Existing template conventions ALWAYS override these guidelines

## Financial models

### Color Coding Standards

Unless otherwise stated by the user or existing template

- **Blue text (RGB: 0,0,255)**: Hardcoded inputs, and numbers users will change for scenarios
- **Black text (RGB: 0,0,0)**: ALL formulas and calculations
- **Green text (RGB: 0,128,0)**: Links pulling from other worksheets within same workbook
- **Red text (RGB: 255,0,0)**: External links to other files
- **Yellow background (RGB: 255,255,0)**: Key assumptions needing attention or cells that need to be updated

### Number Formatting Standards

- **Years**: Format as text strings (e.g., "2024" not "2,024")
- **Currency**: Use $#,##0 format; ALWAYS specify units in headers ("Revenue ($mm)")
- **Zeros**: Use number formatting to make all zeros "-", including percentages (e.g., "$#,##0;($#,##0);-")
- **Percentages**: Default to 0.0% format (one decimal)
- **Multiples**: Format as 0.0x for valuation multiples (EV/EBITDA, P/E)
- **Negative numbers**: Use parentheses (123) not minus -123

### Formula Construction Rules

- Place ALL assumptions in separate assumption cells
- Use cell references instead of hardcoded values in formulas
- Example: Use =B5\*(1+$B$6) instead of =B5\*1.05

## XLSX creation, editing, and analysis

### Overview

A user may ask you to create, edit, or analyze the contents of an .xlsx file.

### Reading and analyzing data

```python
import pandas as pd

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

### CRITICAL: Use Formulas, Not Hardcoded Values

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

#### ❌ WRONG

```python
total = df["Sales"].sum()
sheet["B10"] = total  # Hardcodes 5000
```

#### ✅ CORRECT

```python
sheet["B10"] = "=SUM(B2:B9)"
```

### Common Workflow

1. **Choose tool**: pandas for data, openpyxl for formulas/formatting
2. **Create/Load**: Create new workbook or load existing file
3. **Modify**: Add/edit data, formulas, and formatting
4. **Save**: Write to file
5. **Recalculate formulas (MANDATORY IF USING FORMULAS)**:

   ```bash
   python scripts/recalc.py output.xlsx
   ```

6. **Verify and fix any errors**

#### Creating new Excel files

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

wb = Workbook()
sheet = wb.active
sheet["A1"] = "Hello"
sheet["B1"] = "World"
sheet["B2"] = "=SUM(A1:A10)"
sheet["A1"].font = Font(bold=True, color="FF0000")
wb.save("output.xlsx")
```

#### Editing existing Excel files

```python
from openpyxl import load_workbook

wb = load_workbook("existing.xlsx")
sheet = wb.active
sheet["A1"] = "New Value"
wb.save("modified.xlsx")
```

### Recalculating formulas

```bash
python scripts/recalc.py <excel_file> [timeout_seconds]
```

The script:

- Automatically sets up LibreOffice macro on first run
- Recalculates all formulas in all sheets
- Scans ALL cells for Excel errors
- Returns JSON with detailed error locations and counts

### Formula Verification Checklist

- [ ] **Test 2-3 sample references**: Verify they pull correct values
- [ ] **Column mapping**: Confirm Excel columns match (e.g., column 64 = BL)
- [ ] **Row offset**: Excel rows are 1-indexed (DataFrame row 5 = Excel row 6)
- [ ] **NaN handling**: Check for null values with `pd.notna()`
- [ ] **Division by zero**: Check denominators before using `/`

### Best Practices

#### Library Selection

- **pandas**: Best for data analysis, bulk operations, and simple data export
- **openpyxl**: Best for complex formatting, formulas, and Excel-specific features

#### Working with openpyxl

- Cell indices are 1-based
- Use `data_only=True` to read calculated values
- **Warning**: If opened with `data_only=True` and saved, formulas are permanently lost
- Formulas are preserved but not evaluated — use `scripts/recalc.py` to update values

### Code Style Guidelines

- Write minimal, concise Python code without unnecessary comments
- Avoid verbose variable names and redundant operations
- For Excel files: add comments to cells with complex formulas, document data sources for hardcoded values

### Dependencies

- **pandas**: Data analysis and manipulation
- **openpyxl**: Excel file creation and editing with formulas/formatting
- **LibreOffice**: Formula recalculation (via `scripts/soffice.py`)