You’re comparing Excel files the hard way—here are 2 better methods

It’s Monday morning, and an “updated” copy of an Excel workbook is sitting in your inbox. But when you open it, it’s often impossible to tell what’s changed. Instead of playing a game of spot the difference, use these built-in Excel tools to highlight differences in seconds.

If you’re using Office Professional Plus or Microsoft 365 Enterprise, you may already have a dedicated tool called Spreadsheet Compare. It’s a standalone utility that handles the heavy lifting for you, but since it isn’t included in standard Home or Business versions, the methods below are your best bet for universal compatibility.

Method 1: Highlight mismatched values with conditional formatting

Use visual alerts to audit smaller datasets

Conditional formatting is a quick, visual way to spot differences between two datasets. However, it only works when both versions are in the same workbook—Excel doesn’t allow conditional formatting formulas to reference another workbook. If they’re in different workbooks, open both workbooks, then follow these steps to consolidate them:

1. Right-click the tab of the updated sheet, then click Move or Copy.
2. In the To book drop-down menu, select the original workbook.
3. Select Move to end so the updated sheet appears to the right of the original sheet. Also, check Create a Copy if you only want to duplicate this sheet in the other workbook, or uncheck this option if you want to move it permanently.
4. Click OK.

With both sheets now in a single file, click New Window in the View tab to open a second instance of your file, then click Arrange All > Vertical to tile them. Now you can have both worksheets open at the same time, side by side.

You’re now ready to tell Excel to highlight the discrepancies between the two versions:

1. Select the whole data range on your original sheet.
2. In the Home tab, click Conditional Formatting > New Rule.
3. Click Use a formula to determine which cells to format.
4. In the dialog, click Format, then choose a highlight color such as light red.
5. Build the formula by selecting the first cell in the original dataset, typing <> and selecting the corresponding cell in the updated sheet. Press F4 three times on each reference to remove absolute locking.

Here’s the formula I used in my example:

=A2<>Sales_Updated!A2

This method works well for small, clean datasets, but it has a major weakness: it depends on perfect row alignment. If someone inserts, deletes, or reorders rows in either sheet, Excel will continue comparing by position, which leads to widespread false mismatches.

If Excel highlights a cell that appears to match, you likely have hidden characters or formatting mismatches. First, remove stray spaces using Find and Replace (Ctrl+H) or the TRIM function, then check for formatting mismatches by clicking the green triangle in a cell and choosing Convert to Number.

Method 2: Compare row data using Power Query joins

Build a durable audit trail for large worksheets

If you need to compare versions of larger datasets, Power Query is the most robust method. Instead of comparing cells by position, it matches data based on values you define—making it resistant to row movement and structural changes.

First, ensure both datasets are formatted as Excel tables (Ctrl+T), then load them both into the Power Query Editor as connections:

1. Load the first table into Power Query by selecting one of its cells and going to Data > From Table/Range.
2. In the editor, click Close & Load To.
3. Select Only Create Connection and click OK.
4. Repeat these steps for the second table.

With both tables loaded into Power Query, you can begin the comparison. Start by comparing the original table against the updated version:

1. Double-click one of the queries in the Queries & Connections pane to reopen the editor.
2. Then, in the Home tab, click Merge Queries > Merge Queries as New.
3. In the Merge dialog, select the original table on top and the updated table below.
4. Click the first column in the top table, then click the same column in the bottom table. Then, hold Ctrl as you repeat this process for all the other columns. Notice that each column pairing is numbered to indicate the matching order.
5. Choose Left Anti as the join type and click OK. This returns rows that exist in the original but have no exact match in the updated version—meaning they were either removed or altered in a way that breaks the match.

Now, make a couple of small tweaks to the new merged query:

1. Remove the column containing the merged second table (the nested table column).
2. Rename the query to something like v1_Changed.

You now need to repeat the process, but reverse the table order in the Merge dialog—select the updated table on top and the original table below, then run the same Left Anti join and rename the query (for example, v2_Changed). Running the merge in both directions gives you two outputs: one showing rows that exist only in the original dataset (removed or changed in the update), and another showing rows that exist only in the updated dataset (new or changed rows).

Once you’ve done this, click Close & Load To, check Table, and click OK to load these queries to two new worksheets.

The beauty of this method is that if you add a new row to one of the two source datasets and click Refresh All in the Data tab, the new record is automatically included in the “Changed” query, keeping your information up to date.

Related

You don’t need VBA to auto-refresh your Power Queries in Excel

Stop relying on manual clicks and clunky code—let Excel refresh your queries automatically.

The next time an “updated” workbook lands in your inbox, choose your tool based on the data. For a quick five-minute audit of a small list, the conditional formatting trick is your best friend. But for large-scale datasets where row order can’t be trusted, Power Query is the best way to ensure nothing slips through the cracks. Either way, you’ve turned a tedious manual chore into a repeatable, automated process.

OS

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.