Excel Tip of the Week #1 - Comparing lists

Hello all and welcome to a new regular feature from the ICAEW, the Excel Tip of the Week.

My name is David Lyford-Smith and I am an Excel expert with BDO, where I work on Excel training and education as well as maintaining a role in our Global Outsourcing division.  I also am a member of the Excel Community Advisory Committee.

Each week, I will bringing you a short post about a particular Excel topic.  These will range in both difficulty and focus.  The normal place for these postings will be within the Excel Community, but once a month they will feature here in ITCounts.  You can find out more about the Excel Community at the bottom of this post.

Each post will be tagged not only with the relevant topic area tags, but also with a rating indicating its place in the Spreadsheet Competency Framework.  This week's post is most suitable for General Users.

This week, I will be showing you an easy way to compare two lists of data to identify the differences and similarities between them.

Put the two lists side by side and select all the cells of both lists.  Then navigate to the Home tab on the Ribbon, and select Conditional Formatting => Highlight Cells Rules => Duplicate values:

From that menu, change the dropdown to highlight Unique values, and press OK:

 This will now highlight all the values which appear only in one of the two lists.  These are the differences between the lists (leaving the option as highlighting the duplicates would show which items the two lists have in common).

Imagine using this on two lists of staff that were paid in March and April 2013.  Some employees would be highlighted just in March - meaning they weren't paid in April and are leavers.  Some employees would be highlighted just in April - meaning they weren't paid in March and are joiners.  Employees with no highlighting appear on both lists and are neither joiners nor leavers.

TOTW index
Next post - TRIM and CLEAN

This blog is brought to you by the
Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.