MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Compare Two Lists by Using Go To Special


May 29, 2019 - 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


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.