excel-skill

---
name: excel-skill
description: Generate Excel workbooks (.xlsx) with industry-specific templates for finance, FMCG, e-commerce, and internet/SaaS scenarios. Use this skill when the user wants to (1) create an Excel template with formulas, formatting, and charts; (2) build a dashboard or report; (3) analyze data with pivot tables or Power Query; (4) automate Excel generation in Python via openpyxl; (5) audit AI-generated openpyxl code for common pitfalls (formula-as-string, data_only flag misuse). Provides progressive-disclosure access to formulas, pivot tables, Power Query, charts, openpyxl, pandas-excel, and 4 industry template libraries. Chinese-friendly. Designed to make AI-generated Excel code paste-and-run.
license: MIT
---

# Excel Skill

Generate Excel files / formulas / pivot tables / dashboards correctly the first time.

## When to Use This Skill

Yes:
- Creating .xlsx files with formulas, formatting, validation, charts
- Industry-specific templates: finance (DCF, three-statement), FMCG (sales vs target, RTM), e-commerce (GMV, RFM, funnel), internet (DAU/MAU, LTV/CAC, A/B test)
- Pivot tables and Power Query workflows
- Python automation of Excel generation (openpyxl, pandas-to-excel)
- Auditing AI-generated openpyxl code for common pitfalls
- Recalculating formulas in openpyxl-generated files (via LibreOffice macro)
- Style / formatting standards for professional reports

No:
- Real-time data integration (use Power BI / Tableau / Metabase instead)
- Complex statistical modeling (use Python pandas / R directly, then export)
- Pure data wrangling without Excel deliverable (use pandas)

## Detect User Intent First

| Pattern | Trigger keywords | Action |
|---|---|---|
| **Generate template** | "做一个/帮我生成 X 模板" / "create template for Y" | Pick from `templates/` matching industry, run `scripts/generate_*.py` |
| **Formula question** | "怎么写 X 公式" / "VLOOKUP / INDEX / SUMIFS" | Load `references/01-formulas-cheatsheet.md` |
| **Pivot table** | "透视表 / pivot table / 数据汇总" | Load `references/02-pivot-tables.md` |
| **Power Query** | "Power Query / 数据清洗 / M 语言" | Load `references/03-power-query.md` |
| **Dashboard / chart** | "Dashboard / 仪表盘 / 图表 / 配色" | Load `references/07-charts-and-formatting.md` |
| **Python openpyxl** | Mentions Python / openpyxl / 写脚本 | Load `references/05-openpyxl-python.md` |
| **pandas + Excel pitfalls** | "pandas read_excel / to_excel" / 公式变字符串 | Load `references/08-excel-pitfalls.md`（含 "formula gets stringified" 坑） |
| **VBA vs Python** | "VBA / 宏 / Macro / 该用 VBA 还是 Python" | Load `references/04-vba-vs-python.md` |
| **Cell protection / validation** | "锁单元格 / 数据校验 / 下拉" | Load `references/06-cell-protection.md` |
| **Code review** | User pastes openpyxl code | Run `scripts/excel_lint.py` |
| **Audit existing file** | "体检 / 诊断这份 Excel / 有没有坑" | Run `scripts/analyze.py file.xlsx` |

## Industry Routing (templates)

| User describes | Industry | Template directory |
|---|---|---|
| 财报 / 三表 / DCF / ROE / 投资组合 | Finance | `templates/finance/` |
| 销售达成 / RTM / 经销商 / SKU ABC / 促销 ROI | FMCG | `templates/fmcg/` |
| GMV / 转化率 / 客单价 / RFM / ROAS | E-commerce | `templates/ecommerce/` |
| DAU / MAU / 留存 / LTV / CAC / 漏斗 / A/B 测试 | Internet | `templates/internet/` |

If unsure, **ask which industry first**. Same metric (e.g., "转化率") means different things in e-commerce vs internet/SaaS.

## Things That Must Survive

When generating openpyxl code, NEVER lose:

1. **`data_only=True` misuse**: Loading with `data_only=True` then saving = formulas permanently lost. Only use for read-only analysis.
2. **Formulas in openpyxl are strings**: After `ws['A1'] = '=SUM(B1:B10)'`, the file has the formula but no calculated value. Tell the user to either:
   - Open in Excel (auto-recalc on open)
   - Run `python scripts/recalc.py file.xlsx` (uses LibreOffice macro)
3. **Number format must match value**: A 0.15 cell with format `'0.00%'` displays "15.00%". A "15.00%" string with default format displays as text.
4. **Locale in 公式**: Chinese Excel uses `；` not `,` as separator? **No.** Chinese Excel still uses `,` in formulas — `；` is the LIST_SEPARATOR for some MS Office locales but openpyxl always uses `,`.
5. **A1 vs RC**: Always use A1 notation in formulas. Do NOT generate `R1C1` style.
6. **Sheet name special characters**: Sheet names cannot contain `[ ] : * ? / \ '`, must be ≤31 chars.
7. **Chart references**: Charts need `Reference(ws, min_col, min_row, max_col, max_row)`. Forgetting `min_col` defaults to 1, often wrong.

## Default Workflow for "Make me an Excel"

```
1. Detect industry (finance / FMCG / e-commerce / internet)
2. Detect deliverable type (template / dashboard / analysis / report)
3. Find matching template script under templates/
4. Ask 2-3 clarifying questions:
   - 数据规模（行数、字段名）
   - 是否要图表 / Dashboard
   - 输出文件名 + 公式是否需要重算
5. Run / generate script, output .xlsx
6. If formulas were used: tell user about recalc.py or auto-recalc-on-open
7. If user gives code: run excel_lint.py mentally, point out pitfalls
```

## Files

| File | Role |
|---|---|
| `README.md` | Project intro |
| `WORKFLOW.md` | Cross-cutting workflow (clean → analyze → visualize → deliver) |
| `INSTALL_CN.md` | Chinese install + usage |
| `references/` | 8 progressive-disclosure knowledge files (formulas, pivot, Power Query, VBA-vs-Python, openpyxl, cell-protection, charts-and-formatting, pitfalls) |
| `templates/{finance,fmcg,ecommerce,internet,saas}/` | Output dirs for generated .xlsx (not pre-committed binaries) |
| `scripts/excel_lint.py` | Audit openpyxl **code** for common pitfalls (XL001…) |
| `scripts/analyze.py` | Audit an existing **.xlsx file** for health issues (XA001…XA010) |
| `scripts/recalc.py` | Recalculate formula caches via LibreOffice headless (requires LibreOffice installed) |
| `scripts/generate_*/` | 5 industry template generators (one script per vertical) |
| `scripts/helpers/` | Reusable styling / formula helpers |
| `tutorials/` | Beginner tutorial |
| `examples/` | End-to-end case study (finance DCF walkthrough) |

This skill is at version **0.1.0** as of 2026-04-19. Most templates and references are in active development. See PROJECT_PLAN.md for build sequence.