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

See also

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

  1. Mike Rempel says:

    Nice tip! Learned something useful today!

  2. Wayne says:

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

    =$D$5:$I$27

    to be formatted if there are duplicates in

    =$N$10:$T$31

    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.

  3. 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?

  4. mark says:

    Thanks for the info> :D

  5. 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

  6. 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.

    Darren

  7. pranita says:

    how to remove duplicate itemcode highlight in background color in a excel?

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

  8. Cathy says:

    How can I do a duplicate comparison for 2 spreadsheets?

  9. 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.

    Ideas?

  10. 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.
    Thanks!

  11. Asim says:

    Thanks Nice Sharing

  12. amit says:

    i was updating some 18 digit numbers in text format but it is showing as duplicate even though the it was unique nos.

    • 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 support@ablebits.com. 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.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite 2018.4 for Excel
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard