When two spreadsheets contain many of the same records, comparing them row by row can be misleading. Rows may be reordered, while an important value changes, a record disappears, or a new record is added. A stable identifier lets you compare the records rather than their positions.
This guide works through a careful method for Excel and CSV exports. It also explains when a formula or Power Query is enough and when a separate review tool may save setup.
1. Keep the two versions separate
Save an unchanged copy of each export and label them by time or purpose: for example, Before and After. Check that both files cover the same reporting period, filters, locations, and status rules. If one export omits inactive records and the other includes them, a missing row may reflect a filter change rather than a deleted record.
Do not overwrite the earlier export. You need both versions to review what changed and to investigate a result later.
2. Choose a key that identifies one record
Use a stable ID such as an account number, invoice number, or product SKU. A person’s name or product description may change and may not be unique. If no single column uniquely identifies a record, a combination of columns can serve as a composite key.
Before comparing, check for blank and duplicate keys. A blank key cannot reliably identify a record. A duplicate key may refer to multiple records, so a simple lookup can select the wrong one. Normalize obvious formatting differences too: leading zeroes, spaces, and text-versus-number types can prevent values that look alike from matching.
3. Find records that exist on only one side
For a small, well-formed Excel table, a helper column can test whether each Before ID appears in the After table. With tables named BeforeTable and AfterTable, and an ID column in each, this formula marks IDs with no match:
=IF(COUNTIF(AfterTable[ID],[@ID])=0,"Missing from After","Present")Run the same check in the opposite direction to find records that are new in After. The formula assumes the selected ID is stable and unique; it does not resolve duplicates or explain why a row is absent. Formula names and separators can vary with spreadsheet language settings.
If your Excel edition includes Power Query, a full outer join brings in matching and one-sided rows from both tables. Microsoft documents the join types and what they return in its Power Query merge guide. The exact menu availability can depend on your Excel version and platform.
4. Compare values for records with a matching key
Once records are matched, compare the fields that matter to the decision: quantity, status, price, owner, or date. A simple lookup can return the After value beside the Before value; a comparison column can then mark differences. Check key uniqueness before relying on a lookup, because duplicate keys can make a first-match result look complete when it is not.
A clean review has four useful outcomes:
- Unchanged: key exists on both sides and reviewed values match.
- Changed: key exists on both sides and one or more reviewed values differ.
- Missing: key is in Before but not After.
- New: key is in After but not Before.
Small fictional example
| ID | Before status | After status | Review |
|---|---|---|---|
| A-102 | Active | Active | Unchanged |
| A-207 | Review | Active | Changed |
| A-318 | Open | — | Missing from After |
| A-450 | — | Open | New in After |
These rows are illustrative only. They are not customer data, usage data, or a measured product result.
When a manual method is enough
For a short list with consistent columns and a one-time comparison, formulas, filters, or a side-by-side review may be all you need. Power Query can help when the process repeats and you want to preserve the join steps. Microsoft also documents Spreadsheet Compare through Inquire for supported Excel on Windows; availability depends on the Excel edition and platform. Check Microsoft’s workbook comparison guidance.
A dedicated comparison tool is more useful when you repeat the same file-pair review, need the output grouped by identifier, or want a separate review report without building helper columns each time. It still depends on good keys and a human decision about what to do with the results.
Where Change Check fits
Change Check is a Mac desktop app for comparing CSV and XLSX files by a selected identifier. You can review Changed, Missing, and New records, inspect identifier exceptions, and export a separate report. The current release supports up to 10,000 data rows per selected sheet; it does not approve an import or connect to an inventory or accounting system. Your original files remain unchanged.
See Change Check for the supported files, current requirements, and product details.
For stock files, see the inventory export example for a SKU-focused walkthrough.
Frequently asked questions
Can I compare two spreadsheets when the rows are in a different order?
Yes, if you match records with a stable key rather than comparing row positions. Confirm that the key is unique and formatted consistently in both files.
How do I find new and missing rows?
Check each side’s key values against the other side. An ID in Before but not After is missing from the newer file; an ID in After but not Before is new. Confirm the same filters and reporting periods before acting.
Can I compare an Excel file with a CSV?
Some tools support mixed formats. Change Check’s current product page lists CSV and XLSX support, including a CSV file compared with an XLSX workbook. Check the selected sheet and identifier before running a review.
Does a changed row mean the source system is wrong?
No. It means the reviewed values differ between the two files. A change can be expected, caused by different export filters, or require follow-up. The comparison does not decide which file is correct.