Get a spreadsheet formula from AI, then prove it works

Use it when you ask a chatbot for an Excel or Google Sheets formula, it looks right, and you are about to fill it down across thousands of rows.

Not when you want the assistant to answer a question about the data itself. Use Asking questions of your data. If you are matching two files that should agree, use Reconcile two exports.

You need the sheet, a spare tab, and about 30 minutes the first time. Paste header names and made-up example values, not real customer rows: What not to paste. The steps and times are our suggestion.

Why test a formula that looks right

Spreadsheets were error-prone long before AI. In Panko's review, the more careful field audits from 1997 to 2000 "found errors in at least 86% of the spreadsheets audited", and developers "are overconfident in the accuracy of their spreadsheets" (Panko, 2000, review of field audits). Those audits were small and mostly counted serious errors only. Models are not past this either: a benchmark (a fixed set of test questions) of 912 real questions from Excel forums found "a substantial gap" between the best models and people (SpreadsheetBench, 2024, benchmark, preprint). This page applies the general routine in Check before you trust to formulas.

Steps

Silent failures to check

These return a plausible answer instead of an error.

• IFERROR hides everything. It returns your fallback for #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL! alike (Microsoft). A typo in a range then looks like "not found". Run the test block without IFERROR first, and add it last if you need it.
• VLOOKUP defaults to approximate match. In Excel, if range_lookup is left out, "the default value will always be TRUE or approximate match", which assumes the first column is sorted (Microsoft). Google Sheets is the same: "If is_sorted is not specified, it is TRUE by default" (Google). On unsorted data you can get a wrong value with no error. Ask for FALSE to be written in.
• XLOOKUP defaults to exact match and returns #N/A when nothing is found, unless you give it an if_not_found value (Microsoft). It is "not available in Excel 2016 and Excel 2019", which is why step 1 names the version.
• References that move when filled down. A relative reference shifts as you copy it; a $ fixes the column, the row or both, and F4 cycles through them in Excel (Microsoft). Check the formula in the last row of the test block, not only the first. A lookup table without $ slides down as you fill.
• Whole-column references. A:A takes in the header and any totals or notes below the data. Our suggestion: check what the range would pick up, or use a bounded range or a table.
• Separators. Excel expects the list separator set in Windows regional settings, and comma is the default for US English; a formula typed with another separator gives a "problem with this formula" error (Microsoft). Tell the model which one you use.

Try the fill-down failure on a small VAT column (illustrative). Change how C2 refers to the rate and read the formula in the last row.

The prompt

I need a formula for <APP_AND_VERSION>. My formulas use <SEPARATOR> between arguments.
Tab: <TAB_NAME>. Data runs from row <FIRST_ROW> to about row <LAST_ROW>.
Columns (header: example values):
<COLUMN_LIST>
Put the result in column <RESULT_COLUMN>. It should: <WHAT_IT_SHOULD_DO>
Give me:
1. The formula for the first data row, ready to fill down.
2. A plain-words explanation of each part.
3. What it returns for: a blank cell, text in a number column, a duplicate key, a zero, a month-end date, and a value with no match.
4. Any default it relies on (match type, sorting), written out explicitly.
Do not wrap it in IFERROR.

Replace the following:

• <APP_AND_VERSION>: such as Excel for Microsoft 365 on Windows or Google Sheets.
• <SEPARATOR>: commas or semicolons.
• <TAB_NAME>: the tab's exact name.
• <FIRST_ROW> and <LAST_ROW>: the first data row and roughly the last.
• <COLUMN_LIST>: one line per column, such as B Invoice date: 31/01/2026, 15/02/2026. Use made-up values.
• <RESULT_COLUMN>: the column letter for the answer.
• <WHAT_IT_SHOULD_DO>: one sentence, such as return the unit price from tab Prices for the SKU in column C, or "missing" if there is none.

The test block

case,<INPUT_COLUMNS>,expected,formula_result,match
blank input,,<EXPECTED>,,
text in number column,<VALUE>,<EXPECTED>,,
duplicate key (first),<VALUE>,<EXPECTED>,,
duplicate key (second),<VALUE>,<EXPECTED>,,
zero,0,<EXPECTED>,,
month-end date,<DATE>,<EXPECTED>,,
should not match,<VALUE>,<EXPECTED>,,
normal case 1,<VALUE>,<EXPECTED>,,
normal case 2,<VALUE>,<EXPECTED>,,
last row of range,<VALUE>,<EXPECTED>,,

Replace the following:

• <INPUT_COLUMNS>: the column headers the formula reads, copied exactly.
• <VALUE> and <DATE>: inputs you choose for each case.
• <EXPECTED>: the answer you worked out by hand, before running the formula.

Fill formula_result from the formula, and set match with a simple equality check, such as =D2=E2. Add rows for anything specific to your data.

Built-in assistants

Copilot in Excel opens from the Copilot icon in the lower right of Excel and can explain a formula; Microsoft says to "review, edit, and verify anything Copilot creates" (Microsoft). In Google Sheets, Gemini can create a formula from the side panel on eligible plans, and Google warns it may suggest inaccurate information (Google). The same test block applies.

How to tell it worked

• Every row of the test block matches, including the blank and the row that should not match.
• One total or count you knew in advance matches after the fill-down.
• The hidden tab has the formula, the date and the model, and the test re-runs when the formula changes.
• It has failed if you edited the expected answers after seeing the formula's results.

Sources

1. Panko, 2000. "Spreadsheet Errors: What We Know. What We Think We Can Do." Review of field audits and experiments. Link
2. SpreadsheetBench, 2024. 912 real spreadsheet questions from Excel forums. Preprint benchmark. Link
3. Microsoft. VLOOKUP, XLOOKUP and IFERROR function pages; relative and absolute references; list separator errors. Vendor documentation. VLOOKUP, XLOOKUP, IFERROR, References, Separators
4. Google. VLOOKUP in Sheets; Gemini in Sheets. Vendor documentation. VLOOKUP, Gemini
5. Microsoft. Copilot in Excel. Vendor documentation. Link

Changes

1. v1.010 Oct 2026first version