# Compare two Excel sheets and flag every difference

Matches records between two spreadsheets or lists, flags missing, extra, and changed rows, and gives formulas to reconcile them.

- Topic: Data & Analysis
- Framework: Few-Shot
- Level: Advanced
- Tags: Data & Analytics, Analyze, Fill-in Template
- Source: https://www.thinkwithprompt.com/prompts/compare-two-excel-sheets-and-flag-every-difference

## Prompt

```text
Help me compare two spreadsheets (or two versions of a list) and find every difference.

**Sheet A - what it is and where it comes from:** [e.g. "CRM export, 3 Oct"]
**Sheet B - what it is and where it comes from:** [e.g. "finance system export, 3 Oct"]
**Columns in each:** [PASTE headers and 10-20 sample rows from each]
**Column(s) that identify the same record in both:** [e.g. "Invoice ID", "Employee number" - or "not sure"]
**Columns whose values should match:** [e.g. "amount, status, owner"]
**My tool:** [Excel 365 / older Excel / Google Sheets]

Steps:
1. Confirm a reliable matching key. If IDs are formatted differently (leading zeros, text vs. number, spaces), give me a cleaned key formula for both sheets first.
2. Classify every record: in both and identical / in both but values differ / only in A / only in B / duplicated key.
3. For amounts, suggest a tolerance (e.g. differences under $0.01 from rounding) so noise does not hide real gaps.

Return:
1. **Helper column formulas** for each classification (XLOOKUP or COUNTIF to test presence, IF to compare each field), ready to paste.
2. **A summary layout:** counts and totals by classification, so I can see that A total = matched + only-in-A, and the same for B.
3. **An exceptions table format:** Key | Field | Value in A | Value in B | Difference | Likely reason | Action.
4. **Faster options** for large files: Power Query merge (anti-join) or Spreadsheet Compare, with steps.
5. Likely causes to check for the differences found (timing cut-off, status changes, manual edits).

Use only the values I provide. Do not assume which sheet is correct; mark that as my call.
```

## What to fill in

- e.g. "CRM export, 3 Oct"
- e.g. "finance system export, 3 Oct"
- PASTE headers and 10-20 sample rows from each
- e.g. "Invoice ID", "Employee number" - or "not sure"
- e.g. "amount, status, owner"
- Excel 365 / older Excel / Google Sheets

Free to copy and use. From ThinkWithPrompt (https://www.thinkwithprompt.com/), a library of 2,663 curated AI prompts.