Comparing two versions of a spreadsheet without drowning in false changes.

Files get re-sorted between versions, and a comparison that works by position reports every row below the first moved one as changed. A report flagging 198 rows out of 200 has told you nothing while looking thorough.

Match on a key instead: an order id, an invoice number, a line reference. Where none exists, hash the contents of each row and match on that, which handles re-sorting correctly and has one honest weakness — two genuinely identical rows collapse into one, so an added duplicate reads as no change.

Whichever you use, write it down next to the result. A comparison run on the wrong key is plausible and wrong, and the reader is the only one who can catch it.

Rename a column and a naive comparison reports one column removed and one added. Every value change inside that column vanishes from the report — and those are usually the changes somebody made deliberately.

Detect renames by comparing what is in the columns, not what they are called. Two columns whose values mostly overlap are the same column, and where the values also changed, position helps: a rename in a spreadsheet almost always happens in place.

The most useful thing a comparison can tell you is that a total which barely moved is hiding two large movements that cancelled out. A net figure alone reports that as nothing happening.

So compute the increases and the decreases separately, not just the net. A column that moved by a net two hundred, made of forty-eight thousand up and forty-seven thousand eight hundred down, is a completely different fact from a quiet two hundred.

Compare only the rows present in both versions when you do this. Mixing in added and removed rows blends value changes with new records, which is how a comparison reports a rise that is entirely one new line.