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

    Hi,
    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

    SIR HOW TO DETECT SAME TEXT IN ROWS OF TWO DIFFRENT COLUMNS. IF IN ONE COLUMN ONLY TEXT AND IN SECOND COLUMN TEXT WITH NUMBERS OF SYMBOLS.

    • 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:
      =AND(A1=”Albert”,B1=”Smith”)

      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
    2)HOME-CONDITIONAL FORMATTING-HI LIGHT CELL RULES-DUPLICATE VALUES.

    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.
    kudos….

  • Teresa Kearney

    super helpful. thanks!

  • Saharsh Kumar

    thanks..bro

  • 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

    Hi,
    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.
    Ex:
    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

    THANK YOU!

  • 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?