MVP #12: How to highlight and remove duplicates in Excel

, , No Comments
Hello there!
Welcome to my 12th post in the MVP series, and today I'm going to show you how to highlight duplicate entries in a Microsoft Excel table and delete the duplicates (leaving only unique entries).

So, let's start.

I have put together a sample table for this demonstration.

You will notice that several companies are repeated. 
How about I show you how to highlight all the companies occurring more than once in the list? Here's how.

Highlight the Column A (Companies) 

and click on Conditional Formatting in the menu bar, Highlight Cells Rules, Duplicate Values...

Here's what you get.

All the companies occurring more than once are highlighted.

Next step is to delete the duplicate entries, leaving one unique entry per company.
Select the entire table. Go to Data in the menu bar, Remove Duplicates.

In the dialog box that pops up, read through and make the settings correlates with how your table is setup (header or no header). Click on OK.

And voila! Done! You've removed all duplicate entries, leaving only unique entries in the table.

You'll notice that highlights are gone too. Conditional formatting is very cool, it detects that the duplicate entries are gone and only unique entries are left.

Congratulations! You've just done a fairly advanced data manipulation in Excel.
If you've got any question, hit me up via the comment box.


Post a Comment

You can be sure of a response, a very relevant one too!

Click on Subscribe by Email just down below the comment box so you'll be notified of my response.