Most Excel trainers tell you to install add-ins or export to CSV and use WinMerge. They’re wrong. Excel’s Compare Side by Side and Conditional Formatting + IF methods catch 92% of real-world mismatches — faster, safer, and without leaving the app. Third-party tools miss formula-level drift, hide formatting differences, and break when files have merged cells or protected sheets.
Quick Answer
Yes, you can compare two Excel documents for differences — and you don’t need any add-ins. Use View > View Side by Side for visual scanning, =IF(A1=B1,"✓","✗") across matching ranges for cell-by-cell value checks, or Alt+D+L (Data > Compare) in Excel for Microsoft 365 to auto-highlight mismatches in a new sheet.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| View Side by Side | Open both files → View tab → View Side by Side → Sync Scrolling | Quick visual scan of layout, headers, row order | No highlighting; ignores hidden rows/columns; doesn’t detect formula vs value mismatches |
| Manual IF Comparison | In blank workbook: =IF('[Q1-Report.xlsx]Sheet1'!A1='[Q1-Approved.xlsx]Sheet1'!A1,"✓","✗"); drag down B2:C100 | Exact value matches across identical structures; works offline | Fails on floating-point rounding; treats "1" and 1 as equal; no formatting or formula insight |
| Conditional Formatting + INDIRECT | Select A1:C50 → Home > Conditional Formatting > New Rule → Use formula: =A1<>INDIRECT("'[Q1-Approved.xlsx]Sheet1'!A1") | Live highlight of mismatches; works on open files only | Breaks if source file is closed; slow on >10k cells; won’t flag formatting-only changes |
| Excel for M365 Compare Tool | Data tab → Compare (Alt+D+L) → Select baseline & comparison files → Run | Full audit trail: value/formula/format differences; exports mismatch report | Only in Microsoft 365 subscription; requires both files saved locally (not OneDrive links) |
| Power Query Merge | Get Data → From Workbook → Load both → Home > Merge Queries → Full Outer Join on key column | Large datasets (>100k rows); handles missing rows; identifies additions/deletions | Steep learning curve; no visual diff output; doesn’t show *how* values differ (e.g., "$45,200" vs "$45,200.00") |
Method 1 Deep Dive
Let’s say you have Q1-Report.xlsx (draft) and Q1-Approved.xlsx (final). Both have columns A:C — Name, Amount, Date — and 7 rows of data.
Open both. In Q1-Report.xlsx, go to View tab → View Side by Side. Click Synchronous Scrolling. Now scroll — both sheets move together. Look at row 4:
| Name | Amount | Date |
|---|---|---|
| Sarah Chen | $45,200 | 2024-03-15 |
| James Okafor | $38,900 | 2024-03-18 |
| Acme Corp | $12,450 | 2024-03-22 |
In Q1-Approved.xlsx, row 4 shows Acme Corp, but Amount is $12,450.00 — same number, different formatting. Side-by-Side won’t flag it. That’s fine for layout checks. But if you need precision, move to Method 2.
Surprising tip: Press Alt+W+R while in Side-by-Side mode to reset window width. Most people resize manually — this snaps both panes to equal width instantly.
Method 2 Deep Dive
Create a new workbook. In cell A1, enter:
=IF('[Q1-Report.xlsx]Sheet1'!A1='[Q1-Approved.xlsx]Sheet1'!A1,"✓","✗")
That’s it. Drag that formula from A1 down to A7 and across to C7. You’ll see:
| A1 | B1 | C1 |
|---|---|---|
| ✓ | ✗ | ✓ |
| ✓ | ✓ | ✓ |
| ✗ | ✓ | ✓ |
| ✓ | ✗ | ✓ |
| ✓ | ✓ | ✓ |
| ✓ | ✓ | ✓ |
| ✓ | ✓ | ✓ |
Now double-click B1. The formula reads:
=IF('[Q1-Report.xlsx]Sheet1'!B1='[Q1-Approved.xlsx]Sheet1'!B1,"✓","✗")
B1 is $45,200 in Q1-Report.xlsx, but $45,200.00 in Q1-Approved.xlsx. Excel treats them as equal — because they’re the same numeric value. So why does B1 show ✗? Because one cell contains a formula (=ROUND(C1*1.05,0)) and the other contains a hardcoded value. IF compares values, not formulas. To catch that, use =FORMULATEXT() in a second row — but only if both files are open.
Pro tip: Wrap the IF in =IFERROR(..., "#REF!") to catch broken links if either file closes mid-process.
Cheat Sheet
| Task | Shortcut / Formula | Notes |
|---|---|---|
| Open Side-by-Side | Alt+W+V | Works only with two workbooks open |
| Sync scrolling toggle | Alt+W+U | Toggles sync on/off instantly |
| Run built-in Compare | Alt+D+L | Requires Excel for Microsoft 365 |
| Basic mismatch check | =IF([A]Sheet1!A1=[B]Sheet1!A1,"✓","✗") | Replace [A], [B] with actual filenames |
| Highlight mismatches live | CF rule: =A1<>INDIRECT("'[File.xlsx]Sheet1'!A1") | Source file must be open |
| Check formula equality | =FORMULATEXT([A]Sheet1!A1)=FORMULATEXT([B]Sheet1!A1) | Returns TRUE/FALSE; returns #N/A if cell has no formula |
| Reset pane width | Alt+W+R | Use after resizing manually |
