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