How to Remove Duplicate Rows in Excel Table

This post will deal with imposters!

No, I mean table duplicate entries. We will be able to manage them, remove them, tame them according to our needs and desires by the end of this discussion. So let’s dive in!

Removing duplicate rows from a table or from a range is simple. If table or range data is collected from different sources, there maybe duplicate items. Most of the time, you want to remove the duplicates. In the past, removing duplicate data was a manual task. But now Excel provides some handy tools that make it easier to locate and delete duplicate values from a table or from a range. In a small or a range, it is easy to identify the duplicate values, but in a table that has thousands of rows, it will take huge time and effort to locate the duplicate values and then delete them.

Read More: How to Find, Highlight & Remove Duplicates in Excel

We will now see how you can locate the duplicate values using the conditional formatting and then you can delete them manually. Or you can delete the duplicate values in one go using Remove Duplicates dialog box.

To highlight the duplicates in a table or in a range, at first select the whole Table or whole range. To select a table, just click any cell in the table and then press CTRL-A twice. To select a range, click any cell in the range, and press just CTRL A. Now I click on the Conditional Formatting drop down, then Highlight Cells Rules, then “Duplicate Values”. Duplicate Values dialog box appears.

Finding duplicates using conditional formatting in Excel

You see the preview of highlighted duplicate values in the table with “Light Red fill with Dark Red Text”.

Duplicate values dialog box, Excel table

And you can change how the duplicates will be highlighted in the table.

Duplicate values dialog box, custom formatting

There are six options here in which you can format the duplicates. Even you can use your own formatting to highlight the duplicates in the table clicking on the “Custom Format” option.

Read More: Data clean-up techniques in Excel

Now you can delete the duplicates manually from the table or the range. You can get all the duplicates at the top of your table, just click on a Filter Button, select Sort by Cell Colour.

Sort by color in Excel

Same thing you can apply to a range, but as the range has no filter button, right click on any duplicate cell that has a different color in the range, go to Sort, select “Put Selected Cell Colour on Top” option from the list. The duplicates are at the first of your range. This is how you can select the duplicates in a table or a range and then delete the duplicates.

Sort dialog box, sorting with color

In another way, you can delete the duplicate rows directly from a table or a range. This is an efficient way to delete duplicate rows from a table or a range. But it deletes the duplicates directly; it does not give you any chance to see which rows are going to be deleted.

Read More: Finding out the number of duplicate rows using COUNTIF formula

Select any cell in the table, click on the Design contextual tab, and click on Remove Duplicates control in the Ribbon. Remove Duplicates dialog box appears.

Remove duplicates dialog box, Excel table

All the Columns are already selected. When all the columns are selected, then those rows will be deleted that have the same contents cell by cell.

Microsoft Excel is showing how many duplicates have been removed with a message

Microsoft Excel dialog box appears to confirm that 3 duplicate rows are deleted, 16 unique values remain.

Summary of how to remove duplicates from Excel tables

So this is how you take care of the duplicates in Excel tables as well as in normal excel ranges. That’s it for today folks!

Download working file


Kawser on EmailKawser on FacebookKawser on LinkedinKawser on TwitterKawser on Youtube

Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply.

Keep in mind this African proverb:

"If you want to go fast, go alone,
If you want to go far, go together."

Let's together explore Excel deeply!

We will be happy to hear your thoughts

      Leave a reply

      Solve the Math * Time limit is exhausted. Please reload CAPTCHA.