You’re comparing Excel files the hard way – here are two better methods

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

Method 1: Highlight unmatched values ​​with conditional formatting

Use visual alerts to audit smaller data sets

Conditional formatting is a quick and visual way to detect differences between two sets of data. However, it only works when both versions are in the same workbook – Excel does not allow conditional formatting formulas to reference another workbook. If they are in different workbooks, open both workbooks and then follow these steps to consolidate them:

1. Right click on the tab updated sheet, then click Move or copy.

2. In it To reserve 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, see Create a copy if you just 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 it View tab for open a second instance of your filethen click Organize everything > Vertical to tile them. Now you can have both worksheets open at the same time, side by side.

You are now ready to tell Excel to highlight any discrepancies between the two versions:

1. Select the entire range of data on your original sheet.

2. In it Home tab, click Conditional Formatting > New Rule.

3. Click Use a formula to determine which cells to format.

4. In the dialog box, click Formatand then choose a highlight color like light red.

5. Create the formula by selecting the first cell of the original data set and typing <> and selecting the corresponding cell in the updated sheet. Press F4 three times in each reference to eliminate absolute lock.

Here is the formula I used in my example:

=A2<>Sales_Updated!A2

This method works well for small, clean data sets, but has a major weakness: it depends on perfect row alignment. If someone inserts, deletes, or reorders rows in any of the sheets, Excel will continue to compare by position, causing widespread false mismatches.

Method 2: Compare row data using Power Query joins

Create a durable audit trail for large worksheets

If you need to compare versions of larger data sets, Energy consultation It is the most robust method. Instead of comparing cells by position, it compares data based on values ​​you define, making it resilient to row movement and structural changes.

First, make sure both data sets 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 and load in.

3. Select Just create connection and click OK.

4. Repeat these steps for the second table.

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

1. Double click on one of the queries in the Consultations and connections panel to reopen the editor.

2. So, in the Home tab, click Merge queries > Merge queries as new.

3. In it Bind dialog box, select the original table above and the updated table below.

4. Click the first column in the table above and then click the same column in the table below. Then press and hold Control while repeating this process for all other columns. Note that each pair of columns is numbered to indicate the order of matching.

5. Choose Anti-left as union type and click OK. This returns rows that exist in the original but do not have an exact match in the updated version, meaning they were deleted or altered in a way that breaks the match.

Now, make a couple of small adjustments to the new combined query:

1. Delete the column that contains the second merged table (the nested table column).

2. Rename the query to something like v1_Changed.

Now you need to repeat the process, but reverse the order of the table in the Merge dialog: select the option updated table at top and the original table belowthen run the same Anti-left join and rename the query (e.g. v2_Changed). Running the join in both directions returns two results: one that shows rows that exist only in the original data set (deleted or modified on the update) and another that shows rows that exist only in the updated data set (new or modified rows).

Once you have done this, click Close and load incheck Tableand click OK to load these queries into two new worksheets.

The nice thing about this method is that if you add a new row to one of the two source data sets and click Update all in it Data tab, the new record is automatically included in the “Changed” query, keeping its information up to date.

The next time an “updated” book arrives 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 data sets where the order of the rows cannot be trusted, Power Query is the best way to ensure nothing slips through the cracks. Either way, you’ve turned a tedious manual task into an automated, repeatable process.

SW

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, 1TB of OneDrive storage, and more.