How to Find and Remove Duplicates
in Excel (or Merge them)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

As an Excel spreadsheet grows, you start finding a host of new issues to deal with.

Especially if it gets updated regularly, and even more so if it gets updated by multiple people.

One of the issues you’ll probably come across is duplicate values.

Finding and getting rid of duplicate cells is easy in a small spreadsheet—it’s just a matter of looking through your data and copying and pasting a few cells.

But this process can become significantly more time-consuming in a large spreadsheet.

Kasper Langmann, Co-founder of Spreadsheeto

You could use the data filter to go through each value individually, but that could take hours.

Fortunately, Excel has some great tools for finding and dealing with duplicates.

Let’s take a look.

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Finding duplicates the easy way

Excel has built-in tools for finding duplicates, and that makes the process very easy. It will even highlight duplicates for you.

Let’s try that out.

Grab the example worksheet below so you can try it yourself!

Get your FREE exercise file

Before you start:

To help you learn how to find and manage duplicate values, we’ve put together an example workbook.

Just hit the button below to download it so you can follow along.

Download the FREE Exercise File

Download exercise file
Download free exercise file

Open up the example workbook, and take a look at the first sheet. You’ll see that it has a number of names next to employee numbers.

Unfortunately, something went wrong in our example HR system, and some people have been put on the list twice with two different employee numbers.

We’ll use conditional highlighting to find them.

Kasper Langmann, Co-founder of Spreadsheeto

Highlighting duplicate values

First, select all the cells in the Name and Employee Number columns.

Make sure the Home tab is open in the Ribbon, and click Conditional Formatting > Highlight Cells Rules > Duplicate values

Highlighting duplicate cells in Excel

A new window will appear.

In the drop-down below the text Format cells that contain: select Duplicate

Selecting conditional formatting

The final drop-down menu gives you many highlighting options. Just for the sake of doing something different, we’ll select Yellow Fill with Dark Yellow Text.

Finally, click OK.

If you scroll through the list, you’ll see that any cell that matches another cell exactly has been highlighted in yellow.

Highlighted duplicate cells

You’ll also notice that none of the employee numbers have been highlighted. Each of these is unique.

Some of the duplicates are far apart, and that’s not convenient. Let’s filter the unique names out so we only see the duplicates.

To do this, simply right-click on a cell that’s been highlighted, and select Filter > Filter by Selected Cell’s Color.

Filter by selected cell's color from context menu

You can also do this from the Data tab.

Start by clicking Filter, which will add the filter arrows to the top cells in each column.

Click on the arrow in the Name column, and select Filter by Color. Then choose the dark yellow color.

Filter by cell color from Data tab

Now, you’ll see only the highlighted cells, and you can deal with them how you wish.

We’ll look at a couple strategies for cleaning up duplicate values in a moment.

Excel cells filtered by color

Removing duplicates

The above method is nice because it highlights duplicate values, so you can come back to your spreadsheet and find them again quickly.

If you’d like to get rid of duplicates without highlighting, you can do that, too.

As an example, we’ll get rid of the duplicated names from the Full Name column in our example spreadsheet.

If you’ve filtered the column by color, clear the filter now so all the cells are visible.

To get started, select the list in which you’d like to remove duplicate entries. We’ll select the Full Name column.

Alternatively, you can simply select a cell in a table to filter everything in its column.

Kasper Langmann, Co-founder of Spreadsheeto

Then, open the Advanced Filter dialog by clicking Advanced in the Sort & Filter portion of the Data tab.

Excel advanced filter button

You’ll see a new dialog.

The List Range: box may contain a larger range than you initially selected (you can see that cells in column B were also included). If you don’t want to copy the entire table, you’ll need to change the selection.

In this case, if you want to only select column A, you’ll need to change the List range: from $A$1:$B$26 to $A$1:$A$26.

Excel advanced filter menu

Choose either Filter the list, in-place, or Copy to another location.

We recommend copying the list to a new location, because you’re deleting data here—and if you want to get it back, it’s going to be rather difficult.

Click into the Copy to: box, and specify a new location. For our purposes, we’ll simply enter D1.

Copying advanced filter to another location

Make sure that the Unique records only option is checked.

Then, just hit OK, and you’ll get the de-duped list in its new location.

Keep in mind that this removes all duplicates. If you want to keep one value that’s been duplicated many times, you’ll need to use a different method.

Kasper Langmann, Co-founder of Spreadsheeto

Consolidating duplicate values

If you have duplicate values, there’s a chance you’ll want to combine them in some way or another.

Excel’s consolidation feature can help you do this (or any of these 3 methods).

The consolidate menu gives you lots of options for the final value after consolidation; we’ll just look at average here, but there are other useful options you can use. Skim the menu to check it out.

Kasper Langmann, Co-founder of Spreadsheeto

On the second and third sheets in our sample workbook, you’ll see that we have a list of companies and their stock values. On the second sheet, those values are from May. On the third, they’re from June.

Let’s find the average value from those two dates.

First, open the Stock Values May sheet, click into cell D1, and type “May/June Stock Average” so you don’t forget what these values are.

Adding context to consolidated range

Next, in the Data tab of the Ribbon, click Consolidate (it’s in the Data Tools section). That will bring up a new window.

Make sure the Function: drop-down is set to Average (you’ll notice that there are lots of options; spend some time exploring these).

Then, click into the Reference: box, and click and drag to select all the stock values in column B.

Excel consolidate dialog

Click Add next to the All references: box. Then click into the Reference: box again, delete the contents, and select the stock values from June (you’ll need to click over to the next sheet). Click Add when you’re done.

This is what it should look like when you’re done:

Selecting consolidation ranges

Because we didn’t select the column labels, we’ll leave the Use labels in checkboxes unchecked.

Finally, click OK, and you’ll see the average stock values in column D of the Stock Values May sheet.

Wrapping things up…

Duplicate values can be a real pain—especially in very large spreadsheets.

But with conditional formatting, advanced filters, and consolidation, you can manage duplicates with ease!

2019-10-16T12:45:48+00:00