How To Compare Two Excel Sheets

Comparing two Excel sheets is often necessary when you want to track changes, spot differences, or ensure data accuracy. Whether you’re working with financial reports, inventories, or large data sets, Excel offers a few simple ways to highlight what has changed between two sheets. You don’t need advanced skills or expensive tools—just a basic understanding of Excel functions. Here is how to compare two excel sheets.

1. Use the Side-by-Side View

One of the easiest ways to compare sheets visually is by placing them next to each other.

• Open both Excel sheets.
• Go to the View tab at the top of Excel.
• Click on View Side by Side.
• Both sheets will now appear next to each other, scrolling in sync.
• You can manually check differences in values, formatting, or layout.

2. Use Formulas to Find Differences

If you want to compare specific cells for changes, use a formula.

• Open a new third sheet or use one of the existing sheets.
• In a blank cell (say C2), type a formula like:
  =IF(Sheet1!A2=Sheet2!A2, “Match”, “Different”)
• Drag the formula down to check more rows.
• This will help you spot which cells have changed.

3. Use Conditional Formatting

Highlight differences between two sheets automatically with color.

• Copy the data from one sheet and paste it into a new sheet (for reference).
• On the original sheet, select the data range.
• Go to Home > Conditional Formatting > New Rule.
• Choose “Use a formula to determine which cells to format”.
• Enter a formula like: =A1<>Sheet2!A1
• Set a format (e.g., red fill) to highlight differences.

4. Use Excel’s “Inquire” Add-in (For Microsoft 365 or Pro versions)

If you have access to Excel’s Inquire tool:

• Go to File > Options > Add-ins.
• At the bottom, next to “Manage,” select COM Add-ins and click Go.
• Tick Inquire and click OK.
• Once added, go to the Inquire tab, click Compare Files, and select your two workbooks.
• Excel will generate a report showing all differences.

5. Use a Third-Party Tool (Optional)

There are free tools online like Spreadsheet Compare, or software like Beyond Compare and DiffEngineX, that specialize in comparing Excel files. These tools are helpful for advanced users or when handling large datasets.

Also Read: How To Clean Waterblommetjies