How to Compare Lists of Data and Find Differences in Excel
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?
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:
- Select the cells that you wish to apply Conditional Formatting to
- From the Home tab click on the Conditional Formatting Button
- Choose New Rule
The New Formatting Rule (dialog box appears)
- Select use a formula to determine which cells to format
- Enter the formula =A3<>'Database 2'!A3
- Change the formatting of the cells (as required
- Click OK.
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:
- Click View Tab > New Window (opens same sheet a second time)
- 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.
- How to Use Conditional Formatting in Microsoft Excel — Get started with Conditional Formatting in this two-minute video.
- How to Colour Code Your Data Validation Dropdown List in Excel — Adding colour to certain elements within a spreadsheet can create visual appeal and increase user accuracy when it comes down to interpreting your data. Find out how in this blog.
- How to Use the Excel Dynamic Array FILTER Function — What do you do if you want to look up multiple criteria that returns all match results, not just the first match? Then the new Excel Dynamic Array FILTER formula will come to the rescue!
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.
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.
Testimonials from happy customers
Ruby Alden-Gibson2 days ago
Really enjoyed the course. It was informative and I learnt loads!
Trusted Customer3 days ago
Very good, the process was easy and communication good. Mostly spoke to Laura so it would be good if all her emails had come from her name email address.
Trusted Customer3 days ago
Fully satisfied with the service and location of the training.
Trusted Customer6 days ago
Trent Grassian7 days ago
An incredible training, which was so engaging and informative. Really got a lot out of it and would highly recommend itt o others!
Joanne Spear7 days ago
thanks, the course was simple and easy to follow and i found the presenters approach very friendly.
Wilma Wheatley7 days ago
The tutor was great in including everyone. answered all the questions i had before attending
Trusted Customer7 days ago
Happy's training courses enhanced my knowledge and gave me new skills which I can use in my job to make things more efficient.
Jodie Moore8 days ago
Great course with a great tutor. Very interactive with good learning experiences.
Trusted Customer8 days ago
Great course, enjoyable and useful.
Jackie Costin8 days ago
Roumiana Grigorova8 days ago
I am very happy, taking this training. I would say I am one step more confident. This was an interesting and very useful experience. Our lecture was a great, I have to sa...