excel-spreadsheets

name	excel-spreadsheets
description	Use when creating, importing, repairing, or validating Excel workbooks with formulas, tables, charts, controls, and professional formatting; use python-document-generation for multi-format application exports.
metadata	{"portable":true,"compatible_with":["claude-code","codex"]}

Excel Spreadsheets Skill

Acknowledgement: Shared by Peter Bamuhigire, techguypeter.com, [phone].

Use When

• Generate world-class, professionally designed Microsoft Excel spreadsheets and handle all Excel/spreadsheet workflows. Use when: generating .xlsx files from apps or scripts (openpyxl, xlsxwriter, PhpSpreadsheet, pandas), importing or parsing...
• The task needs reusable judgment, domain constraints, or a proven workflow rather than ad hoc advice.

Do Not Use When

• The task is unrelated to excel-spreadsheets or would be better handled by a more specific companion skill.
• The request only needs a trivial answer and none of this skill's constraints or references materially help.

Excel Spreadsheets Required Context

• Gather relevant project context, constraints, and the concrete problem to solve; load references only as needed.
• Confirm the desired deliverable: design, code, review, migration plan, audit, or documentation.

Excel Spreadsheets Core Method Notes

• Read this SKILL.md first, then load only the referenced deep-dive files that are necessary for the task.
• Apply the ordered guidance, checklists, and decision rules in this skill instead of cherry-picking isolated snippets.
• Produce the deliverable with assumptions, risks, and follow-up work made explicit when they matter.

Quality Standards

• Keep outputs execution-oriented, concise, and aligned with the repository's baseline engineering standards.
• Preserve compatibility with existing project conventions unless the skill explicitly requires a stronger standard.
• Prefer deterministic, reviewable steps over vague advice or tool-specific magic.

Excel Spreadsheets Existing Failure Notes

• Treating examples as copy-paste truth without checking fit, constraints, or failure modes.
• Loading every reference file by default instead of using progressive disclosure.

Excel Spreadsheets Core Deliverables

• A concrete result that fits the task: implementation guidance, review findings, architecture decisions, templates, or generated artifacts.
• Clear assumptions, tradeoffs, or unresolved gaps when the task cannot be completed from available context alone.
• References used, companion skills, or follow-up actions when they materially improve execution.

Evidence Produced

Category	Artifact	Format	Example
Release evidence	Generated Excel workbook	Branded .xlsx artefact compliant with the excel-spreadsheets design standard	docs/output/dashboard-2026-04-16.xlsx

References

• Use the references/ directory for deep detail after reading the core workflow below.

Inputs

Artefact	Source or provider	Requirement	If absent
Data, workbook purpose, and calculation rules	requester or verified source files	required	Return a schema and unresolved-input list if absent

Capability contract

Read access to the workbook and calculation rules is required. Formula, format, macro, or data edits need explicit authority; external connections and workbook publication require a separately confirmed boundary.

Degraded mode

Fallback without spreadsheet execution or rendering: return a qualified workbook plan or patch, list formula and visual checks as unassessed, and do not treat an unopened file as accepted.

Decision rules

Choice	Action	Failure avoided
Existing workbook must retain formulas	Edit in place and regression-check formula cells	Silent calculation loss

Outputs

Artefact	Consumer	Observable acceptance condition
Validated workbook and QA record	decision-maker or application user	Opens cleanly; formulas, formats, filters, and required sheets are checked

Excel Spreadsheets Evidence Notes 1

• Record source totals, preserved formulas, workbook changes, reconciliation variances, and sheet-level visual checks.

Worked example

For a monthly tracker, preserve the raw-data sheet, add a table-backed summary, use explicit number formats, and record formula and visual checks.

Excel done right looks like a financial analyst and a graphic designer collaborated on it. Done wrong, it's a wall of unformatted data nobody trusts. This skill covers both the code that generates Excel files and the design standards that make them world-class.

Reference files (read when needed):

• references/design-standards.md — layout, colour palettes, typography, table structure, conditional formatting, print setup
• references/formulas-functions.md — XLOOKUP, dynamic arrays, LET, LAMBDA, SUMIFS, MAXIFS/MINIFS, essential formula patterns
• references/programmatic-generation.md — openpyxl, xlsxwriter, PhpSpreadsheet, pandas — code patterns for generating professional Excel from apps
• references/charts-pivot.md — chart types, professional chart formatting, PivotTables, slicers, dashboards
• references/financial-modeling.md — PMT, PV, FV, NPV, IRR; Goal Seek, Data Tables, Scenario Manager, Solver; modeling golden rules; formula debugging
• references/python-automation.md — Excel–Python–Excel workflow, advanced read_excel(), 6-step export, pivot_table/merge/crosstab, email automation, =PY() function, xlwings
• references/vba-macros.md — recording/running macros, VBA golden rules, common patterns, keyboard shortcuts reference
• references/quality-checklist.md — pre-delivery checklist

Sources: Microsoft Excel 365 Bible (Walkenbach/Alexander); Microsoft Excel Bible 2026; Ultimate Excel Formula & Function Reference Guide; Excel 2025 All-in-One; Excel 2019 Advanced Topics (George); Advanced Excel for Productivity (Urban); Automate Excel with Python (Wengler, 2026); Python in Excel Advanced (Van Der Post)

---

The Standard

Every spreadsheet produced must pass: a data analyst and a designer would both be satisfied. Specifically:

1. Data lives in a properly structured Excel Table — never raw ranges
2. Every number has an intentional format (currency, %, dates — never General)
3. Visual hierarchy is clear: header rows are distinct, data rows are readable
4. Formulas are correct, efficient, and use structured references where possible
5. The file opens correctly on any machine, in any regional locale

---

Core Architecture Rules

Rule 1 — Always use Excel Tables

Convert every data range to an Excel Table (Ctrl+T) immediately. Tables give you:

• Structured references: =Table1[Amount] instead of =$C$2:$C$100
• Auto-expansion when new rows are added
• Built-in filter arrows
• Automatic banded rows
• Named reference for programmatic access

Programmatic: In openpyxl, xlsxwriter, and PhpSpreadsheet, always add a Table (ListObject) definition over data ranges. See references/programmatic-generation.md.

Rule 2 — One table per sheet, one topic per sheet

Never mix multiple unrelated datasets on one sheet. Use separate sheets with clear names. Sheet names: PascalCase or Title Case, max 20 characters, no spaces (use underscores if needed).

Rule 3 — Separate data from presentation

• Data sheets — raw data in Tables, no decorative formatting, no merged cells
• Report/Dashboard sheets — formulas pulling from data sheets, full formatting treatment
• Configuration sheets (hidden) — lookup lists, parameters, constants

Rule 4 — Never merge cells in data ranges

Merged cells break sorting, filtering, PivotTables, and programmatic reading. For visual centering of headers, use Center Across Selection instead (Format Cells → Alignment → Horizontal: Center Across Selection).

---

Excel Table Design

Read references/design-standards.md for full colour palettes and formatting specs.

Standard table anatomy:

Row 1:  Sheet title / document header   ← merged+centred, large font, brand colour
Row 2:  Subtitle / date / filter info   ← smaller, grey
Row 3:  [blank spacer row]
Row 4:  Table header row                ← Excel Table header (bold, brand fill, white text)
Row 5+: Data rows                       ← banded, 11pt, left/right aligned by type
Last:   Totals row                      ← bold, top border, SUM/AVERAGE via Table totals row

Column alignment rules:

• Text columns → left-aligned
• Number/currency columns → right-aligned
• Date columns → right-aligned or centred
• Status/category columns → centred
• Header row → match column alignment (not always centred)

---

Number Formats (critical — never leave as General)

Data type	Format code	Example output
Currency (UGX/KES/TZS)	#,##0	1,250,000
Currency with decimals	#,##0.00	1,250,000.00
USD	"$"#,##0.00	$1,250.00
Percentage	0.00%	12.50%
Percentage (whole)	0%	13%
Date (display)	DD MMM YYYY	05 Apr 2026
Date (ISO sort)	YYYY-MM-DD	2026-04-05
Large numbers	#,##0.0,,"M"	1.3M
Negative red	#,##0.00;[Red]-#,##0.00	-500.00 (red)
Integer	#,##0	42,000
Duration (hours)	[h]:mm	37:30

Custom format anatomy: positive;negative;zero;text

---

Essential Formulas

Read references/formulas-functions.md for full formula patterns. Core rules:

Use structured references in Tables:

=SUMIFS(Sales[Amount], Sales[Region], [@Region], Sales[Status], "Paid")

XLOOKUP over VLOOKUP always:

=XLOOKUP([@ID], Products[ID], Products[Price], "Not found", 0)

Dynamic arrays for reports:

=FILTER(Sales[#All], (Sales[Region]="East")*(Sales[Month]=B2))
=SORT(UNIQUE(Sales[Category]))
=SEQUENCE(12, 1, DATE(2026,1,1), 30)  ← 12 monthly dates

LET for complex formulas (readability + performance):

=LET(
  data, FILTER(Sales[Amount], Sales[Status]="Paid"),
  avg, AVERAGE(data),
  IF(avg>100000, "Above target", "Below target")
)

---

Data Validation

Every user-input column must have data validation. Never let free-form text corrupt a data column.

Dropdown from a Table column:

• Source: =INDIRECT("Table1[Category]") or a named range
• Input message: "Select a category from the list"
• Error alert: Stop — "Invalid entry. Please select from the list."

Date range validation:

• Allow: Date, Between, =TODAY()-365, =TODAY()+365

Whole number range:

• Allow: Whole number, Between, 0, 1000000

---

Conditional Formatting

Apply to entire Table columns, not fixed ranges (so it auto-expands with the Table).

Standard patterns:

• Heat map (numeric): 3-colour scale, low=white, mid=yellow, high=brand colour
• Above/below average: Green fill for above, red fill for below
• Status column: Formula-based — =[@Status]="Paid" → green; =[@Status]="Overdue" → red
• Data bars: For ranking/comparison columns — no border, solid fill, brand colour
• Duplicate detection: =COUNTIF(Table1[Email],[@Email])>1 → orange fill

---

Professional Finishing

Read references/design-standards.md → Professional Finishing section.

Freeze panes: Always freeze the header row (and optionally the first column for wide tables). View → Freeze Panes → Freeze Top Row.

Print setup (every sheet intended for printing):

• Page Layout → Page Setup:
  • Orientation: Landscape for wide tables
  • Scale to fit: 1 page wide, auto tall
  • Print titles: Row 1 (and Table header row) to repeat on every page
  • Margins: Narrow (0.64 cm) for data tables; Normal for reports
  • Header: Document name left, date centre, page number right
  • Footer: "Page &P of &N" centred, confidential notice if needed

Workbook hygiene:

• Delete all unused sheets (Sheet1, Sheet2, Sheet3)
• Name every sheet clearly
• Set the first sheet as the active sheet on open
• Remove all #REF!, #VALUE!, #NAME? errors before delivery

---

Programmatic Generation

Read references/programmatic-generation.md for full code patterns per language/library.

Library selection:

Use case	Library	Language
Full formatting + charts	openpyxl	Python
Large data, max performance	xlsxwriter	Python
PHP apps	PhpSpreadsheet	PHP
Data analysis output	pandas + openpyxl	Python
Node.js apps	exceljs	JavaScript

Non-negotiable programmatic rules:

1. Always define a Table (add_table / addTableStyleInfo) over data — never just write raw rows
2. Always set column widths — auto-width from content, with min 8 and max 60 characters
3. Always apply number formats to numeric columns — never leave as default
4. Always freeze the header row
5. Always set a tab colour per sheet for multi-sheet workbooks
6. Always use a professional table style (TableStyleMedium2 or equivalent)

---

Import / Parsing Patterns

When reading Excel files in applications:

Always:

• Read with header=0 (first row is headers) unless the file has multi-row headers
• Strip whitespace from string columns after reading
• Validate expected columns exist before processing — fail early with clear error messages
• Parse date columns explicitly (don't rely on auto-detection)
• Handle merged header cells by forward-filling merged values

Never:

• Assume column order — always reference by column name, not index
• Assume data starts at row 1 — check for title rows above the table
• Trust data types — validate and coerce explicitly

Python pattern:

import pandas as pd

df = pd.read_excel("file.xlsx", sheet_name="Sales", header=0)
df.columns = df.columns.str.strip()  # remove whitespace from headers
df["Date"] = pd.to_datetime(df["Date"], dayfirst=True)
df["Amount"] = pd.to_numeric(df["Amount"], errors="coerce")
df = df.dropna(subset=["ID"])  # drop rows with no ID

---

Financial Functions Quick Reference

Read references/financial-modeling.md for full formulas, examples, and What-If tools.

Function	Use case	Key rule
=PMT(rate, nper, pv)	Monthly loan/mortgage payment	Rate and nper must match time unit (÷12 for monthly)
=PV(rate, nper, pmt)	Present value of an annuity	Cash inflows = positive, outflows = negative
=FV(rate, nper, pmt)	Future value of savings/investment	pmt is negative (cash going out)
=NPV(rate, cashflows)	Net present value	Add period-0 investment separately outside NPV()
=IRR(cashflows)	Internal rate of return	First value should be negative (initial outlay)

What-If tools:

• Goal Seek — find the input that achieves a target output (Data → What-If Analysis → Goal Seek)
• Scenario Manager — store and compare named sets of input values (Base/Optimistic/Pessimistic)
• Data Tables — calculate output across a range of input values (1-variable or 2-variable)
• Solver — optimise across multiple variables with constraints (requires Solver add-in)

---

VBA Macros

Read references/vba-macros.md for full VBA patterns and keyboard shortcuts.

Record a macro: View → Macros → Record Macro (or status bar button bottom-left) Run a macro: Alt+F8 or assigned shortcut key Edit a macro: Alt+F11 opens the VBA editor

Critical shortcuts:

• Ctrl+Arrow — jump to end of data range
• F2 — edit mode with colour-coded cell references
• F4 — toggle $A$1 → A$1 → $A1 → A1 (absolute/relative)
• Ctrl+Shift+Enter — legacy array formula (prefer dynamic arrays)
• Alt+H+O+I — auto-fit column width

---

Customisation Quick Reference

What to change	Where
Table colour palette	references/design-standards.md → Colour Palettes
Formula patterns	references/formulas-functions.md
openpyxl/xlsxwriter code	references/programmatic-generation.md
Chart types and formatting	references/charts-pivot.md
Financial functions & What-If	references/financial-modeling.md
Python automation & =PY()	references/python-automation.md
VBA macros & keyboard shortcuts	references/vba-macros.md
Pre-delivery checks	references/quality-checklist.md

Workflow

1. Confirm workbook purpose, consumers, source data, formulas, and required compatibility.
2. Preserve raw data and existing calculations before making authorised changes.
3. Stop when source totals, calculation rules, or workbook protection cannot be verified.
4. Generate or repair, then inspect formulas and rendered sheets; recover from a failed check by reverting the affected change and isolating the defect.

Excel Spreadsheets Evidence Notes 2

Evidence	Consumer	Acceptance
Workbook QA and reconciliation record	Release owner	Formula, totals, structure, formatting, and visual checks are recorded

Anti-Patterns

• Overwriting formulas with displayed values. Fix: preserve and regression-check formula cells.
• Leaving numeric cells on General. Fix: apply explicit semantic formats.
• Mixing source data and presentation logic. Fix: separate raw, calculation, and output sheets.
• Shipping without opening and inspecting the workbook. Fix: run structural and visual checks.
• Hiding an unreconciled total. Fix: stop release and record the variance.

Reference Index

• Design standards; formulas; charts and pivots; financial modelling
• Programmatic generation; Python automation; VBA macros; quality checklist