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
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.