MrExcel.com - Your One Stop for Excel Tips & Solutions

Find Differences In Two Lists

October 31, 2022 - by

Find Differences In Two Lists

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.
Figure 784. Did anything change between these lists?

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.
Figure 785. 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:

The data B2:D12 that is different from G2:I12 is selected.
Figure 786. 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.

Apply a red fill to the selected cells and only the changed cells are selected.
Figure 787. 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.

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.
Figure 788. Highlight differences using conditional formatting.

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.
Figure 789. The highlights only appear in the second data set.

This article is an excerpt from Power Excel With MrExcel

Title photo by Pierre Bamin on Unsplash