How to Compare Two Excel Files

Comparing two Excel files sounds simple until you open them and discover 14 worksheets, thousands of rows, mysterious formulas, hidden columns, and one cell colored neon yellow for reasons lost to history. Suddenly, “find what changed” feels less like spreadsheet work and more like digital archaeology.

Fortunately, Excel offers several reliable ways to compare workbooks. You can inspect files side by side, use formulas to flag mismatches, highlight differences with conditional formatting, run Microsoft Spreadsheet Compare, or use Power Query to reconcile large datasets. The best method depends on what you need to compare: values, formulas, formatting, records, workbook structure, or all of the above.

This guide explains how to compare two Excel files step by step, including practical formulas, examples, troubleshooting tips, and lessons learned from real-world spreadsheet comparisons.

Decide What You Need to Compare First

Before clicking buttons or unleashing a formula across 80,000 rows, define what “different” means for your task. Two workbooks can differ in several ways:

• Cell values have changed.
• Formulas have been edited, removed, or replaced with typed values.
• Rows were added, deleted, or reordered.
• Formatting, comments, named ranges, or VBA code changed.
• One file contains missing or duplicate records.
• Numbers look identical but have different underlying data types.

A quick visual review may be enough for a 20-row budget. It is not enough for two customer databases containing 100,000 records. Choosing the right comparison method prevents both wasted time and the classic spreadsheet disaster known as “I thought they matched.”

Prepare the Workbooks Before Comparing Them

Save Backup Copies

Create copies of both files before making changes. Use clear names such as Sales_Original.xlsx and Sales_Revised.xlsx. If the files are stored in OneDrive or SharePoint, you may also have access to version history, but a separate backup still provides inexpensive insurance against enthusiastic clicking.

Confirm That the Files Use the Same Structure

Check worksheet names, column headers, table ranges, and reporting periods. A comparison can produce hundreds of false differences when one workbook starts data in row 2 and the other starts in row 3.

Also look for hidden worksheets, filtered rows, grouped columns, merged cells, and blank rows. These features can make two files appear similar while concealing important differences.

Identify a Unique Key

When comparing lists, identify a column that uniquely represents each record. Common keys include employee ID, invoice number, SKU, order number, or email address.

Do not assume names are unique. Two customers may share the name John Smith, and one customer may appear as “Acme Inc.” in one file and “ACME Incorporated” in another. A stable ID gives Excel something dependable to match.

Method 1: Compare Two Excel Files Side by Side

The fastest method is to open both workbooks and inspect them together. This works well when the files are small, follow the same layout, and require human judgment.

1. Open both Excel files.
2. Select the View tab.
3. Choose View Side by Side.
4. Select the workbook you want to compare if Excel asks you to choose one.
5. Turn on Synchronous Scrolling to move through both files together.
6. Use Reset Window Position if the windows become uneven.

Synchronous scrolling is especially useful when both worksheets have matching row and column arrangements. Scroll to row 500 in one workbook, and the second workbook follows along like a well-trained spreadsheet puppy.

Comparing Two Sheets in the Same Workbook

Excel normally displays one sheet from a workbook at a time. To view two sheets from the same file side by side:

1. Open the workbook.
2. Go to View and select New Window.
3. Choose a different worksheet in each window.
4. Select View Side by Side.

This method creates another window into the same workbook rather than creating a duplicate file.

When Side-by-Side Comparison Works Best

Use this approach for small budgets, short schedules, presentation-oriented reports, or worksheets where layout and visual formatting matter. Avoid relying on it for large financial datasets or compliance reviews. Human eyes are impressive, but after a few hundred rows they start negotiating for snacks.

Method 2: Compare Matching Cells With a Formula

If the two worksheets have the same layout, a simple formula can test corresponding cells.

Suppose the original data is on Sheet1 and the revised data is on Sheet2. Enter this formula in a third worksheet:

Copy the formula across and down to evaluate the required range. Excel will display Match when the values are equal and Different when they are not.

Return the Actual Values When Cells Differ

A more informative formula can show both values:

This is helpful when reviewers need to understand the change without repeatedly switching between worksheets.

Use EXACT for Case-Sensitive Comparisons

The standard equals operator does not treat uppercase and lowercase text as different. To distinguish ABC123 from abc123, use:

Case sensitivity matters when comparing product codes, passwords, system identifiers, or other values where capitalization has meaning.

Allow for Small Rounding Differences

Two calculated numbers may look identical while differing several decimal places below what Excel displays. For example, one cell may contain 25.120001 and another 25.12.

To compare numbers using a tolerance, use:

This formula treats differences smaller than one cent as matches. Adjust the tolerance according to the accuracy required for your data.

Method 3: Highlight Differences With Conditional Formatting

Conditional formatting is one of the easiest ways to make mismatches visually obvious. Instead of filling a helper column with labels, Excel can color cells that differ from the corresponding cells in another sheet.

1. Open the worksheet you want to review.
2. Select the comparison range, such as A2:F500.
3. Go to Home > Conditional Formatting > New Rule.
4. Select Use a formula to determine which cells to format.
5. Enter a comparison formula such as =A2<>Sheet2!A2.
6. Choose a fill or font format.
7. Select OK.

Cells that do not match will be highlighted. This creates a fast visual map of changes without altering the original values.

Conditional formatting works best when worksheets have identical structures. If rows were inserted, deleted, or sorted differently, cell-by-cell highlighting may flag nearly everything after the first shifted row.

Method 4: Compare Lists With XLOOKUP

When records appear in different orders, compare them by a unique key instead of row position. XLOOKUP is ideal for this task in modern Excel versions.

Assume both files contain these columns:

• Column A: Product ID
• Column B: Product Name
• Column C: Price

To retrieve the price from the second workbook based on Product ID, use a formula similar to:

You can then compare the returned price with the price in the current workbook:

A single formula can perform both steps:

Find Missing Records

To determine whether an ID from the first file exists in the second file, use:

This technique is useful for identifying deleted customers, missing invoices, absent inventory items, or records that did not transfer between systems.

Watch for Duplicate Keys

XLOOKUP normally returns the first match it finds. If the lookup column contains duplicate IDs, the formula may hide additional records. Check for duplicates before trusting the results:

Any result greater than 1 means the key is not unique and should be investigated.

Method 5: Use Microsoft Spreadsheet Compare

Spreadsheet Compare is designed for detailed workbook auditing. It can identify differences in values, formulas, formatting, named ranges, worksheet structure, and certain VBA content. Its output is far more comprehensive than manual scrolling.

The feature is available only in supported enterprise-oriented editions of Excel for Windows. It may not appear in personal subscriptions, Excel for Mac, or Excel for the web.

Enable the Inquire Add-In

1. Open Excel and select File > Options.
2. Select Add-ins.
3. Choose COM Add-ins in the Manage box.
4. Select Go.
5. Check Inquire and select OK.

Run the Workbook Comparison

1. Open the two workbooks.
2. Select the Inquire tab.
3. Choose Compare Files.
4. Select the original and revised workbook.
5. Choose the categories you want to compare.
6. Run the comparison and review the color-coded results.

Spreadsheet Compare is especially valuable for financial models, regulated reports, inherited workbooks, and files where a formula may have quietly been replaced by a number. That last one is the spreadsheet equivalent of removing a smoke detector because it was making noise.

Method 6: Compare Large Excel Tables With Power Query

Power Query is often the best choice when the workbooks contain large structured datasets. It can import both tables, standardize their columns, join them by one or more keys, and produce a repeatable comparison report.

Import Both Workbooks

1. Open a new Excel workbook.
2. Go to Data > Get Data > From File > From Workbook.
3. Select the first file and import the required table or worksheet.
4. Repeat the process for the second file.
5. Open both queries in Power Query Editor.

Merge the Tables

1. Select the first query.
2. Choose Home > Merge Queries as New.
3. Select the second query.
4. Choose the matching key column in each table.
5. Select the appropriate join type.

Useful join types include:

• Full outer join: Returns all rows from both files.
• Inner join: Returns records found in both files.
• Left anti join: Returns records found only in the first file.
• Right anti join: Returns records found only in the second file.

After merging, expand the matching table columns and add custom columns that test whether values such as price, status, quantity, or department have changed.

The major advantage is repeatability. After building the query once, you can replace the source files and select Refresh All. Excel reruns the same comparison without requiring you to rebuild dozens of formulas.

Use Version History for Cloud-Based Excel Files

If a workbook is stored in OneDrive or SharePoint, version history can help you inspect and restore previous versions.

1. Open the workbook.
2. Select the file name or go to File > Version History.
3. Open an earlier version in a separate window.
4. Compare it with the current version.
5. Restore the earlier version only when you are certain it is the correct one.

This method is useful when the real question is not merely “What differs?” but “Who changed this report before the Monday meeting?” Version history can narrow the investigation without requiring dozens of separately named attachments.

Common Problems When Comparing Excel Files

Rows Are in Different Orders

Do not compare cells by position when records were sorted differently. Use XLOOKUP, Power Query, or another key-based comparison method.

Text Contains Extra Spaces

Values such as Boston and Boston look identical but are not equal. Clean text with functions such as:

Nonbreaking spaces imported from websites or business systems may require additional cleanup with SUBSTITUTE.

Numbers Are Stored as Text

The number 100 stored as text may not behave like the numeric value 100. Standardize data types before comparison using Excel conversion tools, VALUE, or Power Query data-type settings.

Dates Include Hidden Times

Two cells may display July 1, 2026 while one contains midnight and the other contains 2:30 p.m. Compare the full underlying value or remove the time portion with INT when time is irrelevant.

Formulas Differ but Results Match

A formula comparison and a value comparison answer different questions. Two formulas may currently return the same number while using entirely different logic. Use Spreadsheet Compare or the FORMULATEXT function when the formulas themselves matter.

Hidden Content Is Overlooked

Review hidden rows, columns, worksheets, names, comments, external links, and macros. Important spreadsheet differences have a curious habit of hiding exactly where nobody thought to look.

Which Excel Comparison Method Should You Use?

Experience-Based Lessons From Comparing Excel Files

In practical spreadsheet work, the hardest part is rarely writing the comparison formula. The real challenge is understanding the data well enough to know whether a difference is meaningful. A price changing from $49.95 to $49.9500 is probably harmless. A formula changing from a SUM range ending at row 500 to one ending at row 499 may be a very expensive typo wearing a tiny disguise.

One of the most useful habits is to create a comparison summary before examining individual cells. Count the rows in each file, count unique IDs, identify duplicates, total important numeric columns, and compare worksheet names. These high-level checks reveal the shape of the problem. When one file has 12,450 records and the other has 12,447, you already know that at least three records require attention.

Another lesson is never to use row numbers as record identities. A revised file may contain exactly the same customers but in a different sort order. A direct comparison of row 20 against row 20 would report a mismatch even though both records exist. Matching by customer ID, invoice number, or another unique key produces a far more trustworthy result.

Data cleaning should also happen before the main comparison. Extra spaces, inconsistent capitalization, text-formatted numbers, and slightly different date formats generate noise. When every second row appears different, the important changes become harder to see. Standardizing text with TRIM, setting consistent data types, and agreeing on date precision can turn a chaotic report into a short review list.

For one-time comparisons, formulas are usually quicker than building an elaborate system. A helper column containing XLOOKUP and IF can solve the problem in minutes. For recurring monthly or weekly comparisons, however, Power Query generally pays for itself. Once the import, cleanup, merge, and difference rules are established, future comparisons may require little more than replacing the source files and refreshing the query.

Spreadsheet Compare is particularly valuable when receiving a revised financial model from another person. A normal value comparison may show that the totals still match, but it will not necessarily reveal that a formula was replaced with a manually typed number. That change may not cause an error today, yet it can break the model next month when the source data changes. Comparing formulas and workbook structure provides a deeper level of assurance.

It is also wise to separate findings into categories such as added records, deleted records, changed values, changed formulas, and formatting-only changes. Combining everything into one giant list makes review unnecessarily difficult. A finance manager may care deeply about formula changes but not about a slightly wider column. A design team may have the opposite priorities.

Finally, preserve the evidence. Keep the original files read-only, save the comparison output, record the date, and document the rules used to classify a difference. A comparison that cannot be reproduced is difficult to defend. This matters in audits, financial reporting, migrations, inventory reconciliation, and any situation where someone may later ask, “How did you determine these files were the same?” Ideally, your answer should involve a repeatable process rather than a heroic afternoon of scrolling.

Conclusion

Learning how to compare two Excel files gives you several options, from a quick side-by-side review to a detailed workbook audit. The correct method depends on file size, structure, row order, and the kinds of changes you need to detect.

Use side-by-side viewing for small visual checks, formulas and conditional formatting for aligned worksheets, XLOOKUP for key-based record matching, Spreadsheet Compare for formulas and formatting, and Power Query for large or recurring datasets. Whichever method you choose, clean the data first, verify unique keys, check for duplicates, and preserve backup copies.

The goal is not simply to produce a colorful list of differences. It is to separate genuine changes from harmless noise and create results that another person can review, understand, and reproduce without requiring a magnifying glass or emotional support workbook.

Note: Excel menus and feature availability can vary by operating system, Microsoft 365 plan, software edition, and update channel. Spreadsheet Compare and the Inquire add-in are not included in every Excel edition.