Fix the spreadsheet before asking AI what it means

The answer can be tidy and still wrong

A manager uploads a sales export and asks, “How did we do this week?” The assistant produces a beautiful chart and says sales reached THB 64,600. Everyone starts discussing why sales increased. Nobody asks whether the sheet contains sales, payments, duplicates or cancelled orders.

The first useful AI task is to inspect what the file can support. Before asking for insight, write the business question in terms another colleague could calculate independently. For example: “What was the value of confirmed orders placed this week, after recorded credits, in THB?” That definition excludes cash collected for earlier orders. It also prevents a dollar amount from quietly becoming baht.

The example below is fictional. Its figures teach reconciliation, not accounting treatment. Your finance owner should supply the reporting definitions used in your business.

A small export with four ways to mislead you

Adding the numeric cells produces 64,600. It is arithmetic without a usable unit or business definition. Under our stated reporting rule, the THB order subtotal is 12,000 + 18,000 - 3,000 = THB 27,000. The USD 600 order remains separate until an approved conversion rule is provided. Cash received requires its own calculation from payment records.

Do not hide the cancelled order or duplicate by deleting them. Retain them in the original export and explain their treatment in a working table.

Agree what may change

Make three sheets: Original, Prepared and Checks. Protect the original from editing. In Prepared, add a stable source-row reference, normalized fields and an inclusion reason. The row marked duplicate still exists; its reporting status becomes excluded with a reason pointing to O102.

Normalize whitespace and obvious formatting only where meaning stays intact. Keep invoice references and customer IDs as text when leading zeroes matter. A date such as 03/04 must remain unresolved until the source convention is known. A Thai-language export may use a different year representation from another system; do not subtract years globally because one cell looks unfamiliar.

Two matching amounts are not enough to establish duplication. Check the stable order identifier, line identifier and export history. Two legitimate line items can have the same customer, date and price. Ask for a candidate-duplicate list before authorizing exclusions.

Microsoft's data-cleaning guidance is a practical spreadsheet reference. The business rules still come from your organization, not from a cleaning feature.

The correction that changes the management conversation

The first AI draft says: “Weekly sales were THB 64,600, with the largest contribution from O102.” A reviewer asks it to show the calculation and notices O102 twice. The assistant fixes that and returns THB 46,600. That is still wrong.

The stronger correction is to revisit the definition, not keep subtracting suspicious rows. The reviewer separates confirmed orders, cancellations, credits and payments, then partitions currency. The accepted output says: “Confirmed orders after the linked credit total THB 27,000, plus USD 600 shown separately. The export also contains a THB 10,000 payment for an earlier period. No combined currency total has been calculated.”

That answer is less dramatic but usable. It also explains what remains unknown. The manager can now ask about order value and cash collection separately, instead of inventing a growth story from a mixed column.

Give the total a visible reconciliation

Checks should show counts as well as money. Here, seven source rows become three included THB reporting rows, one separate USD row and three excluded rows. Every source row has one disposition. If a cleaning step leaves six source references, something disappeared.

Show the THB numeric total of 64,000, then the adjustments: remove the duplicate 18,000, cancelled 9,000 and prior-period payment 10,000 to reach 27,000. The negative credit was already included in that arithmetic. Keep the USD amount outside this bridge.

Test missing amounts, blank currencies, repeated exports, negative values and a credit without a matching order. A blank should produce a visible exception, not silently become zero. Compare the important total using an independent formula or source-system report. Asking the same assistant to repeat its answer is not independent reconciliation.

Make the preparation repeatable

Save the agreed inclusion rules alongside the prepared file so next week does not become another improvised cleanup. Run the same checks on the next export and compare the exception list before changing the rules. A new record type should prompt a classification decision, not silently inherit the treatment of the closest familiar label.

Keep a short change log: rule changed, reason, owner and affected periods. If the team decides that credits belong to a different reporting period, revisit the comparison as well as the current total. Otherwise an apparent week-on-week movement may come from the new definition rather than the business. Make that distinction visible in the report.

Copy a preparation brief before requesting a chart

Business question: [precise measure and period]
Source export and extraction time: [reference]
One row represents: [order / line / payment / other]
Reporting currency and conversion rule: [approved rule]
Preserve Original. Work on a separate Prepared table.
Keep a source-row reference on every output row.
Propose duplicate, date and missing-field exceptions.
Do not infer missing amounts, currencies or statuses.
Return: inclusion rules, row dispositions, totals by currency,
reconciliation to the source, and unresolved questions.
Do not write a performance narrative until Checks passes.

Start with one weekly export and reconcile one important number. Have the person who owns that number approve the inclusion rules. Only then ask for a chart or explanation, and make its title name the exact measure: confirmed order value, invoiced sales or cash received.

Sources and dates

Sources checked on 20 October 2026. Worked examples are teaching examples unless explicitly attributed. Provider documentation describes product behaviour; the operating recommendations are DLVX editorial judgement.

• Microsoft: top ten ways to clean your dataSpreadsheet cleaning reference; the worked dataset and reconciliation rules here are fictional teaching material. Checked 2026-10-20.