← All Kits · Excel Kit

Checking a Formula an AI Wrote, in the Order Errors Actually Happen

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

A generated formula arrives correct in the only way a machine can guarantee: it parses, and it returns something. Whether it returns the thing you asked for is a separate question, and Excel has no way to raise it.

What you do: check six things, in this order, and test on rows where you already know the answer.

The short version. A formula that returns a number has passed no test at all.

Check 1: the range covers the data, and only the data

Select the range inside the formula and look at the count in the status bar. Two failures, both common:

• Short range. SUM(B2:B500) on 620 rows of data. The total is quietly 120 rows light, and it looks perfectly reasonable.
• Long range. SUM(B2:B10000) works today and silently absorbs anything pasted below the data later, including a stray total row.

The fix for both is a table and structured references, so the range is defined by the data rather than by a number somebody typed.

Check 2: the anchoring

Copy the formula to the last row of the column and read it there. Lookup ranges must be locked with dollar signs; the row key must not be. A range written without anchors slides down, so the top rows are right and the bottom rows return #N/A, which is the tell described in lookups that return #N/A.

Press Ctrl+` to show all formulas at once. Sliding ranges are visible immediately in that view, and invisible in the normal one.

Check 3: the match mode

Any lookup has a match argument, and the default is the dangerous one.

An approximate match on unsorted data returns wrong values without erroring, which is the worst failure mode available. If you see a lookup with a missing final argument, add it before doing anything else.

Check 4: the hidden error handler

Search the formula for IFERROR, IFNA and ISERROR. Then remove the wrapper temporarily and look at what is underneath. You are asking two questions: how many rows were erroring, and what kind of error was it.

Count the hidden failures:
=SUMPRODUCT(--ISNA(original_formula_range))

If that count is not zero, you have found rows the report is silently swallowing. Deciding they should show as blank is fine. Not knowing they exist is not. See IFERROR for the line between the two.

Check 5: the four boundary rows

Test on rows where you can compute the answer in your head:

1. The first data row. Off-by-one errors live here, especially where a header was counted.
2. The last data row. Range and anchoring errors live here.
3. An empty row. Does it return 0, blank, or an error, and which did you want?
4. An extreme row. A negative, a zero, or a very large value. Percentage formulas break on zeros; band lookups break below their lowest bound.

Four rows, two minutes, and between them they catch the great majority of real formula defects.

Check 6: reconcile the total

This is the highest-value check on the page. Compare the column total to a number you already trust: last month's report, the source system, a printed invoice total. One comparison tests every row at once.

Difference   =new_total - trusted_total
Rows counted =COUNT(range)  against the source row count

If the difference is exactly one row's worth, you have an off-by-one. If it is a clean multiple, you have duplication from a join or a repeated paste. If it is small and untidy, you have rounding or a handful of dropped rows, and both are worth finding.

What to ask for, rather than what to fix

Two habits change what you get back. Ask for the formula and the sentence describing what it does, then check the sentence against the formula rather than against the output. And ask what the formula returns for an empty cell, for a zero and for a missing key, which forces the edge cases into the open before they arrive in a report.

How to apply this to your own work

1. Keep a small test block at the bottom of your working sheets: a known row, an empty row, a zero row. Paste any new formula there first.
2. Convert sources to tables so ranges stop being a thing you have to verify.
3. Never accept an IFERROR you did not add yourself, until you have seen what it is hiding.
4. Reconcile to one trusted number every time, and write the comparison into the sheet rather than doing it in your head.
5. Say what the formula does out loud in one sentence. If you cannot, you are not yet in a position to defend it.

The one habit to keep

Test on rows where you already know the answer. It sounds too simple to be a method, and it is the entire method: verification is the practice of comparing an output against something you knew independently of it.

What is the number you would reconcile against, on the workbook you are building right now?