How to compare two Excel sheets and highlight the differences (with AI, tested in Excel)

01Why comparing row by row gets it wrong

Excel can show two worksheets at once: on the View tab, View Side by Side, then Synchronous Scrolling to move both together (Microsoft). It's perfect for reading. But your eyes still compare line 7 with line 7, and so does a formula like =C2=Friday!C2. The moment one export is sorted differently, or a line is added, everything below it shifts.

Here are the two counts of our fictional hardware store. Monday's export is sorted by SKU. Friday's came out in another order, and it went through Excel once on the way, which matters later.

We put both lists in Microsoft Excel for Mac (version 16.113) on October 13, 2026, and compared them three ways: cell against cell on the same row, a lookup wrapped in IFERROR(…, 0), and the formula an AI wrote for us (method 2 below). These are Excel's real results:

Row by row, six of the eight lines read CHANGED. In reality, two quantities changed (P102 from 9 to 8, P106 from 12 to 0), one product was removed and one was added. Match on a key, never on the row: that's the whole trick, whatever tool you use.

02The zero trap: P103 vanished, and its stock was zero

Look at the middle column. When a lookup is wrapped in IFERROR(…, 0), so that a missing product shows 0 instead of an error, a removed row becomes impossible to tell apart from an empty shelf. P103, the 1-inch ball valve, had 0 in stock on Monday and no row at all on Friday. Excel's verdict with that formula: SAME.

03Method 1: compare two lists with an AI prompt

For a short list (a few dozen lines), paste both lists into ChatGPT, Claude, Gemini or Copilot, with rules. Without rules, the AI decides for you what counts as the same line. Here is the prompt, with its five rules:

Compare these two lists. Rules:
1. Match rows by exact [KEY, e.g. SKU or invoice number], never by row position. Keep every [KEY] as text, exactly as written.
2. A quantity of 0 is a real value, not a missing row.
3. Report four lists: ADDED, REMOVED, CHANGED (with before → after) and UNCHANGED.
4. If two [KEY]s differ only by spaces, capital letters or leading zeros, don't merge them: list them under POSSIBLE MATCH for me to decide.
5. Don't rewrite or correct either list.

[NAME OF LIST 1, e.g. MONDAY]
[PASTE LIST 1 WITH ITS HEADER ROW]

[NAME OF LIST 2, e.g. FRIDAY]
[PASTE LIST 2 WITH ITS HEADER ROW]

We ran it with Claude in a fresh conversation on October 13, 2026, with our two counts pasted in place of the brackets. Here is its answer, unedited:

## Stock count comparison: Monday → Friday

Rows were matched by exact SKU text. Row order made no difference.

### ADDED (on Friday only)
| SKU | Item | Qty |
|---|---|---|
| `P104` | Teflon tape | 25 |

### REMOVED (on Monday only)
| SKU | Item | Qty (Monday) |
|---|---|---|
| `P103` | Ball valve 1 in | 0 |

*P103 had a real quantity of 0 on Monday. Its row is missing on Friday, so it counts as removed.*

### CHANGED
| SKU | Item | Before → After |
|---|---|---|
| `P102` | PEX tubing 3/4 in | 9 → 8 |
| `P106` | Shut-off valve 1/2 in | 12 → 0 |

*P106 is still on the Friday list with a quantity of 0, so it is a change and not a removal.*

### UNCHANGED
| SKU | Item | Qty |
|---|---|---|
| `P101` | Copper elbow 1/2 in | 40 |
| `P105` | Pipe wrench 14 in | 6 |
| `P108` | Drain snake 25 ft | 3 |

### POSSIBLE MATCH (your call)
| Monday SKU | Friday SKU | Difference | Item | Qty Mon / Fri |
|---|---|---|---|---|
| `"0412"` | `"412"` | Leading zero | Brass fitting 1/2 in | 30 / 30 |
| `"P107"` | `"P107 "` | Trailing space on Friday | Thread sealant | 15 / 15 |

I left these two pairs out of the four lists above until you decide:
- **If they're the same item:** each pair goes under UNCHANGED, since the item name and quantity are identical.
- **If they're different SKUs:** `0412` and `P107` go under REMOVED, and `412` and `P107 ` go under ADDED.

### Check
- Monday: 8 rows = 3 unchanged + 2 changed + 1 removed + 2 possible matches
- Friday: 8 rows = 3 unchanged + 2 changed + 1 added + 2 possible matches

Neither list was edited. SKUs appear exactly as written, including the trailing space in `"P107 "`.

Every line is where it belongs, and the AI checked its own work: eight rows on each side, each classified once. For comparison, we also asked the same AI, without rules, to "tell me what changed". It found P103 and the new tape, but it listed the brass fitting and the thread sealant as unchanged, and only mentioned their codes further down, under "data issues to check". Probably right. But deciding that two codes are the same product is your call, not the AI's.

04Near matches: lost zeros and invisible spaces

The two possible matches are classics. Friday's brass fitting reads 412 instead of 0412. Microsoft says it plainly: Excel automatically removes leading zeros (Microsoft). Format the code column as Text before you paste, or, in Microsoft 365 and Excel 2024 (Windows and Mac), turn that conversion off under Automatic Data Conversions.

Friday's P107 ends with a space you can't see. Excel's TRIM function removes it: it "removes all spaces from text except for single spaces between words" (TRIM). Microsoft also notes that TRIM does not remove the non-breaking space (character 160) that often comes with text copied from web pages.

05Method 2: ask the AI for the formula, then test it in Excel

For a real export with hundreds or thousands of rows, don't paste it into a chat. Ask the AI to write the formula, and let Excel do the matching. Our request:

I use Excel with English function names and commas between arguments. My workbook has two sheets, Monday and Friday. On both, column A is the SKU (text), column B the item name and column C the quantity, with headers in row 1 and data in rows 2 to 9.

Write:
1. A formula for Monday!D2, to fill down, that returns REMOVED if the SKU is not on the Friday sheet, CHANGED if the quantity is different, and SAME otherwise. Match the SKU exactly. A quantity of 0 is a real value: a missing SKU must never come back as 0.
2. A formula for Friday!D2, to fill down, that returns ADDED if the SKU is not on the Monday sheet.
3. A conditional formatting formula that highlights the whole row on the Monday sheet when D is not SAME.
4. A short test table: for each kind of row (same, changed, changed to 0, removed with 0 stock, added), the result I should see.
Keep the formulas as short as you can.

**1. Monday!D2** (fill down to D9)

```
=IFNA(IF(XLOOKUP(A2,Friday!A$2:A$9,Friday!C$2:C$9)=C2,"SAME","CHANGED"),"REMOVED")
```

- **Exact SKU match:** XLOOKUP looks for an exact match by default. VLOOKUP, MATCH and COUNTIF treat `*` and `?` in a SKU as wildcards, but XLOOKUP doesn't.
- **A missing SKU is never 0:** XLOOKUP returns #N/A. The comparison and the IF pass the #N/A through unchanged, and only IFNA turns it into REMOVED. A real 0 on Friday comes back as 0 and is compared like any other number.
- **Upper and lower case:** XLOOKUP ignores case, so ab-01 matches AB-01. If case matters for your SKUs, replace `A2,Friday!A$2:A$9` with `TRUE,EXACT(A2,Friday!A$2:A$9)`.
- **Excel version:** XLOOKUP and XMATCH need Microsoft 365 or Excel 2021 or later.

**2. Friday!D2** (fill down to D9)

```
=IF(ISNA(XMATCH(A2,Monday!A$2:A$9)),"ADDED","")
```

XMATCH also uses an exact match with no wildcards. To make it case-sensitive, use `XMATCH(TRUE,EXACT(A2,Monday!A$2:A$9))` instead.

**3. Conditional formatting (Monday sheet)**

Select **A2:D9**, with A2 as the active cell. Then go to Home › Conditional Formatting › New Rule › "Use a formula to determine which cells to format", and enter:

```
=$D2<>"SAME"
```

The `$` fixes the column to D, so every cell in a row checks that row's status. Apply the rule only to rows 2–9. Empty rows have a blank D, which also counts as "not SAME", so they would be highlighted too.

**4. Test table**

| Case | Monday (A / C) | Friday (A / C) | Monday!D | Friday!D | Monday row highlighted? |
|---|---|---|---|---|---|
| Same | A100 / 5 | A100 / 5 | SAME | *(blank)* | No |
| Changed | A200 / 8 | A200 / 3 | CHANGED | *(blank)* | Yes |
| Changed to 0 | A300 / 4 | A300 / 0 | CHANGED | *(blank)* | Yes |
| Removed, 0 stock | A400 / 0 | *(not there)* | REMOVED | — | Yes |
| Added | *(not there)* | A500 / 6 | — | ADDED | — |

The two rows with 0 catch the common mistakes:
- **Changed to 0** fails if a 0 is treated as "not found".
- **Removed, 0 stock** wrongly shows SAME if a missing SKU is turned into 0, for example with `IFERROR(...,0)`.

Then we tested it in Excel on the eight tricky rows above (last column of the results table). P103, zero then gone: REMOVED. P106, 12 to 0: CHANGED, not fooled by the zero. P104, the Teflon tape: ADDED on Friday's sheet. The two near matches came out strict, 0412 and P107 as REMOVED and 412 and "P107 " as ADDED, which is exactly what we asked for. The highlight rule, =$D2<>"SAME", was true on the five Monday rows that need a look.

1. 1
   Put each export on its own sheet

   Name them Monday and Friday, or Before and After. Format the code column as Text before you paste.

2. 2
   Add the status formulas

   Paste the AI's formula in D2 of each sheet and fill it down.

3. 3
   Add the highlight

   Select the rows, then Home › Conditional Formatting › New Rule › Use a formula. Enter =$D2<>"SAME" and pick a fill.

4. 4
   Test before you trust

   Add four test rows: a quantity that goes to 0, a row that disappears with 0 stock, a new row, and a code that lost its leading zero. Check each status once, by hand.

06Method 3: make it refresh itself every week (Power Query)

If the two exports arrive as files every week, Power Query can do the matching without formulas. Microsoft says Power Query is available in Excel for Windows, Excel for Mac and Excel for the web, and that you can refresh the data periodically to keep it up to date (Microsoft).

1. 1
   Load both exports

   Data › Get Data, from each file, or from the two tables in your workbook.

2. 2
   Merge on the SKU: left anti

   Merge Monday with Friday on the SKU column, join kind Left anti: Microsoft describes it as "only rows from the left table" (merge overview). That's your REMOVED list.

3. 3
   Merge again: right anti

   Same merge, join kind Right anti, "only rows from the right table" (right anti join). That's your ADDED list.

4. 4
   Inner join to compare quantities

   An inner join keeps only the rows found in both tables. Add a column that compares the two quantities: that's your CHANGED list.

5. 5
   Load, then refresh

   Load the three results to sheets. Next week, replace the two files and choose Data › Refresh All.

Power Query also offers fuzzy matching, which Microsoft supports on text columns only. Useful, but a fuzzy match is a guess: keep those pairs on a review list instead of accepting them silently. And if your company has Microsoft 365 Apps for enterprise, or Office Professional Plus 2013, 2016 or 2019, there's a separate tool, Spreadsheet Compare, that reports the differences between two workbooks; Microsoft says it is only available with those editions (Microsoft).

07What it's worth

We put the numbers in our free ROI calculator: comparing exports and chasing differences for 2 hours a week, at a loaded cost of $35 an hour, is $3,360 a year. With the calculator's typical assumption that about half of report work can be automated, that's 48 hours and $1,680 a year back. The cautious scenario says $1,008 (29 hours), the ambitious one $2,184 (62 hours). It's an indicative estimate, not a promise, and the review step stays human.

08Not just for stock: any two lists

The same prompt works on lists you compare at home. We tried it on two versions of a kids' hockey team contact list (fictional names, example.com addresses), matching people by name:

Here are two versions of the contact list for my kid's hockey team. Tell me who was added, who was removed and whose details changed. Match people by name. If two names look like the same person written differently (accents, spaces, capital letters), don't merge them: list them under POSSIBLE MATCH and tell me why. Don't correct either list.

LIST FROM SEPTEMBER
Name,Parent email
Mélanie Roy,m.roy@example.com
Jean-François Côté,jf.cote@example.com
Amélie Gagnon,amelie.g@example.com
Noah Tremblay,ntremblay@example.com
Léa Bouchard,lea.b@example.com

LIST FROM OCTOBER
Name,Parent email
Jean-Francois Cote,jf.cote@example.com
Melanie Roy,melanie.roy@example.com
Noah Tremblay,ntremblay@example.com
Léa Bouchard,lea.b@example.com
Olivier Pelletier,o.pelletier@example.com

I matched people only when their names are spelled exactly the same. Two pairs of names look like the same person written differently. I kept those pairs apart and listed them under **Possible match**.

## Added (only in October)
| Name | Parent email |
|---|---|
| Olivier Pelletier | o.pelletier@example.com |

## Removed (only in September)
| Name | Parent email |
|---|---|
| Amélie Gagnon | amelie.g@example.com |

## Details changed
**None.** Everyone whose name matches exactly has the same email in both lists.

## Unchanged
| Name | Parent email |
|---|---|
| Noah Tremblay | ntremblay@example.com |
| Léa Bouchard | lea.b@example.com |

## Possible match (not merged)

**1. "Jean-François Côté" (September) and "Jean-Francois Cote" (October)**
- **Why they might be the same person:** the names differ only by accents ("ç" became "c" and "ô" became "o"), and both have the same email, jf.cote@example.com.
- **How sure:** very likely the same person, with the accents dropped in the October list. If so, nothing changed except how the name is written.

**2. "Mélanie Roy" (September) and "Melanie Roy" (October)**
- **Why they might be the same person:** the names differ only by the accent on the "é".
- **What also differs:** the email changed from m.roy@example.com to melanie.roy@example.com.
- **How sure:** less sure than pair 1, because the email is different too. It could be the same parent with a new email, or it could be a different family.
- **If it is the same person:** her email changed, and that is the only real change.

If you confirm these two pairs aren't the same people, they become 2 more removals (the September spellings) and 2 more additions (the October spellings).

It found the one real addition and the one real removal, and kept two pairs apart: Jean-François Côté and Jean-Francois Cote (same email, very likely the same parent), and Mélanie Roy and Melanie Roy (different email, less sure). Accents are the leading zeros of Quebec names: a list that went through another system can lose them.

09Start this week

1. Pick two exports you already compare by hand.
2. Run the prompt on a short sample and check it against what you know.
3. Ask for the formula, add the four test rows, and check them once.
4. When it passes, move to Power Query and refresh it every week.
5. Get the free automation map to see what else in your week could run this way.