How to Remove Duplicate Rows in Excel Table

Get FREE Advanced Excel Exercises with Solutions!

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 may be 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 a huge time and effort to locate the duplicate values and then delete them.

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 the 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”. A duplicate Values dialog box appears.

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

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

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 by clicking on the “Custom Format” option.

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.

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.

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.

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.