How to Compare Lists of Data and Find Differences in Excel

In: BlogDate: Nov 08, 2022By: Sal Agoro

Do you need to compare information in different lists? For example, you may need to find out which values exist in two lists and highlight differences.

There are several ways to compare two sheets or even workbooks in Excel. You can use functions like EXACT, IF, Lookup or Conditional Formatting to compare lists of data. In this blog, Happy's trainers Sal and Ebbie will use Conditional Formatting to highlight values in one list that do not appear in another list.

Hi, we are Happy

We are leading a movement to create happy, empowered and productive workplaces.

How can we help you and your people to find joy in at least 80% of your work?

More about Happy

Generally Conditional Formatting allows you to format cells that meet a given condition or criteria. But it can be used to highlight values in a list that do not appear in another, too:

 

 

To do this:

  1. Select the cells that you wish to apply Conditional Formatting to
  2. From the Home tab click on the Conditional Formatting Button
  3. Choose New Rule
    The New Formatting Rule (dialog box appears)
  4. Select use a formula to determine which cells to format
  5. Enter the formula =A3<>'Database 2'!A3
  6. Change the formatting of the cells (as required
  7. Click OK.

 

This scenario identifies some differences and highlights the cells.

 

If your sheets are in the same workbook you will need to show both sheets on the screen at the same time.

To show both sheets in same workbook:

  1. Click View Tab > New Window (opens same sheet a second time)
  2. Go back to View Tab > Arrange All > Select Vertical > Click OK

 

 

You can also have a scenario where the sheets are in different workbooks but you can’t use Conditional Formatting across workbooks. It will need a logical formula to find the differences.

Find out more on our Excel Level 4 course

Join us on our interactive and engaging Expert Excel Core Level 4 full-day course (split over two half-days) to learn how to build more complex calculations that will deepen your understanding and help you feel more confident with problem-solving in Excel.

Related blogs

Why not sign up to our newsletter?

Sign up to our monthly newsletter, full of tips, tricks and news to help you to be happier and more productive at work.

Sign up here

Learn More on Our Excel Core Level 4 Course

If you are experienced in Excel but need to start building more complex calculations this one-day course (split over two half days) will help you to deepen your understanding.

Our Excel Core Level 4 has been revamped for 2022 and onwards. The new outline covers VLOOKUP, HLOOKUP and XLOOKUP — and when you should use them. You will also learn how to compare lists of data for similarities and differences.

Find out more

Why learn online with Happy?

  • Interactive and engaging - just like our classroom sessions
  • Bitesize or full-day sessions - fit around your schedule
  • Learn from home - all you need is a quiet place to call from and an internet connection
  • IT Helpline - 2 years of free support after your course
  • No quibble money-back guarantee

Sal Agoro

Sal trains most MS Office packages. Her real love is in transferring knowledge by making her training sessions fun and more impactful. She is Happy’s Outlook Champion and ensures Outlook courses and training material are updated regularly with new features. Sal has been at Happy for over three years.

More by Sal