Contents
This article will show you how to avoid duplicates in spreadsheets using Excel’s built-in tools. This solution is relevant for Excel 2013, 2010 and 2007. It will take you just a few minutes to set it up.
Working with large spreadsheets in Excel, you are constantly adding new data. This could be a list of invoices for this month or a list of new customers this year.
And now the table is already larger than the monitor screen, and your eyes cannot cover it completely. You got rid of duplicates in it and made sure that all records in the rows are unique by manually checking the table for a long time. Of course, you don’t want to spoil such beauty, but every day you need to enter more and more new data, and you again and again have to return to checking for duplicates.
Do you want the data to be automatically checked for duplicates right at the time of their entry? As soon as you enter any duplicate values and press the key Enter, the editable field will immediately be highlighted (for example, it will change the background color or text color), thus notifying you that this column already has a completely identical value (as shown in the screenshot below). Keep reading this article to find out how to do it quickly and easily.
How to dynamically highlight duplicates?
As an example, let’s take a small sign for 6 clients. We have their names, phone numbers and email addresses. In real life, we are usually talking about thousands of customers and dozens of data fields.
We will add a dynamic check for duplicate values in the email and phone columns. As soon as you enter a phone number that is already in some cell above, it will automatically change its format.
Note: Some columns of the table (for example, gender), on the contrary, should contain only repetitions (male, female, in some countries there is also a 3rd gender), and this set of values is defined. You can enter such values by selecting them from the drop-down list, which saves time and helps to avoid typos (for example, “female” or “female” instead of “female”). Do you want to know how to do it? – Read the articles Dropdown list in Excel and How to create linked dropdown lists in Excel.
Tip: If you need to automatically compare the entered data with a column from another table (for example, blacklist phone numbers), read the article How to compare two columns in Excel and highlight duplicates.
1. Prepare a spreadsheet
If your list is a regular range, then you will not see the tab Table Tools (Working with tables) after clicking on any record.
First, you need to create a full table: select any cell with data and click Ctrl + T. Check that Excel correctly recognizes table headers and borders when you see the dialog box Create Tables (Creating a table). Make sure the checkbox next to the option is checked My table has headers (Table with headers) and click OK.
By converting a regular range to a table, your list will be automatically formatted, formulas copied to new lines, and so on. Here you can get acquainted with all the advantages of Excel spreadsheets, as well as learn how to create and use them.
2. Select all cells in the column you want to check for unique values
To do this, move the mouse cursor over the column heading so that it turns into a black down arrow and click the left mouse button.
3. Apply conditional formatting to the column
Click the Home (Home) and click on the icon Conditional formatting (Conditional formatting).
In the menu that appears, select Highlight Cells Rules (Cell selection rules) > Duplicates Values (Duplicate values).
4. Set Format for Cells with Repeats
Choose one of the formatting options from the list Values with (Values from) dialog box Duplicate Values (Duplicate values) and press OKto apply formatting.
In addition, the drop down list Values with (Values with) You can select an item Custom format (Custom format) and customize the font color and size, fill or border as you wish. For example, bold font and bright yellow fill.
5. That’s it!
Now, when entering a phone number that already exists in the list, both cells will be highlighted in bright color and draw attention to themselves.
