SubscribeEven Faster Way to Compare 2 Lists in Excel [Quick Tip]
Last week we discussed a fun and easy way to compare two lists of data in excel using conditional formatting. In that post, Artem commented,
The quickest way to find all about two lists is to select them both and them click on Conditional Formatting -> Highlight cells rules -> Duplicate Values (Excel 2007). The result is that it highlights in both lists the values that ARE the same. Then in one list non-highlighted are values that are not present in the second list, and opposite for the second list. I think it is sell “geeky”, but it gets job done very very quickly when you don’t want to mess around.
Artem must be an Excel Yoda. I somehow missed this beautiful and dead-simple way to compare lists in Excel. So here, I am documenting that technique so we all remember it and use it.
A Ridiculously easy and fun way to compare 2 lists
[works only Excel 2007+, use the above technique if you are on excel 2003 or earlier]
- Select cells in both lists (select first list, then hold CTRL key and then select the second)
- Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values
- Press ok.
- There is nothing do here. Go out and play!
See the screencast aside to see how this works (click here for a detailed demo).
Hats off to Artem for sharing this beautiful tip with us. Thank you 
Spare a minute to become superawesome at work – Read a Quick Tip.
| ||||
|
| ||||
|
Leave a Reply
 |
Range Lookup in Excel [Formulas] | Football anyone? [off-topic] |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
70 Responses to “Even Faster Way to Compare 2 Lists in Excel [Quick Tip]”
Thank you
I have very useful this formula.
Thanks again!
Nice trick but doesn’t work on 2003′
Great timing! I needed to do something like this just last night, and ended up doing it manually. I’d alpha-sorted two lists of names and I needed to put unique ones from one list into the other. This trick would have helped, but I’d still have had to move the lists to make room for the unique entries from one going to the other, which needed to be updated.
Any thoughts on a way to automate this merging, so that the unique items from one list can be inserted into the other?
@Philip,
Actually there is an idea: after you’re done with conditional formatting you can go in and sort the items by color. By default you will have unique items non-highlighted, so just copy filtered items into the other list to synchronize. It’s again quick and dirty. Macros generally would be much easier to work with.
This works great, except if there are duplicate names within each column. Excel will display these records as duplicates as well. Conditional formatting in Excel 2007 is a great tool!
So what do you do in this case? I experienced the same problem, I want to compare data from one list to another but not considering if they are duplicate within the same column… I only need unique (or duplicate) from column vs the other.
Use PivotTable to summarise data. Then you won’t have duplicate values on one column
any way to do this when comparing columns from different sheets in the same workbook?
I am trying to accomplish the same goal. I am able to do this with a formula and extra columns but not with the simple conditional formatting. It seems that once you click on two different sheets the conditional option is no longer available.
http://chandoo.org/wp/2010/05/19/new-features-in-excel-2010-conditional-formatting/
The very first addition to excel 2010.
Now that’s clever!
ALT + HLHD for highlighting duplicates
or
ALT + HLHDU for highlighting uniques
Useful if you are checking for data in both colums or rows but the previous method of highlighting duplicates is better for a row by row comparison. Either way is quicker than writing a function.
Hi! you can go here to know how to make conditional lists:
http://runakay.blogspot.com/2011/03/conditional-lists-on-excel.html
MASTER!! Super cool! Thank you very much.
Kind regards,
Anthony Martina
Saved my work by hours, very sweet trick, thanks for sharing!
Thank you so much for sharing! This is extremely useful.
THANKS FOR THIS INFORMATION, SAVED ALOT OF USEFUL TIME
You are the bomb! Thank you for sharing – it saved me a ton of time!!!
[...] Even faster way to compare lists [...]
you saved my day with this hint !!!! thank you very very much.
WOW!!!! Soooo Simple and Easy
Hats off to you(Chandoo.org) and Team Microsoft Office…….
This worked perfectly!!!! Thanks buddy!
After seeing this and playing with it a bit, I was happy to discover that I could use this method to select multiple columns to compare. (I’m using Excel 2010)
I had to find the rows in a second spreadsheet that were not in an original spreadsheet.
In the first spreadsheet I selected all the rows and changed the font to red.
Then I selected all the rows in the second spreadsheet, copied them and pasted them at the end of the rows in the first spreadsheet.
You should now have one spreadsheet with two different colored rows – red ones from original spreadsheet, black ones from second spreadsheet. (makes them easier to identify later)
Then I selected 4 columns to use for the comparison… that’s right, 4 columns: date, invoice#, product, qty.
Now from the Home tab, select Conditional Formatting, ‘New Rule’, ‘Format only unique or duplicate values’. Change Format all: to ‘unique’ Select a highlight color Click OK
I now had every row highlighted that was NOT in both files, regardless of which file it started in. Next was to sort the data by cell color to group all the ‘missing’ rows together. The color of the font also told me exactly which set of data had that row so that I could make sure I only added the right rows to the original spreadsheet.
Now I’m going to play with it some more and see if I can get this to work without having to merge the data into one spreadsheet.
Hats off to your great help, keep sharing and keep helping us
This option works. Really smart and quick way to compare two column in EXCEL
[...] Compare 2 lists quickly, Compare 2 lists – detailed [...]
Awesome trick.
Would you know if there is a limit this method can handle? I have to compare 175,000 lines of a 578,000 lines list (folder security report)…and the workseet totally freezes now, even if I close Excel then open the workbook again. It seems the conditional formatting is not a ‘one shot deal’, but always a work in progress.
Thanks,
You saved a lot of my time. Thanks a Ton buddy.
Here is another simple way to highlight differences between two lists. This approach works with lists that are each comprised of multiple columns.
Use the COUNTIFS formula to compare the corresponding columns in each list, and check if the total is greater than zero. If the total is non-zero, then the corresponding row is different between the two lists.
Example:
Worksheet1: Columns to compare: A, B, C, D
Worksheet2: Columns to compare: E, F, G, H
Assuming that columns A and E have similar data, columns B and F have similar data, etc.
Put the following formula into a cell on row 1 on worksheet2
“=countifs(WorkSheet1!$A:$A,Worksheet2!$E1, WorkSheet1!$B:$B,Worksheet2!$F1, WorkSheet1!$C:$C,Worksheet2!$G1, WorkSheet1!$D:$D,Worksheet2!$H1)=0″ will return TRUE if the values in row 1 on Worksheet2 are missing from Worksheet1.
You can set this formula in a conditional format rule to highlight the missing rows on Worksheet2, if desired.
-Sajan.
On a weekly basis I use two excel windows to copy data from one file to a pivot table file. However, I was not aware of the “side-by-side” button on the View ribbon. What I’ve been doing since Excel 97 days is to open both files, then go to Window, then Arrange, then select Vertical or Horizontal. In Excel 2010, the sequence is View, then Arrange All, then select Vertical or Horizontal.
I will be using the “side-by-side” button in the future. Thanks for the tip.
Very nice way to compare lists
Hi
Very useful information,
sofar i am doing this by using toggle key
Thanks of info
Ramachendir
awesome….is it possible same in excel 2003?
Hi Atul, Please check http://spreadsheetpage.com/index.php/tip/comparing_two_lists_with_conditional_formatting/. content in this link shows how to do the same in excel 2003.
I have been looking for hours for this and it’s so simply! THANK YOU! You saved me manually going through 4000 records
Thanks for sharing this tip. You saved me a lot of trouble!
great and thnx
I am using this brilliant method! However there are some items in my list across different columns that match exactly letter by letter but somehow is picked up and highlighted upon as ‘unique’??? Any help on this?
Imagine there are three columns. A few cells match in the first two, a few in the second and the last, and a few in the first and the last. This function would let me know all the duplicates, but would not tell me the duplicates across any of the two columns. How to rectify this issue. any thoughts?
O Hi ,,
Thanks for the cool tip.. I wanted to open a web page inside an excel cell
how could I do that
So multiple cells I can use different pages …IS their a way
Excellent one it really helped a lot
Nice one! I also really like the fast and neat keyboard shortcut version MiceMustDie sneaked in above:
___
ALT + HLHD for highlighting duplicates
or
ALT + HLHDU for highlighting uniques
___
For uniques, on my Vista I have to use ALT + HLHD, then U for highlighting uniques, ie without the ALT for the last keystroke.
OMG thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! I was too tired to understand anyone else’s directions on how to do this. you just made my night less painful.
Really helped, Thanks!
[...] PPS: If you just want to highlight the common values, see this. [...]
Thanks a lot for this; you opened a wall I was hitting every day….. thanks a looooooooooooooottttttttttt
I have two lists, one in column A with over 2000 records, and one in column B with fewer than 200 records.
When I select column A, and then CTRL select column B, and
Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values
Press ok.
ALL fields are highlighted in both columns–even the extra fields in Column A where there are no matches in column B.
WTFish? Did I misunderstand something?
UPDATE
That should read, “…column B with fewer than 2000 records.”
Can someone help me out
I am using this excellent idea but I need some customization in this. I want to highlight each duplicate value with different colours
For example my numbers data has three duplicates i.e. 15, 20, 25
I want to highlight 15 with blue, 20 with red and 25 with yellow
Gr8 help, thanks a lot for sharing…….
I think it is really cool but it also highlights the duplicates in the single list alone… it does not just do cross reference…it does duplicates within the single column… How do we prevent it from highlighting additional duplicates on single column that are not on second list?
Worked beautifully for what I needed. Thank you soooo much!
Nice explanation
this doesnt help much as it doesnt compare two list. it just finds out if the same number is repeated twice in ANY of the column and not in the second column. tried using randbetween() function in two columns. pls enlighten me if i m missing something. office 2003
oh, just read it again. works only in office 2007+. apologies
great! thaNK YOU IT HELPED
Ok WOW that was ridiculously easy.
Ty ty ty
Awesome stuff !!!! really appreciate it, now I can be awesome too !
YOU HAVE SAVED MY LIFE!!! THANK YOU!!!!!!!
damnnnnnnnn sooooo good.. saved me like 2 hours of work now
thanks mate!
Grt technique my friend..
This is one of the single most useful tricks I have ever seen shared. Thank you guys!!!!
Thank you! This just saved me a lot of time.
THANK YOU!!!!!!!
Very helpful, in stark contrast to the microsoft page, which just confused me
Give that Man a C Gar…!!!
That is an awesome tip..Thank you so much!!
very thank full to all i’m searched more over 100 sites but there is no easy answer to this finally i got it here. thank u so much
i need the same in ms office 2003 also will u please post me