---
name: spreadsheet-formula-explainer-builder
description: "Gives you a working Excel or Google Sheets formula, a plain-English breakdown of each part, and the likely ways it breaks, or fixes one that errors. Use when you need a spreadsheet formula built, explained, or fixed in Excel or Google Sheets, including VLOOKUP or SUMIFS style problems and error values. Do not use for building a full spreadsheet model, pivot tables, or macros and scripts. Related topics: Productivity, Spreadsheets, Data."
---

# Spreadsheet Formula Explainer & Builder

Gives you a working Excel or Google Sheets formula, a plain-English breakdown of each part, and the likely ways it breaks, or fixes one that errors.

## When to use

Use this skill when you need a spreadsheet formula built, explained, or fixed in Excel or Google Sheets, including VLOOKUP or SUMIFS style problems and error values. Do not use it for building a full spreadsheet model, pivot tables, or macros and scripts.

## Workflow

1. Ask the user for each input listed below in a single message, skipping any they have already given. Where options are listed, offer them, but accept other answers too.
2. Read the data layout and map each column letter to its meaning before touching any formula.
3. In build mode pick the simplest function that fits, in explain mode unpack the formula innermost call first, and in fix mode name the cause of the error first.
4. Check the formula against the chosen tool and the compatibility toggle, swapping out unsupported functions.
5. Test the formula on a small worked example with sample values and show the result.
6. List the two most likely failure cases with a guard for each.

## Inputs

- **Tool** (choose one: Google Sheets, Microsoft Excel, Either with the most portable version)
- **What I am trying to do** (longer free text)
- **My data layout** (longer free text)
- **Mode** (choose one: Build a new formula, Explain a formula I already have, Fix a formula that is erroring)
- **Existing formula** (free text, e.g. "Paste it here or leave blank")
- **Avoid array formulas and newer functions for compatibility** (choose one: Yes, No)

## Output

The formula in a code block, a part-by-part explanation, one worked example with sample values, and two failure cases each with a guard.

## Prompt template

Fill the inputs into this template and follow it as the full instruction.

### Spreadsheet Formula Explainer and Builder

Help me with a spreadsheet formula.

- Tool: {{Tool}}
- What I am trying to do: {{What I am trying to do}}
- My data layout: {{My data layout}}
- Mode: {{Mode}}
- Existing formula: {{Existing formula}}
- Avoid array formulas and newer functions for compatibility: {{Avoid array formulas and newer functions for compatibility}}

Work from the data layout, not from guesses: use the real column letters I gave. In build mode, choose the simplest function that does the job and say why. In explain mode, break the pasted formula into its parts from the innermost call outward. In fix mode, name the exact cause of the error before changing anything. Respect the compatibility toggle. Return the formula in a code block, then a part-by-part explanation, then a worked example with sample values, then the two most likely ways it breaks (blank cells, text stored as numbers, ranges that do not expand) with a guard for each.

## Pitfalls

- Using functions that exist in only one product when the user chose Either.
- Hard-coding a range that stops growing as rows are added.
- Changing a broken formula before diagnosing the cause, which hides the real bug.
