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:
data_only=True misuse: Loading with data_only=True then saving = formulas permanently lost. Only use for read-only analysis.- 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)
- 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.
- 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 ,.
- A1 vs RC: Always use A1 notation in formulas. Do NOT generate
R1C1 style.
- Sheet name special characters: Sheet names cannot contain
[ ] : * ? / \ ', must be ≤31 chars.
- 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.
1---2name: excel-skill3description: 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.4---56# Excel Skill78Generate Excel files / formulas / pivot tables / dashboards correctly the first time.910## When to Use This Skill1112Yes:13- Creating .xlsx files with formulas, formatting, validation, charts14- 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)15- Pivot tables and Power Query workflows16- Python automation of Excel generation (openpyxl, pandas-to-excel)17- Auditing AI-generated openpyxl code for common pitfalls18- Recalculating formulas in openpyxl-generated files (via LibreOffice macro)19- Style / formatting standards for professional reports2021No:22- Real-time data integration (use Power BI / Tableau / Metabase instead)23- Complex statistical modeling (use Python pandas / R directly, then export)24- Pure data wrangling without Excel deliverable (use pandas)2526## Detect User Intent First2728| Pattern | Trigger keywords | Action |29|---|---|---|30| **Generate template** | "做一个/帮我生成 X 模板" / "create template for Y" | Pick from `templates/` matching industry, run `scripts/generate_*.py` |31| **Formula question** | "怎么写 X 公式" / "VLOOKUP / INDEX / SUMIFS" | Load `references/01-formulas-cheatsheet.md` |32| **Pivot table** | "透视表 / pivot table / 数据汇总" | Load `references/02-pivot-tables.md` |33| **Power Query** | "Power Query / 数据清洗 / M 语言" | Load `references/03-power-query.md` |34| **Dashboard / chart** | "Dashboard / 仪表盘 / 图表 / 配色" | Load `references/07-charts-and-formatting.md` |35| **Python openpyxl** | Mentions Python / openpyxl / 写脚本 | Load `references/05-openpyxl-python.md` |36| **pandas + Excel pitfalls** | "pandas read_excel / to_excel" / 公式变字符串 | Load `references/08-excel-pitfalls.md`（含 "formula gets stringified" 坑） |37| **VBA vs Python** | "VBA / 宏 / Macro / 该用 VBA 还是 Python" | Load `references/04-vba-vs-python.md` |38| **Cell protection / validation** | "锁单元格 / 数据校验 / 下拉" | Load `references/06-cell-protection.md` |39| **Code review** | User pastes openpyxl code | Run `scripts/excel_lint.py` |40| **Audit existing file** | "体检 / 诊断这份 Excel / 有没有坑" | Run `scripts/analyze.py file.xlsx` |4142## Industry Routing (templates)4344| User describes | Industry | Template directory |45|---|---|---|46| 财报 / 三表 / DCF / ROE / 投资组合 | Finance | `templates/finance/` |47| 销售达成 / RTM / 经销商 / SKU ABC / 促销 ROI | FMCG | `templates/fmcg/` |48| GMV / 转化率 / 客单价 / RFM / ROAS | E-commerce | `templates/ecommerce/` |49| DAU / MAU / 留存 / LTV / CAC / 漏斗 / A/B 测试 | Internet | `templates/internet/` |5051If unsure, **ask which industry first**. Same metric (e.g., "转化率") means different things in e-commerce vs internet/SaaS.5253## Things That Must Survive5455When generating openpyxl code, NEVER lose:56571. **`data_only=True` misuse**: Loading with `data_only=True` then saving = formulas permanently lost. Only use for read-only analysis.582. **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:59 - Open in Excel (auto-recalc on open)60 - Run `python scripts/recalc.py file.xlsx` (uses LibreOffice macro)613. **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.624. **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 `,`.635. **A1 vs RC**: Always use A1 notation in formulas. Do NOT generate `R1C1` style.646. **Sheet name special characters**: Sheet names cannot contain `[ ] : * ? / \ '`, must be ≤31 chars.657. **Chart references**: Charts need `Reference(ws, min_col, min_row, max_col, max_row)`. Forgetting `min_col` defaults to 1, often wrong.6667## Default Workflow for "Make me an Excel"6869```701. Detect industry (finance / FMCG / e-commerce / internet)712. Detect deliverable type (template / dashboard / analysis / report)723. Find matching template script under templates/734. Ask 2-3 clarifying questions:74 - 数据规模（行数、字段名）75 - 是否要图表 / Dashboard76 - 输出文件名 + 公式是否需要重算775. Run / generate script, output .xlsx786. If formulas were used: tell user about recalc.py or auto-recalc-on-open797. If user gives code: run excel_lint.py mentally, point out pitfalls80```8182## Files8384| File | Role |85|---|---|86| `README.md` | Project intro |87| `WORKFLOW.md` | Cross-cutting workflow (clean → analyze → visualize → deliver) |88| `INSTALL_CN.md` | Chinese install + usage |89| `references/` | 8 progressive-disclosure knowledge files (formulas, pivot, Power Query, VBA-vs-Python, openpyxl, cell-protection, charts-and-formatting, pitfalls) |90| `templates/{finance,fmcg,ecommerce,internet,saas}/` | Output dirs for generated .xlsx (not pre-committed binaries) |91| `scripts/excel_lint.py` | Audit openpyxl **code** for common pitfalls (XL001…) |92| `scripts/analyze.py` | Audit an existing **.xlsx file** for health issues (XA001…XA010) |93| `scripts/recalc.py` | Recalculate formula caches via LibreOffice headless (requires LibreOffice installed) |94| `scripts/generate_*/` | 5 industry template generators (one script per vertical) |95| `scripts/helpers/` | Reusable styling / formula helpers |96| `tutorials/` | Beginner tutorial |97| `examples/` | End-to-end case study (finance DCF walkthrough) |9899This 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.
gaaiyun