How to Check AI-Generated Spreadsheet Formulas Before Sending a Client Report
Short answer: Treat an AI-generated formula as a draft, not as evidence that the calculation is correct. Before a report leaves your hands, define the intended calculation, inspect the formula and its references, test it against small known inputs, try boundary and error cases, compare it with an independent calculation, and reconcile the result to a trusted total. Record what you checked so another person can repeat the review.
Both major spreadsheet platforms now describe AI features that can create formulas. Microsoft says Copilot in Excel can generate formulas and that its output can be inaccurate or misinterpret a request; it explicitly advises users to review, edit, and verify generated content before relying on it [1]. Google similarly documents Gemini features that create formulas and warns that generated suggestions may be inaccurate or inappropriate [2]. That makes verification a normal quality-control step, not an accusation that the tool is unusable.
1. Freeze the question before checking the formula
Start with the business question in plain language. For example, “Calculate the percentage of completed orders for each region, excluding cancelled orders” is more useful than “make a completion-rate formula.” Write down the numerator, denominator, inclusion rules, time period, expected output unit, and treatment of blanks or errors. If the request is ambiguous, pause and resolve the ambiguity rather than trying to compensate with a more complicated formula.
This step separates a logic error from a syntax error. A formula can be valid spreadsheet syntax and still answer the wrong question. Ask the AI tool to explain the formula in ordinary language, but verify that explanation against the written specification. Fluent wording is not proof of correctness: Microsoft notes that Copilot can produce grammatically correct explanations while the content remains inaccurate [1].
2. Preserve the original and inspect the generated formula
Make a copy or version of the workbook before accepting changes. Put the proposed formula in a clearly marked review area, or retain the original formula beside the AI suggestion. Do not silently overwrite a working calculation until the review is complete. In Excel, Copilot has edit, plan, and chat modes; plan or chat can be useful when you want to understand a proposed approach before changing the workbook [3]. In Google Sheets, generated output can be inserted, copied, retried, or undone, so keep the accepted version distinguishable from the conversation's suggestion [4].
Read the formula from left to right. Check the function names, operators, parentheses, absolute and relative references, sheet names, named ranges, and criteria strings. Look especially for a range that begins or ends one row too early, a reference to the wrong tab, a mixed reference such as $B2 where B$2 was intended, or a criterion that treats a blank, text value, or cancelled record differently than the specification.
Reference inspection questions
- Does every referenced column represent the field described in the requirement?
- Does the range include all intended records and exclude headers, subtotals, and notes?
- Will copying the formula across or down change references in the intended direction?
- Are dates real date values, or are some stored as text?
- Could a hidden row, filter, duplicate key, or external link change the result?
3. Test with a tiny known-answer table
Do not begin with the full client dataset. Build a small test table whose answer you can calculate by hand. Include ordinary rows and deliberately simple values. For a completion rate, five records might contain three completed, one pending, and one cancelled; if cancellations are excluded, the expected answer is three out of four, or 75 percent. The point is not the example's business meaning but the transparency of the expected result.
Use several test cases rather than one lucky example. Test a single matching row, no matching rows, all rows matching, a blank value, a zero denominator, duplicate identifiers, and a value at the date or threshold boundary. If the formula is copied down, test the first, middle, and last relevant rows. If it aggregates across tabs, create a case where one tab has a known subtotal and another has a different subtotal.
Compare the spreadsheet's displayed result with your independently calculated expected result. If they differ, do not “fix” the answer by rounding or changing formatting. Trace the difference back to the rule, input, reference, or function behavior.
4. Use two independent calculation paths
For material totals, calculate the same quantity a second way. Independence matters: copying the AI formula into another cell is not an independent check. Instead, use a manual count, a small pivot table, a filtered subtotal, a helper column, or a short calculation in a separate clean sheet. For instance, compare an AI-generated conditional sum with a helper column that flags qualifying rows and a plain SUM of the flags. Choose methods whose likely failure modes differ.
A second path is particularly useful for formulas that combine lookups, date logic, nested conditions, or multiple sheets. Compare both the final number and the set of rows included. Two methods can coincidentally produce the same total while selecting different records, so inspect a few positive and negative examples.
5. Reconcile to trusted source totals
Reconciliation asks whether the result fits the source data's control totals. Add the categories or regional subtotals and compare them with the grand total. Compare row counts before and after filtering. Check that the number of matched and unmatched lookup keys equals the number of input records, allowing for explicitly documented exclusions. If the report contains a time range, reconcile the first and last dates and check for gaps.
When totals do not reconcile, isolate the smallest failing slice: one date, one region, one category, or one source tab. Then compare the raw rows, the formula's included rows, and the independent calculation. Common causes include duplicated records, text-versus-number types, invisible spaces, inconsistent date boundaries, filters that do not affect ordinary aggregation functions, and formulas that stop before the last populated row.
6. Check failure and boundary behavior
A report is not verified merely because its normal case works. Test what happens when an input is blank, malformed, missing, negative, duplicated, outside the date range, or exactly equal to a threshold. Inspect whether the result becomes an error, a blank, a zero, or a plausible-looking value. Each behavior may be acceptable in one context and misleading in another, so decide the intended behavior before editing the formula.
Also review calculation settings and platform differences. Microsoft states that Copilot editing is supported when Excel's Calculation Options are set to Automatic [1]. Google says Gemini in Sheets works best with native Google Sheets files and instructs users to convert an .xlsx file to Google Sheets to use Gemini features [2]. If a workbook moves between Excel and Sheets, rerun the test cases after conversion rather than assuming identical behavior.
7. Review the finished report, not only the formula cell
After the formula passes tests, inspect the report as a reader will see it. Check number formats, percentage versus decimal display, rounding, signs, labels, date periods, and whether a stale cached value is being shown. Refresh or recalculate as appropriate, then reopen the file or sheet and confirm that the result persists. Verify that charts, pivots, summaries, and narrative text use the same data range and period as the checked formula.
Do not include more source data than the recipient needs. Follow the applicable organizational handling rules for confidential or personal information. Google warns users not to submit personal, confidential, or sensitive information to Gemini in Sheets and notes that prompts, generated content, and referenced Workspace content may be collected under its feature terms [4]. That is a product-information caveat, not a substitute for your organization’s current privacy or security requirements.
A practical formula-verification checklist
Use this checklist before delivery. Mark an item complete only when you can point to the evidence.
- The calculation question, inclusions, exclusions, period, and units are written down.
- The original workbook or formula is preserved, and the accepted formula is identifiable.
- References, ranges, criteria, copy behavior, and data types were inspected.
- A tiny known-answer test passed, including at least one boundary or empty case.
- An independent calculation path produced the same result and included the same records.
- Subtotals, row counts, dates, and grand totals reconcile to trusted controls.
- Errors, blanks, duplicates, missing matches, and zero-denominator behavior are intentional.
- The final presentation, refresh state, chart ranges, and labels were reviewed.
- Any unresolved ambiguity or limitation is documented for the recipient or reviewer.
When to hold the report
Hold the report when the requirement is ambiguous, the independent method disagrees, a control total fails, the formula depends on an unverified external link, or a boundary case produces an unexplained result. For high-stakes operational decisions, ask a suitably qualified subject-matter reviewer to check the underlying assumptions and current applicable rules. This guide is a spreadsheet quality-control process; it is not legal, tax, accounting, investment, medical, employment, privacy, or other professional advice.
