How to compare two columns to find duplicates in Excel

In this tutorial I will show you how to compare two columns in Excel so that the values that appear in both columns become highlighted and you can do something with those duplicate values.

Find duplicate values in two columns

1. Click on the Column Header to highlight Column A.

Highlight column A by clicking the column header
Highlight column A by clicking the column header

2. Select the Conditional Formatting button on the Home tab.

Select the Conditional Formatting button under the Home tab
Select the Conditional Formatting button under the Home tab

3. Select the New Rule option from the Conditional Formatting button drop-down list.

Select New Rule from the Conditional Formatting drop-down list
Select New Rule from the Conditional Formatting drop-down list

4. Select ‘Use a formula to determine which cells to format‘ as a rule in the New Formatting Rule window.

Select the 'Use a formula to determine which cells to format' option
Select the ‘Use a formula to determine which cells to format’ option

5. Proceed by entering the following formula in the ‘Format values where this formula is true‘ box:

=countif($B:$B, $A1)

Enter the formula provided in the text box
Enter the formula provided in the text box

6. Click on the Format button and specify the format you want to set.

Click on the Format button
Click on the Format button

It is possible to set a different cell color for duplicate values by clicking the Fill tab and selecting a background color.

Change the cell color by clicking on the Fill tab and selecting a color
Change the cell color by clicking on the Fill tab and selecting a color

7. After having specified the format you want click OK.

You will be redirected back to the New Formatting Rule window and will be given a preview of the formatting you have selected. If you are happy with the formatting you have specified click OK.

The conditional formatting rule then will be applied to all values of the column and should look like this.

Preview of workbook after Conditional Formatting has been set
Preview of workbook after Conditional Formatting has been set

Note how the values of the highlighted cells in Column A are repeated in Column B. I have manually highlighted the cells with identical values in Column B green for demonstrative purposes.

Demonstration of how repeated values are highlighted
Demonstration of how repeated values are highlighted

So this is how you can easily see which values are duplicate in different columns in Excel.

Let me know in the comments if I can answer any of your questions!

  • Olumide Tijani

    Great Tip. Let us assume we have value 32423 repeated in cell A9, your formula above will also highlight cell A9.
    What can I do for cell A9 not to be highlighted.

  • Yogendra Yogi

    Wow, You Rock..
    Formula works like a charm..
    Thank you very very much..

  • aero

    After following these steps it doesn’t seem to be working in Excel 2010, i get an error message stating that the formula contains an error. I have two columns as well. Column C has 327 entries, column B has 13272 entries. I have to highlight all the matching entries in both columns C and column B.

  • Allyson

    I nearly admitted defeat, but then I found this. Thank you, from the bottom of my heart!!!

  • Anas

    Thanks a lot !!!

  • Ram Sahu


    • Erin Dalton

      I’m sure there’s a smarter way to do this, but my first instinct is the follow the following process:

      I would split column A into a few different columns. first:
      1. insert a few blank columns (3 or so) in between column A & B.
      2. Highlight column A, then select “Text to columns” on the data ribbon
      3. select “[” as the data delimiter, then select “finish.”
      4. Result will be:

      Column A: full name, (h.s.c.), EXP, etc. (We’ll fix this in a second)
      Column B: 1733-25/09/1995]
      5. Insert 4 blank columns between A & B again (to give column A space to split again)
      6. “Text to columns” with “comma” as data delimiter, then select “finish”
      7. Your result should be:
      Column A: Full Name
      Column B: (h.s.c.) or applicable
      Column C: EXP or applicable
      Column D: blank or unnecessary columns, and so on
      8. Delete the columns you don’t need, and label the rest accordingly
      9. Then, perform your duplicate matching, again, for your “new” full name column.

      I hope this helps!

  • Jawad Hussain

    it works perfectly when Column A has more entries the column B or both have same number of entries, but it didn’t work if column A has lesser entries. i don’t know why but it is happening with me in office 2010.

  • Juan Chasan

    doesn’t work

    • Lisa Beach

      Mine didn’t work either – only on some duplicates, not all. Wondering if it has to be sorted or something first…

  • If column A is first names and column B is last names how can I highlight duplicates? I can highlight duplicates in one column but can’t make a rule that only highlights where both columns are equal.

    Example, there are 86 Smiths and 41 Alberts. I don’t care to highlight all of those. I do want to see it highlighted if there are 2 Albert Smiths though.

    • write in column C:

      This will return TRUE for an albert smith. Then filter the column to only show true values. You can also use advanced conditional formattings too if you know how to use it.

  • Alonso Gutiérrez

    Isn’t that formula only counting the duplicates of the CELL A1 in the entire column B?, what about the rest of cells in A??

  • Shatarup Chowdhury

    Thanks bro… it works..

  • Imran Nazeer

    Oh no this is long process, you can do this in simple way there you can find duplicate in both the columns,

    1)select both the columns

    enjoy GUYS.

    • Tyler Hibbard

      This isn’t the same thing. The article talks about when an item in one column matches an item in another column. Your suggestion will simply highlight any cell whose value exists somewhere else in either of the two columns.

    • Openmindedwannabe

      That worked perfectly for what I needed…thanks!

  • Thanks dude you rock!

  • Khaleed Phagacy

    Thanks Bro…
    You’ve really helped me out.

  • Teresa Kearney

    super helpful. thanks!

  • Saharsh Kumar


  • Razab-sekh Giani

    hi there,

    I have a large list with tree names (19000 trees) versus a smaller list (600 trees). I In the large list there are repeats. I want to highlight the tree names in the large list based on all the names fro the small list. This would be the best solution for my taks. When i am using the provided option the cells that contain repeats in the large list (but are not listed in the small one) itself are being highlighted. Can you help on this, please?

    • In mine, although duplicates in the left hand list are all highlighted, they do also exist in the right hand list. I guess the answer is to strip out duplicates from both lists before you start.

  • Beth

    Hi, I am working on a spreadsheet of past sales and we have a lot of duplicates. 5130 lines. Column A is Closing Name (address), Column B is Closed Deal Date, Column C is Actual Sales Price and so on until Column T.

    My question is this: Is there any way to merge duplicate lines and have information “win”? Like, If Column A, Lines 6 and 7 are the same, then I want Column I, row 6 to win if row 7 is blank? Does that make sense at all?

  • Santosh Bhadange

    I want following criteria for my excel it will really helps me if you give me solution for this

    In my excel in ‘column A’ I have numbers 1 – 3000 and in ‘columns B’ I am going to enter value randomly between same 1-3000

    Now condition: if value in column B matches with Column A it should highlight with colour (eg yellow ) BUT if same value in column B repeated again then it should show error or heigh light with diff. Color (eg Red)

    And I want to set this formula for Google sheets :)

    Please help me to find out the solution

  • Bibhu Mohanty

    Thank You Very Much
    Please help me further
    I need a formula takes unique values of column A(that means no similar values in column A) and highlight the cell it if a unique value exist in column B.
    A B
    11 84
    22 56
    33 11
    44 22
    55 22
    44 33
    66 44
    Like in the above example 11 and 33 got highlighted but not 22 and 44.
    here I have highlighted font colour but I need cell to be highlighed.
    Above formula ” =countif($B:$B, $A1) “”

    but it highlights 22 and 44.

  • Dave Worley

    I have 2 excel worksheets and I would like to highlight the ones (duplicates) in column A worksheet 1 that are also in column A (or any column) of worksheet 2, I tried vlookup but couldn’t get it to work. can you help?

  • Desirée Pienaar

    Thanks for this! Worked really well for what I needed. Unfortunately Excel did decide to freak out when I then tried to filter the data.
    Bypassed that problem by turning off the filters, and sorting by cell colour.

  • Cathy Poulos

    I have two columns of data – Column A is name and receipt number, column b is invoice amount. Where column A is the same, I want to review and locate duplicates in column B. I am looking for duplicate postings in the GL under the same customer. Any suggestions?

  • Geri Z


  • I’m at a total loss after spending hours looking. I have a spreadsheet with Columns A – M.
    There are 5097 rows. Several of the rows contain duplicate data across all columns (A-M).

    It’s a client list, so from A – M is First Name, Last Name, Address, City, State, Zip, Work Ph, Home Ph, Mobile Ph, Email, Birth Month, Birth Day, Birth Year

    I need to find each duplicate row matching across columns A-M and remove them or move them to another sheet.

    There are instances where a client is duplicated 8-15 times, but I can’t go through 5097 rows manually and try to remove them.

    Thanks so much in advance for any help!

  • Hitesher Gef

    Hi there, How do I run this as a macro?

  • Sharad Shukla

    Your formula is good & helpful . Just I want identical figure coloured on both side automatically. Is there a formula that can colour identical figure both side automatically that I searching for.

    Thank a lot it also helpful for me.

  • Mwema Kerich

    Thank you so much, you’ve saved me a lot of time and headache!!!

  • Jason

    getting a error – You may not use a reference operators (such as unions, intersections, and ranges) or array constants for Conditional Formatting criteria. What am I doing wrong?

  • Eva Scazzero

    I’ve hightlighted cells in column A that are duplicates of column B using conditional formatting -> =countif($B:$B, $A1). Now if I want to copy those highlighted cells from A into a new column C is there a shortcut for that?

  • Jeff

    How do I create a formula to search column “A” for X and if found, return the value in column “F”? And possibly, if not found, then leave blank?

  • Stephanie Aguilera

    This was so helpful! Thank you :D

  • Bader Al-methen

    thank you

  • SuperG

    Probably this will work for some. Easier for me than inputting formulas. Test it out with typing an real duplicate and you should see the highlighted numbers.

    “Example 4. Built-in rule to compare 2 lists and highlight duplicates or uniques
    If you are one of those power users who mostly rely on functions and formulas, you may have missed this amazingly simple way to compare two columns in Excel :)

    Select two lists you want to compare. If they include different numbers of cells or are located in non-adjacent columns, select the first list, press and hold the Ctrl key, and then select the second list.
    On the Home tab, go to Conditional Formatting > Highlight Cells Rules > Duplicate Values.
    Select either Duplicate or Unique from the left-hand side drop-down list, and choose the desired format from the right-hand side drop-down. If you are not happy with any of the predefined formats, click Custom format… and set the Font or Fill color to your liking.
    Click OK and you are done!”

  • Umar Mairamri

    I have two tables. Table A contains the ID Number and Names of my Staff. This table is from my office database and as a rule, each name begins with the Surname of the staff.

    The Staff of recent, went to the bank to open bank accounts. The bank sent me the names and account numbers of the staff in Excel (Table B). However, they did not include the ID Numbers of my staff.

    I know that I could easily add the ID Number of the Staff to Table B using the VLOOKUP formula or by creating a query in Microsoft Access that will join the ID number to the names.

    However, in this case, I have to first compare the Names in table A to those in table B to ensure that no staff is missing from table B.

    The problem I am having is that all the names in the column of names in table A are in this order: Surname-First Name-Middle Name. Some staff use only two names (no middle name) hence, in their case it is Surname-First Name.

    However, the names in the column of names in table B are not in any particular order. For some staff, it begins with their First Name and for some, it even begins with their middle name. Only a few actually begin with their surname.

    Also, some staff have two names in table A while they have three names in table B and vice versa.

    My question is: How can I compare the column of names in table A with that of table B to match the staff in table A that are in table B so that I could afterwards conveniently add the Staff ID to table B?

  • Ravee Siingh

    Thanks a lot. Its quite easy and solved my purpose

  • Naresh Kumar

    Good one..thanks

  • Raghav

    Quite useful! Thanks a lot. Do you also know how to get the count of these highlighted cells? One thing I can think of is a Sort by Color and manually see till which row number there are colored cells