​Managing Duplicates (duplicate entries) In Excel

Managing duplicates in Excel

Duplicates can cause lots of headaches in Excel.

They can throw out your tallies or counts, and can cause the wrong information to be reported.

If you have the same person listed in your database two or more times, perhaps with the same customer ID or same name, but with two different residential addresses because they moved house, then which one is the current record?

An example of a duplicate entry. Here we have 2 addresses for the same person. Which record should we keep?

Figure 01: An example of a duplicate entry. Here we have 2 addresses for the same person. Which record should we keep?

The likelihood of this occurring increases the larger your database becomes and the more people use it.

It happens.

Excel provides 2 tools for handing this situation and cleaning up your database. First I’ll explain how to identify duplicate entries in your data then I’ll show you how to remove duplicates quickly.

It’s good to know how to deal with duplicates effectively.

Feature Download: We have little takeaway for you. Grab the free PDF cheat sheet of all the key points in this post. That way you have a printable reference so you won't have to keep coming back when you forget something!

1. How to identify duplicates

The first tool is one of the options on the Conditional Formatting feature.

As its name suggests, there are two components - a condition and a format.​

Conditional formatting allows you to check the value of a cell and if a certain condition is true then apply a particular cell format.

For example, 

If the value of a cell is greater than 50, format the cell with white text and a red fill.

When checking for duplicates the test is:

Does this cell have an identical twin?

So, here’s the process:

  1. Highlight the cells that you wish to check. (In a table, this is normally one particular column).
  2. On the Home tab, click the Conditional Formatting icon.
  3. Choose Highlight Cell Rules.
  4. Choose Duplicate Values.
  5. Conditional Formatting icon

    Figure 01: Conditional Formatting icon

  6. Select how you would like to format any duplicate cells found.
  7. Choose how you would like to format any duplicate cells found

    Figure 02: Choose how you would like to format any duplicate cells found

Once this is done, any cell that has another cell with an identical value is highlighted.

Duplicate cells are highlighted

Figure 03: Duplicate cells are highlighted

2. How to remove duplicates

To remove duplicates from a single column:

  1. Select the cell range (or the entire column if that’s easier)
  2. Click the Data tab.
  3. Select the Remove Duplicates icon.
  4. Remove Duplicates

    Figure 04: Remove Duplicates

  5. Tick the box if your data has a header row, otherwise leave unticked.
  6. Confirm that you wish to remove the duplicates

    Figure 05: Confirm that you wish to remove the duplicates

  7. Click OK.

3. How to group duplicates together

Highlighting and removing duplicates is easy for a single column of data, but it’s very rare that you have just one column. Normally, that data is part of a larger table and you need to remove the entire duplicate row.

On a larger table the first duplicate cell might be on row 5 and the next one on row 105 and the next one on row 505​.

So is there an easy way to collect them all together in one place?

Good question. I’m glad you asked.

Yes, there is.

Excel allows you to sort by colour.

Here’s how:

  1. Highlight your duplicate cells (using conditional formatting) as described above. Here’s an example, where a duplicate Customer ID has been identified.
  2. Conditional Formatting applied to the Customer ID column

    Figure 06: Conditional Formatting applied to the Customer ID column

  3. Next, click any cell in your table.
  4. Select the Data tab.
  5. Click the large Sort button (not the little AZ or ZA icons)
  6. In the Sort By drop-down list, select the column that contains the highlighted duplicates.
  7. Set Sort On to Cell Color.
  8. Set the Order to the colour you used to highlight the duplicate cells.
  9. Sort the list by the Cell Color for the column that contains duplicates

    Figure 07: Sort the list by the Cell Color for the column that contains duplicates

  10. Click OK.

The duplicate rows are now all grouped together at the top of the table. From here you can choose which rows to remove and delete them manually.

Duplicate values are grouped together at the top of the table

Figure 08: Duplicate values are grouped together at the top of the table

Note: When you have several duplicate items, they may not be paired together even though all they are all listed at the top of the table. To rectify this you can add a second sort level that sorts the data by value, smallest to largest.

4. How to check 2 or more columns for duplicates

Consider this example. Two columns have been selected and checked for duplicate values.

How can you check 2 columns in combination for duplicates?

Figure 09: How can you check 2 columns in combination for duplicates?

As you can see Bloggs has been highlighted 3 times - twice for Fred and once for Del.

Also, Smith has been flagged as duplicate, even though one is Joe and one is Fred.

And finally, Fred has been highlighted 3 times - twice for Bloggs and once for Smith.

The only 2 entries that should be flagged here are Fred Bloggs.

To get around this, we can join first name and last name into a single full name using CONCATENATE. In cell D3, write the formula:

=CONCATENATE(B3, " ", C3)

The CONCATENATE function joins together all the items listed inside the brackets. As you can see, the first name and last name have been separated by a space.

Now, simply check the Full Name column for duplicate values.

First name and last name are joined together using CONCATENATE

Figure 10: First name and last name are joined together using CONCATENATE

I hope this has opened up your eyes and given you a few good ideas.

​Don't forget, you can download a free cheat sheet with all the key points from this post.

Please share your thoughts and feedback in the comments below, and if you found this post useful, please share a link on your social networks.

Cheers. Here's to your learning and success. Enjoy the rest of your day.

Jason Morrell blog signature

Jason Morrell photo and bio

Cheat Sheet - How to handle duplicates in Excel

FREE CHEAT SHEET

All the key points in one handy PDF

PLUS, GET A FREE COPY OF MY 30 EXCEL POWER TIPS

>

FREE Cheat Sheet: How to manage duplicates in Excel

x