Compare Two Excel Sheets and Highlight the Differences

To compare two sheets in Excel, you either check them cell by cell (when both have the same layout and order) or by ID (when rows may have moved, been added or been removed). A simple IF formula, a conditional formatting rule and COUNTIF cover both cases, in any version of Excel. This guide is for anyone who has an old and a new version of a report and needs to know exactly what changed.

Which method should you use?#

The right method depends on whether the rows still line up:

All the examples use two sheets in one workbook, named Old and New, each with invoice IDs in column A and amounts in column B:

Between the two versions, INV-102 changed from 480 to 520, INV-103 was removed, and INV-106 was added.

Compare two sheets cell by cell with IF#

A comparison sheet checks each cell against the same cell on the other sheet. Add a new sheet (Windows: Shift+F11; Mac: Fn+Shift+F11, or click the + next to the tabs), then type this in A1:

=IF(Old!A1<>New!A1,"Old: "&Old!A1&" | New: "&New!A1,"")

Fill it right and down to cover the whole data area. A cell stays blank when both sheets match, and shows both values when they don't. <> means "not equal to"; the IF function guide explains the three parts of IF.

If you only want TRUE or FALSE, a bare comparison is enough: =Old!B3=New!B3 returns FALSE here, because 480 is not 520. The lesson on TRUE, FALSE and comparisons covers how these work. Comparisons with = ignore capitals; the guide to comparing two cells shows how to use EXACT when case matters.

If the sheet names contain spaces, Excel needs quotes around them: ='Old data'!A1.

Highlight differences with conditional formatting#

Conditional formatting colors cells that meet a rule, so you see changes right on the sheet:

1. On the Old sheet, select the data, for example A1:B6, starting from A1.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter =A1<>New!A1 and click Format to pick a fill color.
5. Click OK.

Write the rule for the top-left cell of your selection, with no $ signs. Excel then moves the reference for each cell, so B3 checks against New!B3. The guide to conditional formatting with a formula explains why the $ matters, and the conditional formatting lesson lets you try formula rules hands-on. Rules that refer to another sheet work in Excel 2010 and later.

Why row-by-row comparison can mislead you#

Cell-by-cell checks assume the same record sits on the same row in both sheets. In our example, that's only true for rows 2 and 3. Because INV-103 was removed, everything below moved up one row.

A row-by-row check of the amounts flags 4 differences (rows 3 to 6), but only one amount actually changed. The others are the same invoices sitting on different rows. So use cell-by-cell methods only when you're sure the rows line up, such as two copies of a fixed template.

Compare rows by ID when the order changes#

When rows can move, match them on a column that uniquely identifies each record, such as an invoice ID or employee number.

Find records missing from the new sheet. On the Old sheet, in C2:

=IF(COUNTIF(New!$A$2:$A$6,A2)=0,"Missing","Found")

COUNTIF counts how many times the ID appears in the New sheet. Zero means it's gone. Fill it down and INV-103 shows Missing. The COUNTIF function guide covers the syntax. Do the same on the New sheet against Old's IDs to find new records: INV-106 is the only one.

Find changed values. In Excel 2021 or Microsoft 365, bring back the new amount with XLOOKUP, then compare. In D2 on the Old sheet:

=XLOOKUP(A2,New!$A$2:$A$6,New!$B$2:$B$6,"Missing")

For INV-102 this returns 520. Then =IF(D2="Missing","Missing",IF(D2=B2,"Same","Changed")) labels each row. In older Excel, use VLOOKUP with FALSE in place of XLOOKUP. The guide to comparing two lists shows more ways to find matches and gaps, and the guide to finding duplicates in two columns covers the overlap.

Lock the lookup ranges with $ before you fill down, or they'll slide off the data.

Look at two sheets side by side#

Sometimes you just want to scroll through both. On Windows:

1. Go to View > New Window. A second window of the same workbook opens.
2. In the new window, click the other sheet's tab.
3. Go to View > View Side by Side. Synchronous Scrolling turns on, so both scroll together.

On a Mac, open the second window from Window > New Window and arrange the two windows next to each other.

Some Windows editions of Office also include Spreadsheet Compare, a separate program that lists every difference between two workbooks, including formulas and formatting. If you don't see it, the formula methods above do the job in any edition.

Practise it#

Most sheet comparisons come down to COUNTIF and a comparison inside IF. Practise COUNTIF in a live spreadsheet: you type the formula and it checks it instantly. Then learn TRUE, FALSE and comparisons and color cells with formula rules. Try the exercise on this page first. If you also need to spot repeats within one sheet, see how to highlight duplicates.