Find Differences In Two Lists
October 31, 2022 - by Bill Jelen
![Find Differences In Two Lists Find Differences In Two Lists](/img/excel-tips/2022/10/find-differences-in-two-lists.jpg)
Problem: I have two lists that should be identical. I need to highlight any cells that are different.
![The data set in A1:D12 should be identical to F2:I12. Select B2:D12 then Ctrl+Select G2:I12.](/img/content/2022/10/LE10000749.jpg)
Strategy: Use the Go To Special dialog’s Row Differences.
1. Select B2:D12.
2. Hold down the Ctrl key while selecting G2:I12.
-
3. Choose Home, Find and Select, Go To Special.
4. In the Go To Special dialog, choose Row Differences. Click OK.
![Select Home, Find & Select, Go To Special. In the Go To Special dialog, choose Row Differences.](/img/content/2022/10/LE10000750.jpg)
Gotcha: Highlight row differences can only compare one column to another column. Initially, it will only compare column B to column G and highlight what changed. If you then press the F4 key once for each additional column, the program will redo the compare for the next column and then the next column.
After step 4, you will have this:
![The data B2:D12 that is different from G2:I12 is selected.](/img/content/2022/10/LE10000751.jpg)
5. Press the F4 key to compare column C to column H.
6. Press the F4 key to compare column D to column I.
7. Open the Paint Bucket icon on the Home tab and choose a fill color.
Result: All of the changed cells will be highlighted, in one list or the other.
![Apply a red fill to the selected cells and only the changed cells are selected.](/img/content/2022/10/LE10000752.jpg)
Alternate Strategy: You can use the Formula method of conditional formatting to highlight differences. Follow these steps:
1. Select G2:I12.
2. Type Alt+O followed by D. (That is O the letter).
3. Click New Rule.
4. Choose Use a Formula to Determine Which Cells to Format.
5. Type a formula of
=G2<>B2
.6. Click the Format… button.
7. Click the Fill tab.
8. Choose a red format.
9. Click OK. Click OK.
![Achieving the same effect using Conditonal Formatting: Celect G2:I2 and set up a rule that uses this formula: =G2<>B2. For the Format, use a red fill.](/img/content/2022/10/LE10000753.jpg)
The changed cells on the right are highlighted.
![The advantage of conditional formatting: if you later change a value, it will suddenly be formatted in red.](/img/content/2022/10/LE10000754.jpg)
This article is an excerpt from Power Excel With MrExcel
Title photo by Pierre Bamin on Unsplash