Excel · ChatGPT
Compare Two Excel Sheets and Find Differences
Compare matching cell positions, reconcile records even when row order changes, or audit the formulas behind identical results. Each tested method writes to a separate comparison sheet and preserves both source worksheets.
Choose your sheet comparison — copy the prompt:
I have an Excel workbook with worksheets January and February. Both use A1:C4. January: A1 Product, B1 Units, C1 Revenue; A2 Alpha, B2 10, C2 100; A3 Beta, B3 [truly blank], C3 200; A4 Gamma, B4 0, C4 [truly blank]. February: A1 Product, B1 Units, C1 Revenue; A2 Alpha, B2 10, C2 110; A3 Beta, B3 0, C3 200; A4 Delta, B4 0, C4 [truly blank]. Create a third sheet named Cell Comparison. In corresponding A2:C4 cells return Same when both source cells contain the same nonblank value, Different when values differ (including truly blank versus numeric 0), and Both blank when both are truly empty. Give one formula for Cell Comparison!A2 to fill across C and down through row 4. Explain why blanks must be tested explicitly instead of relying only on January!A2=February!A2. Expected A2:C4: row 2 Same, Same, Different; row 3 Same, Different, Same; row 4 Different, Same, Both blank. The blank cells are genuinely empty, not formulas returning "". Ordinary equality is case-insensitive and this is not a type-sensitive text audit. Keep the comparison non-destructive. Use ordinary desktop Excel formulas compatible with current Windows and Mac; do not modify either source sheet.
Use when both sheets have exactly the same layout and cell positions.
I have worksheets Prior Export and Current Export with headers Product ID, Product, Quantity, Unit Price in A1:D1. Prior Export rows: P100, Alpha, 5, 10; P200, Beta, 2, 20; P300, Gamma, 7, 15. Current Export rows in a different order: P300, Gamma, 7, 15; P400, Delta, 1, 50; P100, Alpha, 6, 10. Create Reconciliation with columns Product ID, Status, Change detail. Compare by Product ID, never row position. Preserve Prior Export ID order, then append current-only IDs in Current Export order. Rules: Unchanged when Product, Quantity, and Unit Price match; Changed when any differs; Missing from current for prior-only IDs; New in current for current-only IDs. Expected output: P100 | Changed | Quantity: 5 → 6; P200 | Missing from current | [blank]; P300 | Unchanged | [blank]; P400 | New in current | [blank]. Give safe copyable Microsoft 365 formulas for A2, B2, and C2. Determine record presence only from Product ID. Assume both exports contain at least one row; Product IDs are unique/nonblank; Product, Quantity, and Unit Price are all nonblank in this validated test. The formulas need not distinguish blank fields from numeric 0 outside these assumptions. Ordinary text comparison is case-insensitive. State that duplicate Product IDs require occurrence-level reconciliation and must not be silently collapsed. Mention that FILTER, VSTACK, UNIQUE, and dynamic arrays require Microsoft 365; older Excel needs a helper-column or Power Query alternative. Do not sort/modify either source, use fuzzy matching, or reduce this to a two-column membership check.
Use when records share unique IDs but appear in different row orders.
I have worksheets Model - Approved and Model - Review. Their displayed results match, but I need to audit the underlying formulas. Both sheets have inputs B2=100, C2=60, B3=4, C3=5, B4=7, B5=3. D2: Approved =B2-C2; Review =SUM(B2,-C2); both display 40. D3: Approved =B3*C3; Review =PRODUCT(B3,C3); both display 20. D4: both use =IF(B4="","",B4*2); both display 14. D5: Approved =ROUND(B5,2); Review =B5; both display 3. Create Formula Audit with columns Cell, Approved formula, Review formula, Formula status. Compare D2:D5 using FORMULATEXT and exact formula-string comparison. Return Same formula only when strings are identical and Different formula otherwise. Expected: D2 Different formula; D3 Different formula; D4 Same formula; D5 Different formula. Show the actual formulas in the audit. Explain why comparing displayed results misses D2, D3, and D5 and why mathematically equivalent but textually different formulas remain flagged. All tested cells are formulas. For a reusable range containing constants/blanks, explain how to use ISFORMULA and label Not a formula instead of allowing FORMULATEXT #N/A. Keep both model sheets unchanged. Mention FORMULATEXT requires Excel 2013 or later on Windows or Mac.
Use when identical current values may hide different formula logic.
Before — source cells differ silently
| Cell | January | February |
|---|---|---|
| A2 | Alpha | Alpha |
| B2 | 10 | 10 |
| C2 | 100 | 110 |
| A3 | Beta | Beta |
| B3 | [blank] | 0 |
| C3 | 200 | 200 |
| A4 | Gamma | Delta |
| B4 | 0 | 0 |
| C4 | [blank] | [blank] |
→
After — Cell Comparison!A2:C4
| Row | A | B | C |
|---|---|---|---|
| 2 | Same | Same | Different |
| 3 | Same | Different | Same |
| 4 | Different | Same | Both blank |
=IF(AND(ISBLANK(January!A2),ISBLANK(February!A2)),"Both blank",IF(OR(ISBLANK(January!A2),ISBLANK(February!A2)),"Different",IF(January!A2=February!A2,"Same","Different")))Before — row order hides record changes
| Sheet | ID | Product | Qty | Price |
|---|---|---|---|---|
| Prior | P100 | Alpha | 5 | 10 |
| Prior | P200 | Beta | 2 | 20 |
| Prior | P300 | Gamma | 7 | 15 |
| Current | P300 | Gamma | 7 | 15 |
| Current | P400 | Delta | 1 | 50 |
| Current | P100 | Alpha | 6 | 10 |
Comparing row 2 to row 2 would incorrectly compare P100 with P300.
→
After — reconciliation by Product ID
| Product ID | Status | Change detail |
|---|---|---|
| P100 | Changed | Quantity: 5 → 6 |
| P200 | Missing from current | [blank] |
| P300 | Unchanged | [blank] |
| P400 | New in current | [blank] |
A2: =LET(p,FILTER('Prior Export'!A2:A1000,'Prior Export'!A2:A1000<>""),c,FILTER('Current Export'!A2:A1000,'Current Export'!A2:A1000<>""),UNIQUE(VSTACK(p,c)))B2: =LET(id,$A2,p,IFNA(XMATCH(id,'Prior Export'!$A$2:$A$1000,0),0),c,IFNA(XMATCH(id,'Current Export'!$A$2:$A$1000,0),0),IF(p=0,"New in current",IF(c=0,"Missing from current",IF(SUM(--(INDEX('Prior Export'!$B$2:$D$1000,p,0)<>INDEX('Current Export'!$B$2:$D$1000,c,0)))=0,"Unchanged","Changed"))))C2: =LET(id,$A2,p,IFNA(XMATCH(id,'Prior Export'!$A$2:$A$1000,0),0),c,IFNA(XMATCH(id,'Current Export'!$A$2:$A$1000,0),0),IF(OR(p=0,c=0),"",LET(o,INDEX('Prior Export'!$B$2:$D$1000,p,0),n,INDEX('Current Export'!$B$2:$D$1000,c,0),h,'Prior Export'!$B$1:$D$1,TEXTJOIN(", ",TRUE,IF(o<>n,h&": "&o&" → "&n,"")))))Before — values match while formulas differ
| Cell | Approved formula | Review formula | Both results |
|---|---|---|---|
| D2 | =B2-C2 | =SUM(B2,-C2) | 40 |
| D3 | =B3*C3 | =PRODUCT(B3,C3) | 20 |
| D4 | =IF(B4="","",B4*2) | =IF(B4="","",B4*2) | 14 |
| D5 | =ROUND(B5,2) | =B5 | 3 |
→
After — Formula Audit compares exact text
| Cell | Approved formula | Review formula | Status |
|---|---|---|---|
| D2 | =B2-C2 | =SUM(B2,-C2) | Different formula |
| D3 | =B3*C3 | =PRODUCT(B3,C3) | Different formula |
| D4 | =IF(B4="","",B4*2) | =IF(B4="","",B4*2) | Same formula |
| D5 | =ROUND(B5,2) | =B5 | Different formula |
B2: =FORMULATEXT('Model - Approved'!D2)C2: =FORMULATEXT('Model - Review'!D2)D2: =IF(EXACT(B2,C2),"Same formula","Different formula")Choose the comparison based on sheet structure
Identical layouts
Compare each address with the same address on the other sheet
Cell by cell
Rows reordered
Find records by a unique ID, then compare their fields
Key based
Results match
Audit FORMULATEXT to reveal different calculation logic
Formula text
How to compare two Excel sheets safely
A practical five-step audit
1
Confirm the sheet structure
Use direct cell comparison only when both sheets share the same layout. If row order differs, identify a unique key first.
2
Define blank behavior
Decide whether truly empty cells, formula-generated empty strings, and numeric zero should be distinct.
3
Write to a third sheet
Keep comparison formulas and results separate so neither source worksheet is overwritten.
4
Compare the right layer
Compare values for data changes, record fields for exports, or formula text when calculation logic itself matters.
5
Verify known differences
Check expected changed, missing, new, and unchanged examples before relying on the full audit.
Do not assume row position identifies a record. Reordered exports require a unique key. Duplicate keys need a separately designed occurrence-level reconciliation.
Frequently asked questions
How do I compare two Excel sheets for differences?
Use a third worksheet for a non-destructive comparison. Compare corresponding cells when both sheets share a layout, or compare records by a unique key when row order differs.
Can I compare two Excel sheets when rows are in a different order?
Yes. Use a unique record key such as Product ID, then retrieve and compare fields by that key. Do not compare row 2 with row 2 unless both sheets are guaranteed to use the same order.
How do I compare formulas instead of values in Excel?
Use FORMULATEXT to return each cell's formula string and EXACT to compare those strings. This can reveal different formula logic even when the current calculated values match.
Why must blank cells be checked explicitly when comparing sheets?
A simple equality test can treat a truly blank cell and numeric zero as equal in some comparisons. Use ISBLANK checks when that distinction matters, and define separately how formula-generated empty strings should be treated.
Will comparing two sheets change the source data?
Not when the comparison formulas are placed on a separate worksheet. Keep both source sheets unchanged and verify the audit output before taking any follow-up action.