How to Check an AI-Generated Spreadsheet Formula Before Reusing It

When is an AI-generated formula ready to reuse?

Reuse an AI-generated formula in a monthly report only when four checks agree: the metric is defined, the references match that definition, tests with known outcomes behave as expected, and a separate calculation reconciles the result. If the report owner has not settled what counts, stop: no test can establish whether a formula follows an unknown rule. For example, as a hypothetical illustration, a formula may correctly total rows dated in May yet still be wrong if the report defines May by invoice date and the formula uses payment date. A working result alone is not enough; the rule and the route to the result must both be clear.

Before reuse, write the metric in ordinary language, inspect the cells and ranges it reads, compare its output with deliberately chosen cases, then calculate the figure by another route. Treat a mismatch, unexplained reference, or unsettled definition as a reason to revise or ask the report owner. A familiar internal total may need only a brief check when its rule and data are stable and the figure is reversible; the effort should fit the consequences, while the four conditions still define a pass.

What does formula-generation research establish about reuse?

The research supports checking an AI-generated formula against the actual report rule; it does not tell a worker the probability that a particular formula is wrong. The studies examine different bounded tasks, and their findings should not be combined into a workplace error rate. They help clarify what a check needs to establish: execution correctness means that the spreadsheet software returns a result, while semantic correctness means that the result implements the intended reporting rule. Either can hold without the other. A formula can calculate without an error message yet count the wrong dates or categories; a formula intended to follow the rule may also fail to calculate as written.

In “Experimenting with ChatGPT for Spreadsheet Formula Generation: Evidence of Risk in AI Generated Spreadsheets,” the author reports experiments asking ChatGPT to generate spreadsheet formulas and computational outputs. The abstract describes correct outputs in some circumstances, alongside breakdowns when information was limited or uncertain or a problem was too complex. That combination matters: the results do not support treating all generated formulas as unreliable, but neither does success under some experimental conditions establish that a formula fits a particular monthly report. The abstract does not establish a representative sample of workplace tasks, adoption rates, or the frequency of incorrect formulas in current reporting. Its practical implication is narrower: specify the task and verify the output against the rule before reuse.

“Benchmark Dataset Generation and Evaluation for Excel Formula Repair with LLMs” describes a synthetic benchmark of 618 formula-repair samples expanded from curated online-forum examples. The authors describe an LLM-as-judge process and execution-based checks, while treating semantic fidelity as a separate concern. This is a constructed repair dataset and model evaluation, not a field study of formulas generated for monthly reports. It is also not the separately titled SpreadsheetBench study. The sample count describes the benchmark’s cases; it does not mean 618 workplace formulas were observed, nor does it yield a worker’s chance of encountering an error. Its value here is conceptual: passing an execution check and matching intended meaning are distinct evaluation questions.

“Large Language Models for Spreadsheets: Benchmarking Progress and Evaluating Performance with FLARE” presents a benchmark spanning spreadsheet function execution, formula generation, data manipulation, auditing, and reasoning. That wider task framing shows how researchers can test specified capabilities under an evaluation setup. The accessible abstract does not provide a basis here for reporting scores or transferring results to an organization’s monthly process. A benchmark result, even when it demonstrates capability on defined tasks, is not a measure of workplace adoption or error prevalence.

Taken together, these sources offer reasons to check the formula’s meaning and behavior, not grounds to reject AI assistance or claim a universal failure pattern. Formula-generation experiments include successful cases; repair and broader spreadsheet benchmarks evaluate bounded tasks. None establishes how often a knowledge worker’s monthly-report formula will be wrong. For the reuse decision, the evidence points back to the local rule: test whether the generated expression follows it, then confirm the result through a separate route.

Sources: Experimenting with ChatGPT for Spreadsheet Formula Generation: Evidence of Risk in AI Generated Spreadsheets; Benchmark Dataset Generation and Evaluation for Excel Formula Repair with LLMs; Large Language Models for Spreadsheets: Benchmarking Progress and Evaluating Performance with FLARE

What exactly is the report counting?

Before judging a formula, pin down the metric in a short contract the report owner recognizes. State the unit counted (transactions, customers, cases, or dollars), the reporting period, the date field that assigns a record to that period, the included and excluded records, and any grouping. For a rate, write the numerator and denominator separately and state which population belongs in each. This is a definition of the requested measure, not a formula specification: it tells you what the calculation must represent before you inspect how the workbook represents it.

A useful contract is concrete enough that two people could decide the same row's treatment. “May sales” is not yet precise. It might mean transactions whose transaction date falls in May, payments posted in May, or invoices issued in May. It might count each transaction once, distinct customers, or net dollars after reversals. Specify the calendar or fiscal period and its exact start and end, along with whether the end date is inclusive. If the report groups by region or product, say which source field supplies that grouping and how blank or unassigned values are handled. These details may already appear in a reporting guide, approved workbook notes, or a prior owner decision; locate that authority and apply it rather than making a new policy.

Consider a hypothetical monthly count in which one order has a transaction date of May 31 but is posted on June 1. A formula using the posting-date column excludes it from May; one using transaction date includes it. Both can calculate cleanly, but they answer different questions. The difference is not resolved by changing operators or checking for spreadsheet errors. If the report owner defines the measure as activity that occurred in May, the transaction date controls; if the organization reports when activity entered its system, posting date may control. The label “May orders” cannot settle that choice by itself.

Status rules can create the same policy fork. Suppose rows are marked Open, Closed, or Reversed, and the report asks for “completed orders.” Does Closed alone qualify, or should a later reversal remove a previously closed order? Does a partially fulfilled order count as completed? A formula author can faithfully apply a stated rule, but silently choosing among these interpretations transfers a business decision to whoever edits the cell. Identify the exact status values and any precedence rule, such as a reversal superseding an earlier completion, only when an authoritative definition supports it.

Once written, use the contract as a row-by-row test of meaning: each counted record should satisfy the period, date-field, status, unit, and grouping rules, while each excluded record should fail at least one stated condition. If wording is unsettled, show the report owner the competing interpretations and a boundary example such as the May 31 order posted June 1. Ask which event the metric is intended to measure and record the answer where future preparers can find it. Until that decision is made, mark the figure provisional or hold it under the team's process; do not let the formula author turn ambiguity into an undocumented rule.

How do you inspect what the formula actually reads?

After the metric contract is settled, translate the formula into a plain inventory of inputs: the data range, the date or status column, each criterion, the output column being summed or counted, and any lookup or helper range. For a formula such as a conditional count, read each range-and-condition pair aloud and match it to one contract rule. Check the sheet name and headers, then verify that the range starts at the first intended data row and ends at the last available one. A formula explanation can suggest what the expression means, but the actual references in the workbook are the evidence of what it reads.

Check whether references are relative or fixed. In spreadsheet notation, a reference such as A2 may shift when copied, while $A$2 is anchored; mixed references can hold a row or column constant. Neither form is inherently correct. Ask whether the formula is supposed to advance by row, preserve a criteria cell, or continue to point at a fixed lookup table. Compare neighboring formulas in the same calculated column: if a copied formula should move from row 2 to row 3, its data references should shift consistently, while deliberate fixed references should remain fixed. An unexplained change in that pattern is a question to resolve, not automatic proof of an error.

The workbook's precedent and dependent tracing can help expose those relationships. A precedent is a cell or range supplying a value to the selected formula; a dependent is a cell whose formula uses the selected cell. Microsoft’s “Display the relationships between formulas and cells” documents Excel tracer arrows for showing these links. Select the result cell and trace its precedents to see which source ranges feed it; if a helper cell appears, continue tracing that cell as needed. Tracing dependents can show which downstream report cells rely on a value being checked. Use the arrows to guide inspection, then open the relevant cells and read their headers and contents directly.

For example, imagine the contract says to count orders by transaction date during May and include every row in the current data table. A hypothetical formula uses the posting-date column, or its range ends at row 500 while a new May record has been appended at row 501. Either formula may return a plausible total without a syntax warning. Compare every reference against the contract: does the date range point to transaction date, do its first and last rows cover the intended records, and do the criteria include the agreed statuses? For a copied formula, inspect both the first and last populated rows and at least one middle row so a shifted or exceptional reference is not hidden by checking only the top cell.

Check sheet links and external sources as well. A reference to another tab should lead to the expected table and column; an external workbook link should resolve to a known source and version. Microsoft notes that Excel cannot trace some relationships, including certain links to a closed external workbook. A missing arrow therefore does not show that no dependency exists. If a range, link, or helper calculation cannot be followed, stop short of certifying the result and ask the workbook or source owner to establish the lineage. Visible references show the route the formula takes; they do not establish that the source is authoritative or that the chosen field expresses the agreed metric.

Sources: Display the relationships between formulas and cells

What can Excel's built-in checks tell you?

Excel's built-in checks can flag mechanical patterns worth investigating, but they cannot decide whether a formula expresses the report's intended rule. Microsoft’s “Detect formula errors in Excel” documents error values and rule-based checks that can identify some inconsistent neighboring formulas or possible omitted adjacent cells. Treat a flag as a review lead: inspect the relevant expression and range against the metric already defined, then decide whether the difference has a valid reason. These features screen for specified patterns; they do not certify a monthly metric.

A short sequence keeps that role clear. First, if formulas or source values have changed and the workbook uses manual calculation, recalculate according to the workbook's normal procedure; otherwise confirm the displayed result is current. Next, inspect visible error values and use Excel's formula error checking on the result and relevant calculated cells. Then scan neighboring formulas where the same operation should repeat down a column or across periods. A change in range, criterion, or reference is a question to resolve, particularly near the first or last data row. Check the expression and its context rather than accepting the warning text as a verdict.

Investigate each warning by comparing the flagged formula with the stated rule and the corresponding neighboring cells. An exception may be intentional: a subtotal row, a special reporting period, or a deliberate one-off adjustment can break a repeated pattern. Record the reason or confirm it with the workbook owner if it is not evident. Conversely, a clean check is not a pass criterion. A formula can consistently use the wrong date field, omit a whole block, or apply a consistently mistaken condition without triggering a pattern warning. Microsoft describes its error-checking guidance as non-exhaustive and does not report a detection rate. Also note which check produced the flag and what you examined, so a later preparer can distinguish a reviewed exception from an overlooked message. If Excel presents a choice to ignore or reset a warning, do so only after establishing why the formula differs; clearing the indicator by itself changes no evidence about the calculation. Use warnings to locate questions; use the metric definition and evidence in the workbook to resolve them.

Sources: Detect formula errors in Excel

Which test cases can expose a wrong formula?

Choose test cases from the metric contract, and decide each expected outcome before running the formula. A compact set has one record that must count, one that must not, and a boundary record such as the first or last reporting date. This makes the test discriminating: it challenges both inclusion and exclusion rules instead of merely checking whether the result looks plausible. For a rate, specify the expected numerator and denominator separately, because a correct-looking percentage can conceal an error in both components or offsetting errors between them.

For example, suppose a hypothetical monthly report counts completed orders by transaction date in May, excludes reversed orders, and counts each order once. Write a small table before checking the workbook: a completed order dated May 1 should be included; a completed order dated April 30 should be excluded; a reversed order dated May 12 should be excluded; and a completed order dated May 31 should be included if the reporting period includes its final calendar day. These are proposed test cases, not incidents observed in a workplace. Mark the expected treatment from the agreed definition first, so the formula's output cannot quietly redefine the rule.

Compare row-level inclusion, not only the monthly total. For each selected record, check whether the formula includes it and, if the metric sums amounts, what contribution it makes. For a rate, record separately whether each case belongs in the numerator, denominator, both, or neither. A hypothetical duplicate identifier is useful only if the contract says whether the unit is rows, transactions, or distinct orders; without that decision, the test exposes an unresolved definition rather than a formula defect. Likewise, test a blank date or a status exclusion only when that category can occur in the source and the reporting rule specifies its treatment.

A few handpicked cases challenge particular assumptions; they cannot establish that every unseen combination is handled correctly. A boundary test can expose an inclusive-versus-exclusive date mistake, while mixed conditions such as a reversal posted after month-end may require another case if the contract covers that interaction. Expand coverage when the data structure, formula, or rule changes, and ask an independent reviewer to challenge the cases when the figure's consequences warrant it. The practical value lies in making expected outcomes explicit and comparing them with actual row behavior, not in treating a short list as proof of universal correctness. Preserve the chosen examples with the metric definition or review notes when they will help the next monthly preparer repeat the check; update them when fields, categories, or period rules change.

How can you reconcile without repeating the same mistake?

A useful reconciliation applies the same agreed metric definition to the source records through a meaningfully different route. “Different” means the second route makes a fresh choice about which records qualify and how the amount is derived, rather than merely re-entering the generated expression. The purpose is to challenge the formula’s row membership and calculation logic, not just to produce another number that happens to match. This is a practical check for this report, not a statistically validated audit protocol or proof that either route is correct.

For a count or sum, one route is to filter the underlying source table using the agreed period, date field, status, and other criteria, then count qualifying rows or add the relevant amounts independently. Before accepting a match, inspect the filtered rows: confirm that expected records appear, excluded records do not, and the selected amount field is the one the definition requires. If the metric is a rate, derive numerator and denominator separately from their stated populations, then calculate the rate. Matching percentages alone can conceal offsetting errors if both components are wrong in compatible ways.

A second spreadsheet formula can help only if its mechanics genuinely differ. Copying the same expression, criteria arrangement, or range boundaries into another cell risks carrying over the original omission or mistaken assumption. Changing software does not automatically create independence either: another tool may inherit the same source range, field choices, filters, and interpretation of the rule. A reviewer who independently starts from the written definition and source records can be more challenging than a fresh calculation built from the original formula’s setup. Choose a route that is feasible with your access and sufficiently different to expose the specific choices at issue.

If the routes disagree, locate the difference at the record and component level: which rows were included, what values they contributed, and which records belong in each side of a rate. Recheck those decisions against the source records and metric definition. Two routes can still share incorrect source data or a mistaken business definition, so agreement is not a guarantee; if the records are incomplete or the definition does not resolve a disputed case, take that question to the authorized report owner. Do not resolve disagreement by choosing whichever total looks more plausible.

For a small report, a simple record of the independent route can make comparison clearer: note the filter or rule used, the row count, and the resulting sum or rate components. This does not need to become a new control system. It helps identify whether a match came from genuinely matching membership and arithmetic, or only from coincidentally matching totals. Keep the comparison tied to the same period and source snapshot so that a later data refresh does not turn a timing difference into an apparent formula disagreement.

Why can a wrong reference leave this month's total unchanged?

A wrong reference can leave the current total unchanged when it points to a cell whose value happens to equal the intended cell, or when the affected records do not change the measured output in the workbook examined. That coincidence does not make the reference correct. If a value later changes, rows are added, or the workbook is reused for another period, the same reference may produce a different and consequential result. The current aggregate therefore cannot, by itself, establish that the formula reads the right records.

The Dartmouth field study “Impact of Errors in Operational Spreadsheets” examined 25 operational spreadsheets from five organizations. The researchers reviewed workbooks, then discussed potential issues with spreadsheet developers to confirm errors and separately assessed their quantitative impact on the studied outputs. They reported 117 confirmed errors; 47 had no measured quantitative impact on those outputs, and some faulty references reached cells with the same value. These are findings from that sample and its examined outputs, not a rate for spreadsheets generally.

The result is easy to misread if “no measured impact” is treated as “not an error.” Confirmation of a defect concerned whether the spreadsheet was wrong; impact assessment concerned whether that defect changed the outputs measured in the study. A reference can be defective even when a same-valued substitute masks its effect at that moment. The distinction explains how a workbook can display a plausible or unchanged figure while containing a reference problem that may matter under changed inputs or reuse. The paper’s audit predates generative AI and did not study AI-generated formulas, so it cannot establish the frequency or behavior of errors in those formulas.

The bounded practical implication is to compare what the formula includes and reads, not only whether its total matches an expected number. Trace relevant references to their source cells, and use discriminating records whose values or eligibility make the intended rule observable. If a test set happens to give the same result under two different references, select or construct a case that distinguishes them when the source data and rules allow it. This applies the study’s warning to a local check; it is an inference, not a reported study recommendation or a claim that every unchanged result conceals a material defect.

The sample also limits how far the lesson travels: 25 workbooks from five organizations is a small, non-random set, and the analysis concerns the spreadsheets and outputs the researchers examined. It did not show that every unchanged output hides an important error, that every same-valued reference becomes harmful later, or that every spreadsheet needs exhaustive review. An unchanged monthly total is one weak signal about a formula’s correctness, not a reason to abandon reuse. Reuse remains a decision about whether the rule, references, tests, and separate calculation agree for the report at hand. The finding is therefore a reason to inspect the relevant dependency, not to infer a future failure without evidence.

Sources: Impact of Errors in Operational Spreadsheets

How much review is proportionate to this report?

Review effort should rise with the consequence of a wrong figure and with uncertainty about the rule, source, or formula behavior. This is a practical judgment framework, not a validated score: ask what decision the number informs, how costly an error could be, and what remains unresolved. A familiar count used for a reversible internal discussion may need only the normal preparer's documented self-check when its definition, source, and tested mechanics are stable. A result that affects pay, cash, customer treatment, compliance, or external reporting deserves the review required by the organization's procedure, which may include the report owner or a designated control reviewer.

The consequence label alone does not show that a formula is wrong. It tells you how much confidence the process should establish before people act on the result. If a monthly internal count is low impact, the definition is settled, the source is known, and the expected cases behave correctly, repeating a full workbook audit every month may add little. Follow the team's normal review policy and retain enough working notes to show what was checked. If the figure later informs a payment or formal filing, reassess the review at that use; a stable formula does not make every use equally consequential.

Uncertainty can raise the review level even when the immediate amount seems small. Involve the report owner when two plausible metric definitions would include different records, because choosing between them is an authority question rather than a formula-editing task. Ask the data or workbook owner to establish lineage if a source range, external link, or transformation cannot be followed. Ask a reviewer with appropriate authority to examine behavior if a relevant test fails, the separate reconciliation disagrees, or the formula changes in an unexplained way. Each escalation should name the open question: definition, source, formula behavior, or consequence under local policy. That makes the request answerable instead of asking someone to approve a vague feeling of risk.

Your own authority also sets a boundary. If you prepare the report but do not own its definition or controls, do not silently redefine the measure, waive a required review, or certify a result beyond your role. Send the specific discrepancy and its effect to the normal owner, and label the figure provisional or hold it according to the team's process while it is unresolved. If you are authorized to approve it, apply the established control and record the basis for that approval. A proportionate check is therefore neither automatic trust nor maximal review: it is enough review to resolve the material uncertainty and meet the decision's required control, with escalation when either is outside your remit.

What should you do before the next monthly report?

Before reusing the formula, write down the metric rule, then choose one record that should count, one that should not, and one boundary case; state each expected outcome before checking it. Inspect the formula's references against that rule and reconcile the result separately from the source records. Reuse it when the definition is authorized, the references and cases agree, and the independent calculation matches. If any of those checks conflict, revise and retest only within your authority; otherwise hold the figure as provisional and take the specific definition, access, or behavior question to the report owner. If you want to assess how AI may affect your wider task mix, the free [task checker](/ai-job-risk-checker) provides task-level change-pressure signals. It does not validate spreadsheet formulas or estimate a probability that you will lose your job. Keep that note with the report or in the place your team normally records preparation decisions, so the next preparer can see which rule and source version the result depended on. If you cannot inspect the source data or edit a shared workbook, do not imply that you completed those checks; state what you could verify and what access or owner decision is still needed. That preserves a clear handoff without turning an unresolved question into an approval.

Questions readers ask