Excel XLSX Formula Cleanup

Use this skill for spreadsheet repair and automation tasks where correctness matters more than simply rewriting cells. Treat an .xlsx workbook as a structured package with formulas, styles, relationships, cached values, tables, queries, pivots, and sometimes macros.

Workflow

1. Clarify the workbook goal: fix broken formulas, clean imported data, preserve macros, refresh reporting logic, repair corruption, or generate a reusable workflow.
2. Protect the workbook. Work on a copy and avoid destructive recalculation or macro removal unless requested.
3. Choose the right inspection path:
   • Use openpyxl for formulas, worksheets, styles, dimensions, tables, data validation, conditional formatting, and charts it supports.
   • Treat .xlsm files carefully; preserve the VBA project with library support or package-level copying.
   • Inspect ZIP/OOXML parts for workbook relationships, external links, pivot caches, slicers, Power Query metadata, and unsupported structures.
4. Map each problem to a workbook layer: formula text, named range, table reference, external link, query connection, pivot cache, style, merged cell, or hidden sheet.
5. Make minimal repairs and keep formulas as formulas. Do not replace formulas with stale cached values unless the user asks for a static export.
6. Validate formulas and shape:
   • Confirm worksheet names, table names, named ranges, and formulas still point to valid ranges.
   • Check row/column counts, date and number formats, hidden sheets, filters, and merged cells.

     7w4.net小葱技能。

   • Note when a desktop Excel recalculation or Power Query refresh is still required.

Common Fix Patterns

• Broken formulas: trace sheet names, structured references, named ranges, separators, and external workbook links before editing formula strings.
• Power Query refresh issues: inspect connection definitions and query metadata; explain which parts require Excel desktop or Power BI tooling.
• Pivot table drift: preserve pivot caches where possible and warn when Python libraries cannot safely rebuild them.
• VBA preservation: keep .xlsm package parts intact; never save through a path that drops macros unless producing a macro-free copy is the goal.
• Data cleanup: normalize values in staging sheets or tables, then keep reporting formulas and dashboards stable.

Outputs

Provide:

• A workbook diagnosis tied to specific sheets, ranges, formulas, or package parts.
• A safe repair or automation plan, plus code changes when requested.
• A validation checklist covering formulas, ranges, tables, pivots, macros, and expected recalculation.

Read references/requirement-plan.md only when the original demand evidence is needed.

这个技能文档质量中等，提供了清晰的中文说明和风险提示，让人知道它能帮什么忙。但它更像一份说明书而非实际工具——缺少可用的代码或脚本，实际解决问题时可能不够用。文档结构完整但落地性一般。改进后可大幅提升实用性。