excel-sheet-reference_skill

Overview

This skill creates Excel .xlsx workbooks with multiple sheets and cross-sheet formulas so you can keep raw data separate from summary or report sheets. It uses Python and openpyxl to write formulas like COUNTIFS, VLOOKUP, MATCH, INDEX, SUMIF/SUMIFS and stores them so Excel performs the calculations when the file is opened. The skill focuses on reproducible, programmatic workbook generation rather than in-memory evaluation.

How this skill works

The code builds a workbook, adds a source-data sheet containing raw rows, and creates one or more summary/report sheets that contain formula strings referencing the source sheet (e.g., =COUNTIFS(Source!A:A,A2)). Formulas are written as strings into cells; Excel evaluates them on open, so computed values appear after Excel recalculates. It supports cross-sheet references, quoted sheet names, absolute/relative addresses, and common patterns such as VLOOKUP with IFERROR and INDEX/MATCH.

When to use it

• Keep a single authoritative source sheet and generate derived reports or dashboards automatically
• Produce Excel deliverables from Python without duplicating raw values
• Create lookup-driven order, invoice, or price sheets that reference a price list
• Generate summary counts, conditional sums, or position lookups across sheets
• Automate periodic reports where formulas should remain live for end users

Best practices

• Test formulas in Excel manually first to verify syntax and ranges
• Use clear sheet names and wrap names with spaces in single quotes (e.g. 'My Sheet'!A1)
• Prefer named ranges for complex or frequently reused ranges to improve readability
• Wrap lookups in IFERROR to avoid #N/A when source data is incomplete
• Use absolute ($A$1) vs relative references deliberately when copying formulas
• For large datasets consider pivot tables or Power Query instead of many volatile formulas

Example use cases

• Create a source data sheet of products and a summary sheet that counts each product with COUNTIFS
• Build an Orders sheet that uses VLOOKUP to pull prices from a PriceList sheet and calculates totals
• Make a Lookup sheet that finds a name’s row with MATCH and returns fields via INDEX
• Generate a dashboard sheet that aggregates sales by category using SUMIFS across the source sheet
• Export recurring reports where formulas remain editable by recipients in Excel