workflows

AI-generated spreadsheet formula: five cases to test before using it

Check an AI-suggested formula against expected results, edge cases and copied references before filling a whole column.

An AI-suggested formula can evaluate successfully and still answer the wrong question: it might reference the wrong price column, miss a row or silently treat a blank as zero. Error-checking tools help with visible failures. A plausible number needs a separate expected result before you fill an entire column.

Short answer

Write the business rule in plain language, identify the input columns and calculate a few outcomes independently. Test the suggestion in a copy against five cases: ordinary, zero, blank, boundary and locale format. Compare actual and expected results, then check how references move when copied to another row. If your team has not defined what a blank means, do not let the model decide.

A small reproducible test set

Assume line total = unit price × quantity, with no tax, shipping or discount. This is a test of the business rule, not a universal Excel or Google Sheets formula. Values are hypothetical.

Case Unit price Quantity Expected result What it checks
1. Ordinary $12.50 2 $25.00 Correct columns and multiplication
2. Zero $12.50 0 $0.00 Zero is not replaced by a blank or prior value
3. Blank $12.50 not supplied Hold for review Missing-value policy is undefined
4. Boundary $0.01 1 $0.01 Cents are not lost to rounding
5. Locale format 1,234.50 2 $2,469.00 The amount is numeric, not text, in an en-US sheet

The blank case has no numerical answer until the operator defines what a missing quantity means. For a UK or Canadian workbook, use the actual currency and locale settings; do not assume the symbol or punctuation in this US example applies everywhere.

A gate before filling down

  1. Use a copy with fictional data. Do not paste client or financial records into an AI chat without authorization and a relevant policy. Supply sample headers and the rule instead.
  2. Set expectations without the AI formula. Work the five results out independently. If the model supplied both the formula and the “correct” answers, the test is circular.
  3. Run it on a few rows. Compare cell by cell. Microsoft says error-checking rules catch common issues but do not guarantee an error-free workbook. Google documents Gemini formula suggestions and error fixes in Sheets; availability varies by account and product.
  4. Copy to another row. Confirm that references intended to stay fixed remain fixed, while row-level references advance. Check whether a range drops newly added rows or includes a header.
  5. Reconcile an independent total. Hand-calculate a small sample and compare it with the computed column. For pricing, payments, tax or an external decision, involve the responsible reviewer before using the result.

Stop conditions

Stop if a case fails, the expected result is disputed, sensitive data would be exposed, or an accounting or tax rule has not been defined. Fix the rule and rerun the set; changing only the row that revealed the problem is not enough. Five cases help catch errors but cannot certify every formula or workbook.

If you are choosing a platform, see our Google Workspace versus Microsoft 365 guide. To decide what may be shared with AI, start with a small-business AI-use policy. These manual checks do not require a paid AI spreadsheet feature.

Sources