How to Find Spreadsheet Anomalies and Build a Reconciliation Report

A spreadsheet anomaly is not automatically an error. It can be a valid exception, a timing difference, a duplicated transaction, a changed identifier, or a formula that silently stopped covering new rows. A reliable reconciliation process therefore does more than highlight unusual cells. It establishes what should agree, traces each difference to evidence, and records how the difference was resolved.

Scenario diagnosis

Use this method when two reports expected to describe the same activity do not agree, or when one workbook contains values that appear incomplete, duplicated, mistyped, or inconsistent. Begin by naming the reconciliation question in plain language: which records should match, on what key, for what period, under which accounting or operational rules?

Distinguish structural anomalies from business anomalies. Structural problems include shifted columns, mixed date formats, formula gaps, invisible spaces, and identifiers stored with inconsistent types. Business anomalies include an unexpected refund, an order posted in a different period, or a legitimate record missing from one system. This distinction determines who can resolve the item. A data steward may repair formatting; a finance or operations owner must approve a business treatment.

The final report should explain the source scope, matching rules, exception classes, evidence, disposition, and unresolved items. It should never claim that a value is fraudulent or incorrect merely because it is unusual.

Required inputs

Collect read-only exports of the source datasets, their extraction timestamps, column definitions, authoritative key fields, expected reporting period, currency and timezone rules, and known exclusions. Obtain any control total provided by the system owner. Gather a mapping table for identifiers that changed across systems and a policy for tolerances, reversals, voids, and late postings.

Create a data dictionary that states each column’s meaning and allowed form. Record which file is authoritative for each attribute. If one source owns transaction status and another owns settlement status, the reconciliation must not assume either file owns both. Define the report schema before analysis: exception identifier, source references, match status, difference type, observed evidence, proposed explanation, reviewer, disposition, and follow-up.

Data-safety preparation

Work on copies, not live operational files. Remove direct identifiers and sensitive free-text columns that are unnecessary for matching. Replace account or customer identifiers with consistent tokens when possible. Keep the token map separate and access-controlled. Confirm that the chosen AI or analysis environment is approved for the data classification.

Lock the original exports against editing and calculate or record a file checksum if your process supports it. Never paste entire confidential workbooks into a general-purpose chat tool. Prefer local formulas or scripts for deterministic comparisons and use AI to explain patterns, propose investigation steps, or draft documentation from sanitized exception samples. Disable macros from unknown files. Preserve formula text separately before converting sheets to values for analysis.

Sequential workflow

Start with source inventory. Record file names, sheet names, row scope, extraction times, and owners. Profile each table without altering it: headers, blank rates, distinct key counts, duplicate keys, data types, date bounds, and formula coverage. Review the profile for import problems before comparing values.

Normalize only in a derived working table. Trim whitespace, standardize case where keys are case-insensitive, parse dates under an explicit locale, and preserve leading zeros in identifiers. Keep both original and normalized fields. Document every transformation as a rule so another analyst can reproduce it.

Define matching in layers. Attempt an exact match on the approved primary key. For unmatched items, use only sanctioned composite keys, such as reference plus date and amount, with an explicit tolerance policy. Do not let a language model choose fuzzy matches as final truth. It may suggest candidates, but a reviewer must confirm them. Classify results as exact match, explained timing difference, valid business exception, source-data defect, possible duplicate, unmatched, or needs owner review.

Calculate differences using deterministic spreadsheet formulas or code. Retain source row references. For each exception class, inspect a sample and confirm the rule with the domain owner before applying it broadly. Then create a reconciliation table containing one row per exception, the relevant source values, the rule triggered, evidence, and disposition. Aggregate only after row-level lineage exists.

Draft an executive summary that states scope and limitations, not just totals. Route source defects to the system owner and policy questions to the accountable business owner. Correct data only in the authorized system, then re-export and rerun the complete comparison. Mark an item reconciled only when the new evidence supports its disposition.

Copyable prompt

```text Act as a data-quality analyst reviewing sanitized spreadsheet profiles and exception rows. Do not invent source values, matching rules, explanations, or corrections. An unusual value is not proof of error.

Reconciliation question: [question] Source definitions and owners: [definitions] Approved keys and normalization rules: [rules] Tolerance, period, timezone, and currency policy: [policy] Column profiles: [profiles] Sanitized exception sample with stable row references: [sample]

Return: - structural issues to resolve before matching; - exception patterns supported by supplied evidence; - alternative explanations for each pattern; - deterministic tests an analyst can run; - questions for source owners; - a reconciliation-report outline with scope, rules, lineage, disposition, and limitations.

Label every statement as observed, inferred, or unknown. Do not label a record erroneous, fraudulent, or reconciled without supplied confirmation. ```

Worked example

Imagine an order export and a settlement export use the same reference field. Several orders appear unmatched. Profiling reveals that the order references contain leading zeros while the settlement tool exported them as numbers. Other differences cluster around the reporting cutoff, and a small group repeats the same reference with distinct status values.

The analyst keeps the original references, creates a text-normalized comparison key approved by the owner, and reruns exact matching. The leading-zero group now links, but it is documented as a structural representation issue rather than silently removed. The cutoff group remains a timing-difference candidate until the finance owner confirms the posting rule. The repeated-reference group is not automatically deleted; the analyst checks whether it represents a reversal followed by a corrected settlement.

The report shows each group, its test, source rows, owner decision, and disposition. It avoids a seductive but unsafe shortcut: forcing every record to match. Some records may properly remain open because the evidence is incomplete.

Verification checks

Reconcile row counts and control totals before and after every transformation. Confirm that normalization did not merge distinct identifiers or drop leading characters. Test formula ranges at the first and last data rows. Check for hidden rows, filters, merged cells, error values, and hard-coded constants inside formula regions. Repeat date parsing with known examples from the source owner.

For every reconciled exception, require a source reference and approved rule or owner confirmation. Independently review fuzzy or composite matches. Compare the sum of matched, explained, and unresolved populations with the original population, while respecting whether records can appear in more than one diagnostic class. Re-run the process from untouched exports to prove reproducibility. A second reviewer should be able to select an exception and reconstruct its journey without asking the original analyst.

Failure recovery

If totals change during cleaning, stop and compare each transformation step to locate the loss or duplication. Restore from the read-only exports rather than repairing the working table by memory. If keys are not unique, identify the intended record grain with the source owner before matching. If date or decimal parsing is ambiguous, retain the raw text and obtain the correct locale rule.

If the AI suggests unsupported explanations, remove them from the report and restate the evidence boundary. If a broad rule resolves some examples but creates false matches elsewhere, withdraw it, return those items to unresolved status, and design a narrower deterministic test. When a source correction is unavailable, document the exception, risk, owner, and next review trigger. Never force a balancing entry merely to make the report look complete.

Reusable final procedure

Freeze and inventory source exports, profile structure, define record grain and authoritative fields, create documented normalized copies, and apply approved matching rules from strict to conditional. Preserve row-level lineage for every exception. Separate observed differences from proposed explanations, obtain owner approval for business treatment, correct data only in its authorized source, and rerun from clean exports. Publish the scope, rules, resolved items, open items, limitations, and review ownership together. Retain the transformation log so the next reconciliation uses the same controlled method instead of an improvised set of filters.