How to compare two Excel sheets with AI: missing rows and changed values

Start with the question, not the upload

Two spreadsheets can look different without describing different business events. Someone sorts by customer, inserts a heading, changes a number format or moves a column. A comparison based on row position can report dozens of changes even though the orders themselves are unchanged. The reverse problem is just as serious: two grand totals can match while individual records have changed.

Choose the question before choosing a tool. Are you comparing the visible layout of two workbook versions, the formulas inside them, or the records represented by their tables? This guide focuses on records: which order lines were added, removed or edited between exports. A record comparison does not establish that workbook formulas, macros, charts or formatting are unchanged.

The downloadable example uses fictional orders created for this article. The expected change ledger comes from direct comparison of the authored records, not a benchmark of any AI product. No claim is made that a named assistant found every difference. You can use the files to evaluate a tool yourself and keep its actual output alongside the expected results.

Work on copies. Preserve the original filenames, export times and source system where available. Calling a file final-new-v3 does not prove it is the later export. If the order of the two versions is uncertain, resolve that first; otherwise added and removed will mean the opposite of what the reader expects.

Define one row and a stable matching key

In the sample, one row represents an order line, identified by OrderID plus LineID. OrderID alone is not sufficient: order 00102 has two separate lines. Matching those lines on the order number would create ambiguous pairings or overwrite one with the other. Product name is not a substitute because a customer can order the same product more than once.

Write the matching rule in plain language: compare the exact OrderID and LineID strings together; preserve leading zeros; do not match by row number. This is the data contract for the exercise. In a real export, confirm that the source system actually guarantees the proposed key. An assistant can count duplicates but cannot create that business guarantee.

Keep the original columns even if you make helper columns. If whitespace cleanup is permitted, apply the same documented rule to both versions and retain the source values. Do not silently remove punctuation or change case in identifiers. Two identifiers that look similar may be intentionally different.

If there is no stable key, the task changes. You may produce candidate matches using several fields, but label them as candidates and require review. A similarity score does not prove that two rows describe the same event. Ask the system owner for an export with proper identifiers before automating a high-consequence comparison.

Before comparing these tables, profile each one separately. One row should represent an order line. The proposed key is the exact pair OrderID + LineID. Preserve both as text. Report row counts, blank key fields, duplicate key pairs and column names. Do not delete duplicates, guess missing IDs or match by row position. Tell me whether the proposed one-to-one comparison is possible and identify exceptions first.

Understand the practice data before asking for differences

The older file contains six rows and six distinct order-line keys. The newer file contains seven rows but only six distinct keys, because 00106 / 1 appears twice with conflicting quantities. That conflict is deliberate. It is a useful way to see whether a comparison stops at an ambiguous key or quietly chooses the first row it sees.

For the unambiguous shared keys, 00101 / 1 has its quantity changed from 10 to 12. The second line of 00102 changes status from Pending to Shipped. Its first line stays unchanged, as does 00104 / 1. Order line 00103 / 1 is present only in the older export, while 00105 / 1 is present only in the newer export.

Treat those as observations about these snapshots. A row missing from the later file does not establish cancellation. The export might have a different filter, a record might have moved to an archive, or the source might be incomplete. The correct output is older only, with any business interpretation added separately after investigation.

The duplicate key is a separate category of work, not an excuse to force the tables into the same shape. Keep both 00106 / 1 rows visible in an exception report. Do not include either as a uniquely resolved new order line until the source owner clarifies whether one is wrong or the key definition is incomplete.

Check imports, blanks and numeric precision

The practice workbook stores order and line identifiers as text and quantities and unit prices as numbers. The CSV versions cannot carry Excel cell-type settings. If you open a CSV directly and your application converts 00101 into 101, the comparison has already changed the input before any assistant starts reasoning about it.

Import identifier columns as text and inspect several values after loading. Keep dates in an unambiguous representation agreed with the source owner. A slash date that can mean either March 4 or April 3 should remain unresolved until the source convention is known. Changing a display format cannot recover information lost during conversion.

Decide what counts as an equal numeric value. For the sample, quantities are exact integers and prices have two decimal places. In a real calculation, floating-point results might need a documented tolerance. Do not let a model choose a convenient rounding rule after it sees a difference. The tolerance should follow the business meaning of the field.

Distinguish an empty value from zero and distinguish the absence of a matched record from an empty field on a matched record. An order line with no discount recorded is not automatically a zero-discount line. Use separate indicators for key presence and field content so missing records cannot disappear into ordinary blank cells.

Related guide: Clean a CSV without changing its meaning

Request a field-level change ledger

A paragraph saying several orders changed is difficult to review. Ask for one row per changed field, with the key, field name, old value, new value and source location. Put records found on only one side in their own categories. Put duplicate-key exceptions somewhere a reviewer cannot overlook them.

Specify the compared fields: Product, Quantity, UnitPrice and Status in this exercise. Ignore row order. If a column exists on only one side, report that schema difference before interpreting missing cells as record edits. A newly introduced column is not evidence that every record has changed in the same sense as a corrected quantity.

Ask the assistant to describe its method before presenting the result. If it can execute code, request the script and an output file rather than an unexplained chat table. If it can only read a pasted sample, the result covers that sample. A confident explanation does not establish access to every worksheet or row in a workbook.

Keep the comparison ledger separate from the operational dataset. The article's goal is to explain differences, not to overwrite the later export or push corrections into a live system. A reviewer should be able to accept or reject each interpretation without losing either original version.

Compare the two order-line tables using exact OrderID + LineID keys. First quarantine every key duplicated in either input. For the remaining keys, use all keys from both versions and report unchanged, changed, older-only and newer-only records. Compare Product, Quantity, UnitPrice and Status. For changes, show key, field, old value and new value. Preserve blanks and identifier strings. Do not infer cancellation, fulfillment or correctness from presence alone. Return a change ledger, an exceptions table, counts and the method or code used.

Use an independent comparison to challenge the result

Microsoft documents a full outer join in Power Query as a way to retain rows from both input tables. That is a useful basis for record comparison because a left-only or inner join can hide records found only in the other file. The join is only the matching stage; you still need field comparisons and explicit categories afterward.

Load copies of the two tables into Power Query, preserve the key types, and inspect duplicate-key exceptions first. Merge the unambiguous sets using both key columns in the same order. Choose Full outer, expand the right-side fields with clear prefixes, and use separate presence indicators to distinguish unmatched records from blank business fields.

Do not run a many-to-many join over unresolved keys and assume the resulting row count represents the business. Multiple occurrences on both sides can multiply rows. A join that completes without an error can still be the wrong operation for the data's grain. Resolve or quarantine the ambiguity before interpreting a total.

For a workbook-level check, Microsoft's Spreadsheet Compare can inspect changes to supported Excel workbooks, including values and formulas. Its availability depends on the Windows edition and subscription. That tool answers a different question from key-based record matching; use it when you also need to inspect workbook structure rather than assuming the record ledger covers everything.

Sources: Microsoft: full outer joins in Power Query; Microsoft: comparing workbook versions

Reconcile counts before trusting the conclusion

The older input has six rows. Four keys are shared unambiguously, one is older only, and one key needs exception handling because it is duplicated in the newer input. Those six keys account for the older side. The newer input has seven rows: four shared unambiguous rows, one unique newer-only row, and two rows belonging to the unresolved key.

Notice the distinction between rows and keys. Reporting one unresolved key is correct, but reporting one unresolved newer row is not. Both conflicting source rows must remain visible. A compact summary that mixes those units can appear to reconcile while hiding a record.

For the practice data, the four shared unambiguous keys split into two unchanged records and two changed records. There are two changed fields in total, but that equivalence is accidental. One changed record could have several changed fields in a different file. Label each count so a reader knows what it measures.

Only reconcile financial totals after defining the included records and the meaning of the amounts. Summing Quantity across unrelated products may be mathematically possible but operationally unhelpful. A money total that includes unresolved duplicate order lines is not a reliable revenue control. Keep unresolved amounts separate instead of treating a clean-looking total as a complete result.

Review the differences with the person who owns the data

Open every exception and a representative set of each resolved category. For a small teaching file, inspect all rows. For a large operational export, design the review around risk: identifier changes, amount changes, missing records, sensitive status changes and unusual concentrations of edits deserve particular attention.

Ask why a difference exists only after establishing that it exists. The quantity increase might be a correction, an additional request or a bad edit. The move to Shipped might be valid or premature. The comparison should preserve these possibilities rather than turn the later file into the automatic source of truth.

Keep a decision log with the reviewer, the source consulted and the approved follow-up. If a duplicate is resolved by a revised export, rerun the comparison from that export. Do not manually patch an output table and forget that the underlying input still contains the conflict.

Share an evidence package: original snapshots, matching rule, comparison method, change ledger, exceptions and review decisions. Someone who did not participate in the AI conversation should still be able to understand the result. A screenshot of a successful prompt is not a substitute for that package.

Fix common comparison failures

If almost every row appears changed, first check sorting, column mapping and types. A row-by-row comparison of differently sorted exports is a common cause. Another is comparing a text quantity with a numeric quantity without deciding whether that representation difference matters for this task.

If the result contains more matched records than either input, inspect duplicate keys and join cardinality. Do not ask the assistant to remove the extra rows until you know why they were created. Removing duplicates after a mistaken merge can conceal a lost distinction and still leave incorrect pairings.

If the tool reports no differences, confirm which sheets and ranges it actually read. The workbook may contain hidden sheets, tables outside the initial preview or formulas whose displayed results were compared without inspecting the formulas themselves. State the coverage rather than claiming the entire workbook is identical.

If the input is too large for the available tool, split work by a stable business partition and reconcile coverage across partitions. Do not split arbitrarily through the middle of a key group. Record excluded sheets, unreadable files and failed batches; a partial run should remain visibly partial instead of turning into an all-clear summary.

Keep record changes separate from formula changes

A table exported from a workbook usually contains values rather than the logic that produced them. Two exports can therefore match even though one workbook has a broken formula that has not recalculated yet. Conversely, formulas can differ while currently producing the same displayed result. Choose the evidence that matches the question.

If the task includes formula review, preserve the original workbooks and inspect formula expressions, calculation settings and referenced ranges through a suitable workbook comparison. Do not claim that a CSV-based order comparison has checked them. Keep the formula review as a separate deliverable with its own coverage and exceptions.

For this practice pack, both worksheets are authored source tables with no calculation formulas. The expected change ledger concerns their records only. That limited scope makes the exercise easier to reproduce and prevents a successful record match from being presented as a complete workbook audit.

Questions to settle before using this on live files

Can you compare two sheets without a unique ID? You can propose matches using other fields, but that is an entity-matching exercise with unresolved cases, not a guaranteed one-to-one comparison. Keep uncertainty visible and obtain better identifiers where possible.

Should AI choose which version is correct? Only when you have supplied a valid rule and the evidence needed to apply it. A newer timestamp does not prove a corrected record. The data owner should approve consequential changes through the normal operating process.

Does the downloadable workbook perform the comparison automatically? No. It supplies two typed source sheets for practice. The separate expected ledger is an authored answer key. This separation lets you inspect what your chosen method actually returns instead of confusing a prefilled example with tool performance.

Start with these fictional files, keep the answer key aside, and run your comparison. Then explain every difference between the output and the expected ledger. The useful outcome is a repeatable procedure that preserves ambiguity and evidence, not simply a table that looks convincing.