spreadsheet-formula-helper_skill

Overview

This skill generates and debugs spreadsheet formulas for Excel and Google Sheets, including pivot tables and array formulas. It translates between dialects and returns working formulas with short explanations and worked examples. Use it when you need reliable, production-ready spreadsheet logic and edge-case handling.

How this skill works

I first gather platform, locale, exact ranges, sample rows, and desired outputs. I restate the problem with explicit sheet names and ranges, then draft primary formulas—preferring dynamic arrays where supported. I explain placement, name suggestions, and guardrails like IFERROR, LET, or LAMBDA. Finally I provide worked examples and variants for Excel vs Sheets plus a brief troubleshooting checklist.

When to use it

• You need a single formula that replaces copy-down logic.
• Porting formulas between Excel and Google Sheets dialects.
• Building dynamic arrays or spill formulas for modern Excel/Sheets.
• Creating robust calculations with edge-case handling (blanks, text, dates).
• Debugging an existing formula that returns errors or incorrect results.

Best practices

• Provide sample rows and expected outputs before drafting formulas.
• Specify platform and locale to ensure correct separators and functions.
• Prefer dynamic array formulas when available to avoid manual fills.
• Wrap fragile expressions with IFERROR or validation checks.
• Document assumptions and where to place named ranges or helper columns.

Example use cases

• Sum the latest date per customer using MAXIFS in Excel and MAX(FILTER()) in Sheets.
• Translate a semicolon-separated CSV split+trim formula into both dialects with locale variants.
• Create a spillable unique list with counts using UNIQUE + COUNTIF or LET-based solutions.
• Debug a VLOOKUP that returns #N/A by replacing it with INDEX/MATCH or XLOOKUP and showing guardrails.
• Build a pivot-like summary using a single dynamic array formula instead of a pivot table.