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.
The prompt
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.
Highlighted [brackets] are blanks to replace with your own details.
Fill in the blanks and use this promptMore Data & Analysis prompts
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
How this prompt is built
This prompt follows the Few-Shot structure (Few-shot prompting). A few-shot prompt includes one or more worked examples of the input and the output you want, so the model can copy the pattern instead of guessing it.
Related prompts
- Find what my dashboard is not telling meReviews a dashboard for blind spots, missing comparisons, vanity metrics, and misleading views, and suggests what to add.
- Build a dynamic Excel dashboard from my raw dataPlans a refreshable one-page Excel dashboard for management, with tab layout, KPIs, pivot charts, slicers, and the formulas behind them.
- Audit an Excel workbook for broken, inconsistent, and hard-coded formulasChecks your spreadsheet for formula errors, broken references, inconsistent formulas across rows, and hard-coded numbers that should be formulas.
- Explain my Excel model to a non-technical executiveTurns a complex spreadsheet or model into a plain-English walkthrough an executive can follow, trust, and question.