This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Find Differences In Two Lists
Problem: I have two lists that should be identical. I need to highlight any cells that are different.
Figure 726 Did anything change between these lists?
Strategy: Use the Go To Special dialog’s Row Differences.
1. Select B2:B12.
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.
Figure 727 Select Row Differences.
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:
Figure 728 Two customers changed.
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.
Figure 729 Changed cells are highlighted.
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.
Figure 730 Highlight differences using conditional formatting.
The changed cells on the right are highlighted.
Figure 731 The highlights only appear in the second data set.