Excel 2020: Compare Two Lists by Using Go To Special

June 01, 2020 - by Bill Jelen

Excel Compare Two Lists by Using Go To Special. Photo Credit: Ben Weber at Unsplash.com

This tip is not as robust as using a Pivot Table to Compare Two Lists, but it comes in handy when you have to compare one column to another column.

In the figure below, say that you want to find any changes between column A and column D.

There are lists of products in A and D. Select A2:A9. Ctrl+Select D2:D9. Open Home, Find & Select, Go To Special. Choose Row Differences. OK.

Select the data in A2:A9 and then hold down the Ctrl key while you select the data in D2:D9.

Select, Home, Find & Select, Go To Special. Then, in the Go To Special dialog, choose Row Differences. Click OK.

Only the items in column A that do not match the items in column D are selected. Use a red font to mark these items, as shown below.

After choosing Row Differences, any items in column A that do not match column D are highlighted. Apply a red font so you can identify which have changed.

Caution

This technique works only for lists that are mostly identical. If you insert one new row near the top of the second list, causing all future rows to be offset by one row, each of those rows is marked as a row difference.

Thanks to Colleen Young for this tip.

Title Photo: Ben Weber at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.