Can Excel compare two files and merge them?

Excel does not have a native 'merge' button for disparate files. You generally have to use the 'Consolidate' tool under the Data tab or Power Query to combine datasets based on a common identifier.

Why is the Spreadsheets Compare tool missing?

The 'Inquire' add-in, which includes Spreadsheet Compare, is only available in specific versions of Excel, such as Microsoft 365 Enterprise or Office Professional Plus. If you have a Home or Business edition, you will likely need to use formulas or external tools.

How do I compare two columns for duplicates in different files?

The easiest way is to use a VLOOKUP or XLOOKUP formula. If the formula returns an #N/A error, it means the value in the first column does not exist in the second.

Blog · DocumentChecker

How to Compare Two Excel Files for Changes

1 September 2026 · 3 min read

When you are dealing with large datasets, manual checking is not an option. Whether you are reconciling monthly accounts or verifying a price list against a supplier update, you need a reliable way to spot what has changed. There are several ways to compare two Excel files for changes, ranging from quick visual checks to precise formula-based identification. This guide explains the most effective methods depending on your specific requirements and technical comfort level.

Visual Comparison with View Side by Side

For small spreadsheets where you simply need to spot obvious discrepancies, Excel’s built-in viewing tools are the quickest starting point. This method does not highlight changes for you, but it synchronises scrolling so you can inspect two sheets simultaneously.

Open both workbooks and navigate to the View tab on the ribbon. Click 'View Side by Side'. If you have more than two files open, Excel will ask which one you wish to compare. Ensure 'Synchronous Scrolling' is enabled so that when you move down one sheet, the other moves at the same pace.

Using Conditional Formatting to Highlight Mismatches

Conditional formatting is a powerful way to make differences jump off the screen. This works best when you have two identical structures (e.g., two versions of the same table) and you want to see which specific cells have been altered.

To do this, copy the data from your second file into a new sheet within your first file. Select the data range in Sheet 1, go to Home > Conditional Formatting > New Rule, and select 'Use a formula to determine which cells to format'. Enter a formula like =A1<>Sheet2!A1. Set a fill colour, and Excel will automatically highlight every cell that does not match the corresponding cell in the second sheet.

The Formula Method for Detailed Audits

If you need a formal record of changes rather than just a visual highlight, using a comparison formula is the most robust manual method. This involves creating a third 'Results' sheet to act as a change log.

In the new sheet, enter a formula that checks for equality between the two source sheets. This allows you to return a specific text string like 'Changed' or 'Same' for every cell, making it easy to filter for discrepancies later.

Common formulas for this include:

IF(Sheet1!A1<>Sheet2!A1, "Difference", "") - A simple check for inequality.
EXACT(Sheet1!A1, Sheet2!A1) - Useful if you need to perform a case-sensitive comparison.
IF(ISERROR(VLOOKUP(A2, Sheet2!A:B, 2, FALSE)), "Missing", "Found") - Best for checking if entire rows are missing from one file.

Professional Tools for Complex Files

Manual methods often fail when spreadsheets become complex, contain hidden rows, or have slightly different formatting that trips up standard formulas. In a professional environment, errors in these comparisons can lead to significant financial or operational risks.

For those who need to compare documents, schedules, and source files to spot mismatches and omissions with high accuracy, Jittan’s DocumentChecker provides a focused alternative. It automates the detection of accuracy issues, ensuring that even minor changes do not go unnoticed in high-stakes documents.

Choosing the right method depends on whether you need a quick visual check or a rigorous audit of every cell. With these techniques, you can ensure your data remains accurate and consistent across different versions.

Quick answers

Can Excel compare two files and merge them?: Excel does not have a native 'merge' button for disparate files. You generally have to use the 'Consolidate' tool under the Data tab or Power Query to combine datasets based on a common identifier.
Why is the Spreadsheets Compare tool missing?: The 'Inquire' add-in, which includes Spreadsheet Compare, is only available in specific versions of Excel, such as Microsoft 365 Enterprise or Office Professional Plus. If you have a Home or Business edition, you will likely need to use formulas or external tools.
How do I compare two columns for duplicates in different files?: The easiest way is to use a VLOOKUP or XLOOKUP formula. If the formula returns an #N/A error, it means the value in the first column does not exist in the second.