domains / documents / spreadsheet-editing / xlsx

xlsx

Skill card

combined

The anthropics/skills copy is the upstream, most complete procedure: it defines the tool-selection table, the mandatory recalc.py contract (JSON status, exit-code semantics, external-link loss, and the _xlfn. prefix rules), and the 'follow the user's spec literally' constraint. The other three copies (zLanqing, foryourhealth111-pixel, Prat011) reproduce the financial-modeling requirements (color coding, number formatting, formula construction rules) and the pandas/openpyxl workflow, but they omit the recalc.py JSON contract and the formula-compatibility rules, and they add material steps the upstream copy lacks (e.g., foryourhealth111-pixel's scientific-schematics section). Crowning one copy would hide either the recalculation contract or the financial-modeling conventions, so the copies must be combined.

Repositories

Other copies

Combined skill

# XLSX creation, editing, and analysis

## Tool selection

| Task | Approach |
|---|---|
| Create or edit with formulas/formatting | openpyxl |
| Bulk data in or out | pandas (read_excel, to_excel) |
| Quick look at a sheet | markitdown file.xlsx (no cell coordinates; do not plan edits from it) |
| Read a model (formulas and values) | two load_workbook passes |

openpyxl, pandas, and markitdown are preinstalled. Do not run pip install first; write the script and import directly. Only if an import fails, pip install the missing package.

## Requirements for every output

- Professional font (Arial, Times New Roman) throughout, unless the user says otherwise.
- Zero formula errors. Never ship while recalc.py reports errors_found. If you think an error predates you, prove it: load the original with data_only=True and look at that cell.
- Use formulas, never hardcoded results. Write sheet['B10'] = '=SUM(B2:B9)', not the Python-computed total.
- Follow the user's spec literally. Exact tab names, exact column headers, and the formula they spelled out.
- Document every assumption and hardcoded number where the reader will see it — a cell comment, or an adjacent cell at a table's end. Cite a real source when one exists; when the number came from the user, say so plainly.
- A workbook you create for someone to fill in needs a short legend naming which cells to edit, and one example row of realistic values. Never add such a row to a file you were asked to edit.
- Editing an existing file: match its conventions exactly. They override every guideline here. Find its designated input cells first — a distinct font color, fill, or shading marks them — write only there, and leave every existing formula untouched.

## Financial-modeling conventions

### Color coding
- 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 the 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
- 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
- Place all assumptions (growth rates, margins, multiples, etc.) in separate assumption cells.
- Use cell references instead of hardcoded values in formulas. Example: use =B5*(1+$B$6) instead of =B5*1.05.
- Verify all cell references are correct.
- Check for off-by-one errors in ranges.
- Ensure consistent formulas across all projection periods.
- Test with edge cases (zero values, negative numbers).
- Verify no unintended circular references.

### Documentation for hardcodes
- Comment or in cells beside (if end of table). Format: "Source: [System/Document], [Date], [Specific Reference], [URL if applicable]".
- Examples:
  - "Source: Company 10-K, FY2024, Page 45, Revenue Note, [SEC EDGAR URL]"
  - "Source: Company 10-Q, Q2 2025, Exhibit 99.1, [SEC EDGAR URL]"
  - "Source: Bloomberg Terminal, 8/15/2025, AAPL US Equity"
  - "Source: FactSet, 8/20/2025, Consensus Estimates Screen"

## Recalculate (mandatory whenever the file contains formulas)

openpyxl writes formulas as strings with no cached values. Until you recalculate, every formula cell reads back as None to anything reading cached values — pandas, load_workbook(data_only=True), and most previewers.

```bash
python scripts/recalc.py output.xlsx [timeout_seconds]   # default 30
```

LibreOffice computes every formula, the file is rewritten in place, and you get JSON: status (success | errors_found), total_formulas, total_errors, and an error_summary naming up to 100 cells per error type (locations_truncated says how many it withheld — trust total_errors, not the length of the list). Fix what it names and run it again. JSON with an error key instead of a status means nothing was recalculated, and only that case exits non-zero — errors_found exits 0, so never treat a clean exit as a clean workbook.

A green recalc proves your formulas evaluate, not that they are right. An off-by-one range or a reference to the wrong row yields a clean, error-free file with wrong numbers. Write 2–3 formulas first and check they pull the values you expect, before building out a grid.

A workbook that links to another file loses those links if you re-save it with openpyxl and then recalculate. Such a formula reads ='[1]Returns Analysis'!$B$2 — the [1] is an index into the workbook's external-reference list, naming a separate file on disk, not a sheet. That file is rarely present here, so the cell's cached value is the only thing holding its data. openpyxl strips that value on save; LibreOffice then has to resolve the reference for real, fails, writes #NAME?, and deletes every link. recalc.py refuses to run in that state — copy those cells' values out of the original before you save over them (--force overrides, and accepts the loss).

## Choosing formulas that survive verification

LibreOffice implements fewer functions than Excel, and one it cannot evaluate becomes a literal #NAME? baked into the file you deliver.

- Prefer Excel-2007-era functions — SUMIFS, INDEX, MATCH, IFERROR, SUMPRODUCT — which need no prefix.
- Six post-2007 functions work, but only with an _xlfn. prefix, because openpyxl writes your formula into the XML verbatim and Excel stores post-2007 names prefixed (its UI hides the prefix): _xlfn.TEXTJOIN, _xlfn.CONCAT, _xlfn.IFS, _xlfn.SWITCH, _xlfn.MAXIFS, _xlfn.MINIFS. Written bare, each yields #NAME?.
- Never use XLOOKUP, XMATCH, SORT, FILTER, UNIQUE, or SEQUENCE. The runtime's LibreOffice cannot evaluate them under any prefix.

## 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): use the recalc.py script.
6. Verify and fix any errors: the script returns JSON with error details. If status is errors_found, check error_summary for specific error types and locations. Fix the identified errors and recalculate again. Common errors to fix: #REF! (invalid cell references), #DIV/0! (division by zero), #VALUE! (wrong data type in formula), #NAME? (unrecognized formula name).