Compare two lists or columns in Microsoft Excel

Posted on:  12/24/2016
Compare two lists or columns in Microsoft Excel

If you need to compare two lists or columns in Microsoft Excel, you can either use Conditional Formatting or you can you an empty column and use Countif function. You can even use both. This is handy when you want to compare 2016 customers to 2017 customers or a list of employees or products.

To compare the master list, let’s assume this is column A and you have a new list which is column B and you want to see who is already in Column A that is in Column B, let’s use Conditional Formatting first.

  1. Select A2 to A10.
  2. Click Conditional Formatting, Click New Rule.
  3. Click Use a formula to determine which cells to format.
  4. Type in the formula =Countif($A$2:$A$10,$B2).
  5. Click Format.
  6. Click Pattern and pick yellow.
  7. Click OK.
  8. Type another name in column B to test it.

Two screenshots below

This is the end result. Notice Tammy and Martha are not in the master list

List compare 1

This is the function you will type in the Conditional Formatting box

List compare 2

YouTube video on comparing two columns or lists

Chris Menard

Chris Menard is a certified Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. He runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events every year, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the Support Staff Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.

Categories