Let’s assume, we have the following (although realistic) challenge: We got two lists which should have the same items in Excel. But they aren’t exactly the same so that we need to compare them. But how do we find out the best way, which items are missing in either one of the lists? Instead of comparing manually, there are some easy steps.
In our example, we have two lists containing three brands of cars. Of course, with only three items per list, it’s easy to see which ones are missing. But these lists are often much longer.
Compare two lists step by step
So, let’s compare them with three steps (the number are corresponding to the picture above:
- First, set up a comparison table. This might as well be an additional worksheet. The first column will later on contain the whole list. The following columns will show, if the item is available in the lists. Now copy each lists underneath each other.
- Next – as many items might occur two times in our combined list – we need to remove duplicates. Excel provides a (very useful) function, called “Remove Duplicates”. Therefore, mark the whole list and go to Data and click on Remove Duplicates. Follow the steps (if you are asked, don’t extend the cell range).
- As the last step, we count how often each item occurs in each list. In our example, the formula in cell E3 would be “=COUNTIFS(A:A;$D3)”. Copy this to the whole range and now you will see, which item is missing in each list as well as if any item appears several times in a list
There are probably also other methods to compare two lists, but these steps should be comparatively easy. The whole process is shown in the images below. The numbers on the picture are referring to the steps described above.
Expert tip: Use ‘Professor Excel Tools’
The Excel add-in ‘Professor Excel Tools’ offers a feature for comparing two worksheets. Click on the ‘Compare Sheets’ button in the middle of the Professor Excel ribbon. As shown on the picture on the right hand side, you have several options for highlighting the differences:
- Do you want to show all values or just the highlights?
- If you want to show all values, you can select how you want to highlight the differences (color and/or with comments).
When you click on Start, your two worksheets will be copied into a new workbook. A third sheet with the differences on it will be created.
This method is especially useful, if you have to versions of a worksheet: An original version and a new version with changes.
Please download the free trial version below and see if it works for you.
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.