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.

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 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.

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.

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!

Read More: Excel Formula to Automatically Remove Duplicates (3 Quick Methods)

Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy:

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF