Spreadsheet Formula Guide

Getting AI to write, explain and debug spreadsheet formulas — without shipping a silent error.

The prompt that works

Vague requests produce formulas that work on your example and break on your data. Give it four things:

In [EXCEL / GOOGLE SHEETS]:

MY DATA
[DESCRIBE EACH COLUMN, ITS LETTER, AND WHAT IT CONTAINS]
Rows: [HOW MANY, AND WHERE THE DATA STARTS]

WHAT I WANT
[THE CALCULATION, IN PLAIN ENGLISH]

EDGE CASES
- Blank cells: [IGNORE / TREAT AS ZERO / FLAG]
- Text in a number column: [WHAT SHOULD HAPPEN]
- No match found: [WHAT SHOULD SHOW]
- Division by zero: [WHAT SHOULD SHOW]

Give me the formula, explain each part, and tell me what it does
on every edge case above.

The edge cases block is what separates a formula that works from one that quietly returns #N/A in row 4,000.

Explaining an inherited formula

Explain this formula step by step in plain English, working from the
innermost function outwards:

[PASTE]

Then tell me:
- What input would make it return a WRONG answer without erroring?
- What assumptions does it make about the data?
- Is there a simpler way to write it?

That middle question is the important one. A formula that errors is safe; a formula that silently returns the wrong number is not.

Debugging

This formula returns [ACTUAL] and I expected [EXPECTED].

Formula: [PASTE]
Sample of my data: [PASTE 5 ROWS INCLUDING THE FAILING ONE]

Find the cause. Tell me what is wrong BEFORE giving me the fix,
so I understand it.

The errors and what they usually mean

Error	Usually means
#N/A	Lookup found nothing — check for trailing spaces and text-vs-number mismatch
#REF!	A referenced cell was deleted
#VALUE!	Text where a number was expected
#DIV/0!	Dividing by an empty or zero cell
#NAME?	Misspelled function, or a function your version does not have
#SPILL!	A dynamic array has no room to expand
Wrong number, no error	The dangerous one. Usually a wrong range, a missing absolute reference, or hidden rows in a SUM

The traps AI regularly falls into

Version mismatch. XLOOKUP, LET, LAMBDA and dynamic arrays do not exist in older Excel. Say which version you have.

Excel vs Sheets. They diverge on array behaviour, ARRAYFORMULA, QUERY, and separators. Always say which one.

Locale separators. Some locales use ; instead of , between arguments. If a correct-looking formula is rejected, this is why.

Absolute references. AI often omits $. The formula works in the first row and drifts as you fill down. Always check by filling down three rows and reading the third.

Hidden and filtered rows. SUM includes them; SUBTOTAL and AGGREGATE can exclude them. If your total disagrees with what you see, this is usually it.

Verification, every time

• Fill down three rows and read the third formula — references correct?
• Check one row by hand against the source
• Check the total against something you already know
• Test a blank row, a zero, and a no-match case
• Sort or filter the data and confirm the answer does not change
• If it feeds a decision, have someone else check it

Building something reusable

I do this calculation every [WEEK/MONTH]:
[DESCRIBE]

Build me a small template:
- Which cells are inputs (and mark them clearly)
- Which are calculated
- Data validation on the inputs so bad values cannot be entered
- What the sheet should show when inputs are empty

Then write a one-paragraph note explaining it to someone
who inherits this sheet from me.

That last instruction is worth insisting on. Most spreadsheet pain is inherited spreadsheets nobody understands.

When to stop using a spreadsheet

• More than a few thousand rows and it is getting slow
• More than one person editing simultaneously
• The same data copied into more than two places
• Formulas nobody can explain
• It has become a database with no rules

At that point the answer is a real tool, not a cleverer formula. AI will happily help you build an increasingly elaborate spreadsheet long past the point where you should have stopped.