MVP #37: Removing Duplicate Entries in Excel and Applying Conditional Formatting

Occasionally, you’ll have to work with an Excel table/database that has duplicate entries that you want to get rid off. 

Excel has a built-in tool for that.

In the sample data I'll use, I typed in 16 original/unique entries (actually pizza types) and copied them across 4984 rows. So I have 16 unique entries but 5000 total entries. So let's see how Excel will sort this out for us and delete all duplicate entries leaving the unique 16 entries.

Just select the entire table and goto Data menu, Remove Duplicates.

And voila! Excel has discovered that we have just 16 unique entries. It has removed the 4984 duplicate entries.

So let's move on to conditional formatting. I don't want to look lazy by giving you a 12 line tutorial.

Conditional formatting is great for:

•Highlighting duplicates,

•Highlighting values above a specified value,

•Marking cells based on their values, and

•Showing trends in a dashboard report

Select the data you want to apply the conditional formatting to.

Goto Home menu, Conditional Formatting

The options are self explanatory. You will be amazed by the out-of-the-box analysis/visualization they provide you.

Don't forget to register for the free Excel training coming up soon. I have been getting expert advice from fellow MVPs from US and Australia, Zack & Mynda, on how to give you a great training experience. The registration link is  http://www.eventbrite.com/e/indepth-online-excel-training-by-the-only-microsoft-excel-mvp-in-africa-tickets-11555369421