Skip to content

How to Remove Duplicates in Excel

This article will show you how to remove duplicate values in Excel.

What’s nice about Excel is that it has a built-in feature to remove duplicates for you. The only downside is that it isn’t intuitive to use at first.

If you want to learn how it all works, then keep on reading!

Remove Duplicates From a Single Column

To remove duplicates from one column of data, start by selecting any cell containing data.

Next, use the Excel Ribbon and go to Data Remove Duplicates.

The Remove Duplicates dialog box will appear, and Excel will highlight what it thinks is your range of data.

If it doesn’t highlight all your data, start over by selecting the entire data range instead, and then go to Data Remove Duplicates.

Go ahead and click on Ok.

Excel will go through the range and remove any duplicate values.

Take note that Excel considers empty cells too. If there is more than one, it will remove all empty cell duplicates.

Remove Duplicates in Two Columns

Take a look at the dataset below.

What happens if you highlight both columns of data and use the Remove Duplicates feature?

Excel claims that it can’t find any duplicates!

This doesn’t make sense because there are clearly duplicate colors and numbers. What’s going on?

If you take a look at the Remove Duplicates dialog box, you will notice that both the Color and Number columns are checked.

Because both are checked, Excel will consider the combination of both columns as a single list item when searching for duplicates. In other words, this is what excel is actually looking at.

Looking at this list, you can see why Excel claims that there are not any duplicates. None of these list items are the same.

What Happens if Only One Column is Checked?

Going back to the Remove Duplicates dialog box, what happens if you only check one column instead of both?

Excel looks through that column only and removes all rows in the selected range that contain duplicate values.

If you look closely at what’s left, you will notice that the color Yellow has disappeared.

That’s because Excel only found three unique values in the number column and deleted all the other rows of the selected range. All instances of Yellow just happened to be in rows that were deleted.

This is a big reason to be mindful when using the Remove Duplicates feature. You don’t want any data accidentally disappearing on you.

So How Do I Remove Duplicates in Both Columns Successfully?

To remove all duplicate values in the Number column and Color column, you must select these ranges independently (one at a time) when you use the Remove Duplicates feature.

Just keep in mind that the colors may not line up in the same rows as the numbers as they did before. The data might shift around.

So whichever method you choose to use when using the Remove Duplicates feature in Excel, just be mindful of how it works.

This Post Has One Comment

  1. I use the remove duplicates ribbon tool all the time, I never thought about using them on independent columns before! I didn’t know you could select what you want Excel to remove. This will be super helpful to know!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top