How to automatically highlight duplicates in Excel once something has been typed

This article will show you how to avoid duplicates in your table columns using built-in Excel tools. The solution works for Excel 2016, 2013, 2010, and it will take you just a few minutes to apply it to your table.

You work with a large table in Excel, and you constantly add new data to the end. It can be a list of invoices for this month, or a list of your new customers this year.

The table doesn't fit the screen and your eyes can't take in the whole of it. You have already got rid of duplicates in it. You made sure that all row entries are unique by manually checking the table for a long time or by running Duplicate Remover add-in in 2 minutes. Of course, you don't want to spoil such beauty, but a lot of new data are entered every day and you have to check back duplicates over and over again.

Do you want to have the data automatically checked for duplicates right when you enter them? Once you type in a text box and press Enter, the box immediately stands out, changes the background color or the color of the text, thus warning you that this column has a box above with exactly the same data, the way you see it in the screenshot below. Read on to learn how to do it simply and quickly.

Highlight Excel duplicates dynamically

How to highlight duplicates dynamically

Let's take a small table for 6 clients as an example. We have customer IDs, names, phone numbers, and emails. In real life, there are usually dozens of fields and thousands of customers.

Our customer list in Excel

We will add a dynamic check for duplicates in the email & phone columns. As soon as you type the phone number that is already in a cell above, the cell will automatically change the format. It will offer you to check if it is a new customer and you accidentally made a mistake (or the client misspelt their data), or if it is an old client whose data you are trying to fill in again.

Note. Some columns of the table (for example, sex) can on the contrary contain only duplicates (male, female, maybe, the third (other) sex in some countries), but the set of values is defined. You can choose to enter data by choosing from the drop-down list options in these columns, it will save your time and help you avoid typos like "femal, fmale" instead of Female. Do you want to find out how to do it? - Welcome to our Blog next week.
Tip. if you need to automatically compare the input data to a column in another table, like a telephone number black list, see How to compare two columns in Excel and highlight duplicates.

1. Prepare your table

If your list is a usual range in Excel, then you won't see the "Table Tools" tab on the ribbon after clicking on any record.
Table Tools are displayed only if you have Excel table

First, you need to make a full-fledged Excel table of it: select any cell with the data and press Ctrl+T on your keyboard. Make sure Excel recognized your table headers correctly when you see the Create Table dialog box; you'll see the checkbox "My table has headers" selected in this case, and click Ok.
Transform your range into Excel table with headers

After reorganizing the data into a table, you'll get your list automatically formatted, the formulas will be auto copied to new rows, etc. You can see all the advantages of Excel table, and learn how to create and apply it here.

2. Select all cells in the column you want to be checked for unique values.

Simply bring your mouse cursor to the column header until it changes to an arrow down, and left-click on it.
Select the entire column with data

3. Apply Conditional format to your column

Next, open Home ribbon tab in Excel and click on the Conditional formatting icon.
Apply conditional formatting to the selected column

Select Highlight Cells Rules -> Duplicates Values… from the list you see.
Choose to apply special formatting for the duplicates

4. Set the format for the cells with the duplicates

Select one of the typical formats from the "values with" list in the "Duplicate Values" dialog box; click OK to apply formatting.
Choose how you want to have the dupes highlighted

You can also select "Custom format" in the standard "Format Cells" dialog and configure the font color and size, background color, border line and color the way you like them, e.g. bold font and bright-yellow background.
Define custom format for the cells

5. That's it!

Now as you enter a telephone number that is already in the list, both the old and the new cell will be highlighted with a bright color to attract your attention.
 Autohighlight duplicates in new entries

You may also be interested in

24 comments to "How to automatically highlight duplicates in Excel once something has been typed"

  1. Ahsan Ali says:

    How to check the duplication of text while entering in it own cell of excel ?
    I need help to identify the duplicate word/string/value in the current cell while writing text in it.

    For Example:
    I am writing the following words/tags in a cell A1.
    "Hydropower Project, Machine Hall, Oil Handling System, Clean Oil Tank, Dirty Oil Tank, Transformer Cavern, Flow Meter,"
    When I write "Handling System" to add another word as keyword in the same cell then it will automatically tells or highlight it, that its already been entered.

    can any body helps me out...

  2. Tamer helmy says:

    I work on sheet1 and create the date in sheet 2 automatic in table by formula(IF(D57"",IF(B57="",NOW(),B57),"")i need No repetition for date

  3. Vasanthi says:

    Hi There
    I have a little different story. I hope you will be able to help with this.
    In my spreadsheet, there is a button to transfer data from one sheet (sheet1) to another (sheet2). I want auto highlight duplicates when transfer data from sheet1 to sheet2.
    Happy to provide more information if needed and I would greatly appreciate if you can help me with this.
    Many thanks

  4. Vasanthi says:

    Hi Amit
    I have a little different story. I hope you will be able to help with this.
    In my spreadsheet, there is a button to transfer data from one sheet (sheet1) to another (sheet2). I want auto highlight duplicates when transfer data from sheet1 to sheet2.
    Happy to provide more information if needed and I would greatly appreciate if you can help me with this.
    Many thanks

  5. Lauren says:

    if there's a formula that copies the data on the row above it eg. (=+B2) for row B3, how do you make sure that there's only one entry in the same row to apply the rule you cannot be at different places at the same time?

    HOUR Location 1 Location 2 location 3 Location 4
    6:00 1
    6:30 1
    7:00 1
    7:30 1
    8:00 1
    8:30 1
    9:00 1


  6. Anup Shakya says:

    When I copy data from another sheet and paste in the sheet where the conditional formatting rules for duplicates have been kept, the copied sheet is not formatted with color even though it contains the duplicate values. What might be the reason?

    • Hello, Amit,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  7. Kerrie says:

    Is there a way of using conditional formatting to highlight when there is a fourth exact name match in a series of data? I just want to highlight any fourth occurrence of a persons name in the data.

  8. Alex Wilson says:

    I have a running total. I am using highlight duplicate to hide the running costs, by making the background and text colour the same.

    Unfortunately it also hides to first duplicate value in the column which I need to show.


    • Fazal Iqbal says:

      Click on "Conditional Formatting" => in Last Click on "Clear Rules then Press Clear rules from Entire Sheet or Selected Cell All background Colors in remove

      Try It...

  9. Darren says:

    I have a slightly different issue. I have a spreadsheet with 10 rows of data. there are 4 columns with drop down boxes that the user is asked to select a code. I am trying to identify if the user selects the same code more than once in one of 4 columns in the same row. Basically they should only enter the code once per record. I can use conditional formatting to check for duplicates on the one row. The problem is how to copy that formula to all rows. When I try to use format painter or even just copy formats, it doesn't work. It identifies duplicates up and down the column and ignores those on the same row.

    I could enter a conditional format for each line, as annoying as that would be, but the real challenge comes if the user needs to add rows to the spreadsheet. The new rows do not have the same conditional format and to ask them to add the formats on a row by row basis will not happen.

    I have not seen anyone discuss duplicates across a row and how to copy that down the columns.

    I appreciate any help you can provide.


  10. abhishek says:

    compare rows 100 and below to rows 1-99 (we're only looking in a Column say D) to see if there are any duplicates. now highlight any addresses in rows 100 and below (Column D) if they are already found in rows 1-99 (Column D). Then format Rows 100 and below (Column D) to ensure that the font matches that found in rows 1-99

  11. mike says:

    I am hoping I can do a variation of this. I would like to make a Fantasy football cheatsheet. Top 200 players in one col. and then several more cols containing players by position. So when somebody selects a player... I can highlight him in the top 200 and it will automatically highlight in the position columns as well. Is this possible?

  12. Wayne says:

    Is it possible to have this rule applied to two tables i.e.


    to be formatted if there are duplicates in


    i want to be able to add numbers to the second selection of cells and have the rule look for duplicates in the first selection and only highlight these cells not the cells i enter the numbers in as they have a seperate rule attached.

    Any help really appreciated.

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)