How to Compare Two Excel Files for Differences

Three ways to compare two Excel files — side-by-side viewing, Conditional Formatting, and VLOOKUP formulas. Each has a real use case. Here's how to decide which one you actually need, and why the first option is usually the right one.

Before you start: what's your comparison type?

Not all Excel file comparisons are the same. Choosing the right method depends on what you're actually looking for:

• Visual scan — You want to see both files at the same time and spot discrepancies by eye. Fast, intuitive, works for any file structure.
• Cell-level diff — You need to highlight every cell that differs between two sheets. Good for structured, identically-stacked data.
• Row matching — You have a key column (like an ID or product code) and want to find which rows exist in one file but not the other, or where a value changed. This is a VLOOKUP job.

Most real-world comparison work is actually the first type — visual scan. You want both files visible at the same time, scrolling in sync, so you can spot anomalies naturally. That's the method that scales best and requires no formula setup.

Method 1: Side-by-side viewing (for visual scans)

The simplest way to compare two Excel files is to open both and look at them. This sounds obvious, but it's genuinely the most effective approach for most comparison tasks — especially when the files don't have matching column structures or when you're looking for visual anomalies rather than formula-level differences.

Excel's built-in side-by-side tool is View → New Window, which opens a second view of the same workbook. But if you're comparing two different files, the cleanest way is to open both files and use View → Arrange All → Vertical to tile them on screen:

1. 1
   Open both Excel files Open the two workbooks you want to compare. They can be the same file (different tabs) or two completely different files.
2. 2
   Arrange side by side: View → Arrange All → Vertical Go to the View tab, click Arrange All, select Vertical. Both files appear on screen at the same time, each taking roughly half the width of your monitor.
3. 3
   Enable Synchronous Scrolling to keep rows aligned In either window, go to View → Synchronous Scrolling to turn it on. Now when you scroll either file, both scroll together — row 50 on the left lines up with row 50 on the right.
4. 4
   Navigate to find differences With both sheets visible and synchronized, you can spot visual differences immediately — a missing row, a different total, a cell that's been edited. No formulas required.

This works for any comparison — same-file different tabs, two versions of the same report, or two completely unrelated spreadsheets. It's the most flexible option because it requires no formula setup and works on files with any structure.

For a more persistent version of this — one where the side-by-side layout survives across Excel restarts and you don't have to manage two separate windows cluttering your taskbar — DualPane lets you pair any two tabs from any workbook into a single split view inside one Excel window, with synced scroll that stays on when you resize the panes.

Method 2: Conditional Formatting (for cell-level diffs)

When both files have the same column structure and you're comparing two sheets cell-by-cell, Conditional Formatting gives you a visual diff — cells that differ light up in a chosen color.

1. 1
   Open both workbooks and tile them vertically Use View → Arrange All → Vertical so both files are on screen at once. The left file is your reference (baseline), the right file is what you're checking.
2. 2
   Select the range in your reference file Click the cell in the top-left of your comparison range, then shift-click the bottom-right (or drag A1:Z500 for a larger range).
3. 3
   Create a Conditional Formatting rule Go to Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format. Enter: =A1<>[filename.xlsx]SheetName!A1
4. 4
   Pick a highlight color and apply Choose a fill color (orange or red stands out), click OK, then scroll through the sheet to find highlighted cells. Each highlight is a cell that differs between the two files.

Method 3: VLOOKUP or IF formulas (for row-level matching)

When you have a unique identifier in both files — an order ID, product code, employee number — VLOOKUP lets you match rows across files and check whether values changed. This is the right method when files have different row orders or when some rows exist in one file but not the other.

Finding rows that exist in one file but not the other

If you want to find missing rows (records in File A that don't appear in File B), use IFERROR + VLOOKUP:

This looks up each value in column A from File A inside File B. If VLOOKUP returns #N/A, IFERROR catches it and returns \"MISSING in File B\". Any cell showing that text is a row that exists only in File A.

Checking whether a value changed

To check if a specific column differs between files (say, the price in column C):

This looks up the matching row in File B and compares the value in column C. Match means nothing changed. Changed: [value] shows the old value from File B next to the new value in the current sheet.

Which method should you use?

A better comparison: DualPane for saved tab pairs

If you're doing the same comparison more than once a week — the same two tabs, the same two files, every time — the native methods all require rebuilding the setup from scratch each session. That's where DualPane changes the workflow.

DualPane is an Excel add-in that puts any two tabs from any workbook into a side-by-side split view inside a single Excel window. Both sheets scroll together (or independently, with a persistent synced scroll toggle). The key feature for recurring comparisons: you can save named tab pairs as layouts.

Save a pair once — say, Actuals vs Budget or v1 Prices vs v2 Prices — and next time you open the workbook, one click loads both sheets in split view with no Arrange All, no tiling, no second taskbar window. The layout travels with the file.