How to Compare Two Excel Files for Differences

Method 1: View Side by Side (Built-in, No Formulas Needed)

Excel's built-in side-by-side comparison lets you scroll both files simultaneously and spot differences visually.

1. Open both Excel files
2. Go to the View tab in either file
3. Click View Side by Side
4. Enable Synchronous Scrolling (also in the View tab) so both files scroll together
5. Scroll through both sheets and compare cell by cell

Best for: Small files, quick visual checks, or when you know roughly where the differences are.
Limitation: You have to spot differences manually — there is no automatic highlighting.

Method 2: Formula-Based Difference Finder (Best for Data)

Create a third sheet that highlights every cell where the two files differ. This is the most reliable method for data comparison.

1. Open both files and create a new workbook (or a new sheet in either file)
2. In cell A1 of the comparison sheet, enter a formula that compares the same cell in both files:

=IF([File1.xlsx]Sheet1!A1=[File2.xlsx]Sheet1!A1,"","DIFFERENT")

3. Copy this formula across and down to cover the full data range
4. Any cell showing "DIFFERENT" marks a discrepancy
5. Use Ctrl+F to search for "DIFFERENT" and jump to each one

For a version that also shows the actual values that differ:

=IF([File1.xlsx]Sheet1!A1=[File2.xlsx]Sheet1!A1,"", "WAS: "&[File1.xlsx]Sheet1!A1&" | NOW: "&[File2.xlsx]Sheet1!A1)

Best for: Large data files, reconciliation tasks, audit trails.
Limitation: Requires both files to have the same structure and sheet layout.

Method 3: Conditional Formatting Highlight (Visual Comparison Within One File)

If you have copied both datasets into the same workbook (two sheets or two columns), use conditional formatting to highlight differences.

1. Copy your "new" data into column B, keeping the "old" data in column A
2. Select column B
3. Go to Home → Conditional Formatting → New Rule
4. Choose "Use a formula to determine which cells to format"
5. Enter: =B1<>A1
6. Set a highlight colour (e.g., orange fill) and click OK

All cells in column B that differ from column A will be highlighted automatically. This updates live as you make changes.

Best for: Side-by-side column comparison, small datasets, ongoing monitoring.
Limitation: Requires both datasets to be in the same workbook.

Method 4: Spreadsheet Compare (Excel Pro / Microsoft 365)

If you have Microsoft 365 or Office Professional Plus, you have access to the Spreadsheet Compare tool — a dedicated comparison utility built by Microsoft.

1. Open the Windows Start menu and search for "Spreadsheet Compare"
2. Click Compare Files
3. Select your two Excel files
4. Click OK — it produces a colour-coded report showing every difference: values, formulas, formats, named ranges, and more

The report is categorised by type of change and shows the old value, new value, and cell address for every difference.

Best for: Professional audits, detailed change reports, formula-level comparison.
Limitation: Only available in Microsoft 365 and Office Professional Plus — not in standard Office Home editions.

Comparing Specific Things: Quick Tips

Compare two lists for duplicates or missing items

Use COUNTIF to check which items from List 1 appear in List 2:

=IF(COUNTIF([File2.xlsx]Sheet1!A:A, A1)=0, "MISSING", "OK")

Any row showing "MISSING" exists in File 1 but not in File 2.

Compare totals only

For a quick sanity check: open both files, select the same range in each, and check the status bar at the bottom (it shows SUM). If the sums differ, there are differences — then use Method 2 to find exactly which cells.

Compare formulas (not just values)

The formula comparison requires Spreadsheet Compare (Method 4) or a manual check using Ctrl+` (Show Formulas mode) in both files to view formulas side by side.

Frequently Asked Questions

Can I compare files with different structures?

Formula methods (Method 2) require both files to have the same column/row layout. If the structures differ, use Spreadsheet Compare or manual comparison. For datasets with different row orders, match on a key column using VLOOKUP before comparing.

How do I compare two sheets within the same file?

Use the same formula approach as Method 2 but reference sheet names instead of file names: =IF(Sheet1!A1=Sheet2!A1,"","DIFFERENT"). This works without opening two separate windows.

I need to track who changed what. Is there a way to do that in Excel?

Excel's built-in Track Changes feature (available in older versions via Review → Track Changes) records edits with username and timestamp. In Microsoft 365, use Version History (File → Info → Version History) to see previous versions of the file and restore them.