I’ve recently posted two easy ways to compare two lists in Excel. One uses ISNA and MATCH and one IF and COUNTIF. I like both of these methods because they allow you to:
- Learn how to use and modify four powerful Excel functions
- Quickly apply AutoFilter to view just the missing values
- Easily manage and compare massive lists of data
For shorter lists, however, there is a much easier, non-formula way to compare two lists in Excel 2007 and later.
How to do it
- Arrange the lists in two columns with List A in column A and List B in column B.
- Highlight both lists.
Highlight the entire range of data. - Navigate to Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values…
- In the Duplicate Values dialog box, select “Duplicate” from the drop-down list to highlight values (in our example, names) found on both lists and click OK.
Apply conditional formatting to highlight duplicate values.
It doesn’t get much easier than that. If you’d rather Excel highlight the values missing from either list, select “Unique” from the drop-down list on the Duplicate Values dialog box (step 4 above).
Practice Yourself
The best way to learn is to practice yourself, so click the link below to download the Excel 2010 workbook used to show the methods described in this post. Please feel free to share!
One response to “Compare Two or More Lists in Excel with Conditional Formatting”
Hello sir, I’m Trying to enter Data in Sheet in form of numbers
for example:-
Roll-No:- 801
Maximum Marks
20
20
20
20
20
20
20
Sig. of Class Incharge
Roll-No:- 802
Maximum Marks
20
20
20
20
20
20
20
Sig. of Class Incharge
but the problem is when i try to drag or paste the same cells in another line…….Rollno has changed 814 instead of 802. My all cells are refrence cells of sheet1.
Now which formula is best during drag or paste the rows.If i paste the row after 14 Rows ..roll no show 815 instead of 802.
please guide me what should i do?
if u want i can send you the copy of Sheet.
Thanks & Regard
Gandhi