AI spreadsheet analysis: check the data before the story

An export can support one decision and be unsuitable for another. An order report may answer which submitted orders need fulfillment, but not recognized revenue or cash collected. Before using AI, state the intended decision, reporting period, source system, row grain, exclusions, and accountable reviewer. Ask what a single row represents: an order, an order line, a customer, a daily snapshot, or a revision to an earlier record.

Write a short acceptance statement: “Produce a CAD order-value summary for the stated reporting period, using unique approved order records, with unresolved amounts held separately.” This gives both the analyst and the model a testable boundary. A plausible chart is not evidence that the data meets that boundary. If the input cannot answer the question, the useful output is a specific request for missing information.

The UK Government Data Quality Framework distinguishes completeness, uniqueness, consistency, timeliness, validity, and accuracy, and emphasizes fitness for purpose. These dimensions are a useful checklist rather than a single quality score: a fully populated field can still contain the wrong value.^[1]

Keep the original file unchanged and record its filename, export timestamp, reporting timezone, and source. Add a stable source-row ID in a working copy so every exception can be traced back. Keep transformation rules outside the raw sheet. For restricted material, use the organization’s approved storage, access, and retention rules; a convenient upload location is not automatically an authorized one.

Treat account numbers, postal codes, SKU values, and long IDs as text unless the source definition says otherwise. Microsoft documents that Excel can remove leading zeros and convert large numbers, and describes importing or configuring data to preserve the original format. A numeric display format cannot reconstruct digits already lost on import. Validate preservation before creating joins or deduplicating.^[2]

For each column, document its type and meaning: currency amount, quantity, identifier, date, status, or free text. A blank amount means unknown unless an approved rule says otherwise; zero means a recorded zero. Do not replace blanks with zero simply because a formula then runs. Keep source date strings alongside normalized dates when locale ambiguity is possible.

Define required fields, allowed status values, valid date ranges, currency rules, and uniqueness keys before asking for a narrative. A duplicate order ID may be an error in a one-row-per-order report, yet expected in a line-item report. Test candidate keys against the documented grain. Flag possible duplicates for review rather than declaring that every repeated customer name is the same person or account.

Microsoft notes that Remove Duplicates permanently deletes data and recommends keeping a copy first. Use that warning as a reason to preserve evidence and record the business rule behind an exclusion. “Same invoice ID, line ID, and revision” is inspectable; “these rows look alike” is not. An owner must decide whether a repeat is a duplicated export, legitimate line, amended record, or separate transaction.^[3]

Give rules stable names such as ID_REQUIRED, DATE_AMBIGUOUS, AMOUNT_MISSING, and POSSIBLE_DUPLICATE. Record the source row, observed value, proposed treatment, owner, and final decision. Multiple findings on one row should remain distinct even when a summary reports the number of affected rows. Otherwise the team cannot tell whether quality improved or the counting method changed.

Separate deterministic work from interpretation. Code or workbook formulas should calculate totals, counts, date differences, and rule failures using the stated inclusion policy. AI can propose a rule, describe a pattern, or write an explanation, but the reviewer should be able to reproduce every reported number without trusting a paragraph. A narrative explanation cannot reconcile a missing row or establish why a customer behaved differently.

In Excel, SUMIFS sums values meeting multiple criteria. For a table named Approved, an example check is =SUMIFS(Approved[Amount],Approved[Currency],"CAD",Approved[Decision],"Include"). The exact formula must match the workbook’s actual table and column names. Check that included rows are eligible and amounts are numeric; a correct function used on the wrong range still yields a misleading answer.^[4]

Specify denominators alongside percentages. “Three of six source rows need review” is a row-level exception rate of 50%. It is not a statement that half of customers, orders, or revenue is defective. For a conversion rate, decide whether the denominator is visitors, leads, qualified opportunities, or unique accounts, and what reporting window applies. Do not let the model choose whichever denominator makes the result sound strongest.

This fictional CAD order export has six rows. R01 contains order 00101 for CAD100. R02 contains order 00102 for CAD200 with the date string 10/02/26. R03 repeats that order and amount. R04 records order 00103 with an explicit zero amount. R05 records order 00104 with a missing amount. R06 contains order 00105 for CAD300. The source does not yet explain the ambiguous date or repeated order.

The concrete first-pass output is an exception ledger for R02, R03, and R05, with R01, R04, and R06 eligible under the pilot rules. The eligible subtotal is CAD400 across three rows, including the legitimate zero. The raw sum of populated amounts is CAD800, but it is not an approved business total. Three of six rows need review, and the duplicate group affects two rows; those are different measures.

Suppose AI writes “Revenue was CAD800 across six orders.” The reviewer changes it to “Three eligible order records total CAD400; three source rows remain on hold. This file is an order export, not a revenue ledger.” Later, the source owner confirms October 2 for R02, identifies R03 as a repeated export row, and supplies CAD80 for R05. The documented corrected order total is CAD680 across five included orders. Those owner-supplied facts, not the AI’s guess, permit the change.

A useful native workbook has Raw, Rules, Exceptions, Approved, and Checks sheets. Raw preserves source values. Rules records definitions and versioned inclusion policies. Exceptions shows unresolved findings and decisions. Approved contains the reviewed analysis dataset with source-row references. Checks reconciles included, excluded, and held records to the raw population and lists expected versus actual results.

Ask for real formulas and actual supported workbook features, not screenshots of tables or values presented as if they had been calculated in Excel. Verify formula results in a compatible spreadsheet application before approval. If a requested function, chart, or interactive feature cannot be delivered, identify that limitation and provide an honest alternative. A CSV is useful portable data, but renaming it .xlsx does not make it a multi-sheet workbook.

Kona can help prepare the review workbook, generate explanations, and organize the exception ledger from authorized files and definitions. The business owner still confirms source semantics and ambiguous corrections. Keep external connections, macros, and hidden assumptions out of a first pilot unless they have a separate approved purpose and review process.

Before publishing a report, check that every source row has a disposition and that included, excluded, and held counts reconcile to the raw row count. Reconcile monetary values within each currency and keep missing amounts explicit. Trace a sample of summary figures back to source rows, including a zero, an exclusion, and a corrected exception. Test what happens when the next export introduces a new status or column.

Have the reviewer sign off on the rule version, source snapshot, unresolved limitations, and intended use. A report may be acceptable for internal workload planning while still unsuitable for invoicing or board reporting. If an exception could materially change the decision, label the report provisional or stop publication. Do not hide the exception ledger merely to produce a cleaner executive summary.

Track preparation time, reviewer time, unresolved exceptions by rule, corrections after release, and successful reconciliation runs. Measure row-level exception rate using affected source rows as the numerator and all source rows as the denominator. Track critical failures separately; averaging an identifier corruption into a general quality score can hide a defect that invalidates the analysis.

Compare recurring runs only when the cohort, cutoff, rules, and denominator remain comparable. More flagged exceptions can indicate better detection rather than deteriorating source data. Record changes in rule coverage alongside the rate. Keep a small set of known-problem fixtures so a new prompt or workbook revision must still catch missing amounts, duplicate groups, and damaged identifiers.

In days 1–3, document one report’s decision, source grain, field types, and expected totals. In days 4–5, build the raw copy, rule table, and exception ledger; test known defects before producing a narrative. Ask someone other than the author to reproduce the fictional example’s CAD400 first-pass subtotal and the owner-approved CAD680 corrected total from the recorded decisions.

In week two, run the workflow on an authorized real export beside the existing process. Review every exception, compare preparation and checking time, and inspect any difference in totals before distributing the result. Expand only after the owner accepts the reconciliation, identifier preservation, and treatment of unknowns. Keep changes to source systems outside the pilot: a reviewed workbook does not authorize automatic updates elsewhere.