Recalculate AI Spreadsheets Before You Trust the Totals

An AI assistant can produce a workbook with tidy formatting, plausible formulas and a confident explanation of the total. None of those establishes that the number saved in the file was calculated from its current inputs. Even a successfully recalculated number can answer the wrong question.

For a team sending a capacity plan, pricing model or operational forecast to another person, the release decision is concrete: what evidence makes this particular spreadsheet safe to rely on for its stated purpose? The answer requires separating formula construction, calculation freshness and business correctness.

This guide concerns generated or programmatically modified workbooks, especially XLSX files. The implementation behaviours below come from primary documentation checked on 18 September 2026. The release method and arithmetic example are AIEngine's engineering analysis, not a claim that we benchmarked the named engines or validated a customer workbook.

A formula and its saved answer are different artefacts

In SpreadsheetML, the formula and its cached result occupy different elements. Microsoft's Open XML formula documentation, updated in January 2025, explains that the formula is stored in <f> and the last calculated value in <v>. The cached value can be omitted, leaving calculation to the reader.

That separation is useful, but it creates several possible states. A file can contain the intended formula and an old answer. It can contain a newly calculated answer from an incorrect formula. It can also contain a manually supplied answer that happens to match today's inputs while the formula would produce something different.

Do not reduce those states to “the spreadsheet opened”. Define three checks:

• Structure: the expected sheets, inputs, formulas, ranges and units exist.
• Execution: the selected engine evaluated the intended workbook state and saved its results.
• Meaning: independently specified checks support the decision the workbook is meant to inform.

Each check answers a different question. A valid file format says little about missing rows. A fresh calculation says little about an incorrect denominator. A correct screenshot says nothing about whether the downloadable file contains the same result.

Know whether the library writes or calculates

The openpyxl formula guide states that the library does not evaluate formulas. Its load_workbook API documentation separately describes data_only: it selects stored values instead of formula text when reading formula cells. Turning that option on is not a recalculation request.

XlsxWriter's formula documentation describes another important default: it writes a cached zero and a flag requesting recalculation when the file is opened. It can also accept a supplied formula result. Neither writing the flag nor supplying a result establishes that an engine has independently calculated the formula; non-calculating readers may display the cached zero.

The practical consequence is architectural. A pipeline that ends after a writer saves the file has completed construction, not necessarily calculation. Adding a second reader that retrieves cached values does not fill that gap.

This is particularly easy to miss when the assistant produces both the formula and an explanatory answer. If it then inserts that answer as the cached value, the workbook and the explanation can agree because they share the same unchecked origin. Agreement between two outputs of one generation step is not independent evidence.

Record which component creates formulas, which evaluates them and which inspects the final saved results. Where there is no evaluator, mark the deliverable as an uncalculated template rather than presenting its displayed totals as verified.

Choose the calculation engine before the handoff

Start with the recipient's intended use. A workbook intended for ongoing editing in Excel has a different compatibility requirement from a fixed values-only export. A successful run in one engine should not be advertised as proof of identical behaviour in another.

Inventory the formula families and workbook features that matter: lookups, dynamic arrays, named ranges, cross-workbook references and any custom functions. Select an engine and version against that inventory. For unsupported features, either change the design with the owner's agreement or withhold the affected output. Silently replacing a difficult formula with an invented value is not a compatibility strategy.

API names also conceal different outcomes. Apache POI's evaluation guide distinguishes evaluate(cell), which returns a result without changing the cell; evaluateFormulaCell(cell), which updates the cached result while retaining the formula; and evaluateInCell(cell), which replaces the formula with its result. Its evaluator also has function-support limits. Pick the operation that matches whether formulas must survive the handoff.

The LibreOffice XCalculatable API distinguishes calculation of dirty cells from calculateAll(), which recalculates all cells. A release log should name the actual request, not just say “calculation enabled”.

Full calculation may cost more time. For a consequential exported workbook, that cost can be preferable to uncertainty about which dependencies were marked for recalculation. Measure the cost on the real workbook rather than assuming that every file needs the same policy.

Control the session as well as the file

Opening a workbook in a familiar application is not a complete execution specification. Microsoft's calculation-mode guidance, updated in March 2026, explains that open Excel workbooks share a calculation mode. The first workbook opened helps determine that mode, and later workbooks inherit the session's setting.

Use a controlled calculation session and explicitly set or verify the intended mode. Otherwise an unrelated workbook can become an undocumented condition of the release. Keep the source artefact separate from the evaluated output so the latter can be compared and investigated.

Decide what constitutes an input before calculation. If a workbook depends on another file, identify the exact approved version. If it depends on a clock, random draw or external feed, specify whether the release represents a frozen snapshot or a live model. A “current” total without an input cut-off is difficult to interpret later.

Treat access to external data as a separate permission decision. Do not grant a generated workbook unrestricted network access or enable macros merely to make its errors disappear. Where required dependencies cannot be supplied safely, fail the release or clearly constrain it to a supported subset.

The same principle underlies our reproducibility and replay-contract guide: the execution conditions belong in the claim. For spreadsheets, that includes the engine, calculation settings, input snapshots and any approved extensions, not just the generated file.

A worked example: fresh can still be wrong

Consider a synthetic workload sheet. Cells B2, B3 and B4 contain 90, 60 and 30 minutes respectively. All three jobs belong in the total. The correct formula is =SUM(B2:B4), and the expected result is 180 minutes.

Suppose the first job previously took 60 minutes. An older saved result of 150 can remain plausible after B2 changes to 90. Separately, a generator might write =SUM(B2:B3), excluding the third job. That incorrect formula also produces 150 on the current inputs.

This is an arithmetic illustration, not an observed engine test. Its purpose is to show why checking only for a successful calculation or an absence of error cells misses a material defect.

A useful test would obtain the three job records independently of the workbook's total formula, add their minutes, and compare that result with the saved output. It would also verify that the intended job identifiers appear exactly once. Matching totals alone can hide an omitted job offset by a duplicated one.

Then change only the third job from 30 to 45 minutes in a test copy. The expected increase is 15. A truncated range remains at 150, exposing the missing dependency. Return to the approved inputs and recalculate the final deliverable; never accidentally release the deliberately mutated test copy.

Build checks from the decision, not from the generated formula

The independent check must not simply translate the generated formula into another language. That can reproduce the same conceptual error with different syntax. Derive expected outcomes from the specification and source records.

For a workload plan, useful invariants include complete job coverage, consistent duration units and a total equal to the sum of included jobs. For a ratio, specify the numerator, denominator, treatment of missing values and whether aggregation occurs before division. Decide rounding rules before comparing outputs.

Our number-extraction guide addresses the earlier boundary: preserving magnitude, units and precision when values enter the system. Recalculation cannot repair minutes that were mistakenly entered as hours. Keep that input validation separate from the spreadsheet's execution check.

Use small fixtures with an answer a reviewer can derive without the workbook. Include a single row, an empty eligible set, a boundary date, a missing required input and a value that exercises rounding. Test whether adding an eligible row expands the intended calculation and whether removing one removes its contribution.

Define tolerances according to the calculation's purpose. Exact integer counts should not need a broad numerical tolerance. Where floating-point comparisons are appropriate, document the allowed absolute or relative difference and its rationale. A tolerance must not conceal missing records or a changed unit.

Ask a model to help propose edge cases if useful, but keep the expected results reviewable. This follows the distinction in our AI output-validation guide: a plausible generated object still needs checks appropriate to what will happen next.

Save, reopen and bind the evidence to the release

After calculation and checks, save the evaluated workbook to the release path. Reopen that saved file with readers appropriate to inspecting both formula definitions and stored results. Verify the agreed outputs without relying on the earlier in-memory object.

Do not make another programmatic edit afterwards and assume the prior evidence still applies. Even an apparently harmless processing step can change the delivered artefact. If a later step is necessary, put it before the final inspection or repeat the relevant checks afterwards.

A compact calculation receipt should record:

• The final file's digest and the release identifier associated with it.
• The intended recipient application and the supported editing or viewing mode.
• The writer and evaluator versions, including relevant extensions.
• The input snapshot identifiers, cut-off and approved external dependencies.
• The calculation mode and operation requested, with completion or failure evidence.
• The formula-preservation requirement and results of checking it.
• The critical outputs, expected values, comparison rules and test outcomes.
• Any excluded features, unresolved warnings and the accountable reviewer.

Keep the receipt outside the file if inserting it would require rewriting an already verified workbook. A digest binds evidence to bytes; it does not certify mathematical correctness or make the file immutable. The receiving process must still ensure it retrieves the matching artefact.

Protect the evidence proportionately. A test record can refer to controlled input snapshots without copying sensitive source data into an unrestricted log.

Make the handoff promise explicit

There are three defensible deliverables: a tested editable workbook, an explicitly uncalculated template, or a checked values-only snapshot. Name which one is being supplied. A snapshot is useful when recipients need a fixed result, but it gives up recalculable logic. A template preserves logic but must not borrow the authority of an executed report.

For editable workbooks, state which application and version were checked and what changes require recalculation and review. Do not promise that arbitrary future edits remain valid. For snapshots, preserve the calculation evidence and input cut-off so a fixed number does not masquerade as a live one.

Measure cache-versus-recalculated mismatches, unsupported-feature failures, independently detected logic defects and defects found after handoff as separate categories. Also measure calculation time and how often another person can reconstruct a sampled release from its receipt. Combining everything into a generic spreadsheet pass rate obscures which control needs improvement.

Revisit the checks when the writer, evaluator, workbook template, formula families or input contract changes. The release claim should remain specific: this saved file, calculated under these conditions, satisfied these independent checks for this stated use. A formula is part of that evidence, not a substitute for it.