How to remove duplicates in Excel 2013

Jan 30, 2015 | Apps, Big Data, General Interest, Office 365, Productivity, Tips and Tricks

Very commonly you may be dealing with a spreadsheet of data, with many duplicates that you want to remove quickly. This happens often if you are dealing with mailing lists with repeated information (names, email addresses, zipcodes, etc.), an export of data from a system, or a report.

Instead of manually deleting duplicated data to trim down your spreadsheet, use the Remove Duplicates feature!

To demonstrate this feature, we have a simple two column spreadsheet.

You can see that the codes in Column A are duplicated, as well as some of the items in Column B. Note, however, that when codes are duplicated, not all the items are (e.g. the code for Strawberries is the same, but one is Strawberries – 1 pack and the other is Strawberries – 2 pack).

We’re going to quickly eliminate all rows that are duplicated in both columns.

  1. Select one of the cells in the spreadsheet.
  2. On the Ribbon, go to the Data tab.
  3. In the Data Tools group, click Remove Duplicates.
  4. Make sure both columns are selected and that My data has headers is selected.
  5. Click OK.

This will remove all the information that is duplicated in both columns. But notice that doing it this way means that some codes in Column A are still duplicated because the corresponding item in Column B differs.

Now let’s eliminate all rows that show duplicates in one column.

If you want to focus on only piece of data in a column, you can focus on removing duplicates in that column even if there is different data in other columns.

To do this, simply go through the preceding steps, but when you click Remove Duplicates, deselect any columns that you don’t to consider when removing duplicates.

We’ll follow this with our spreadsheet by deselecting Column A (Item).

You can now see there are now duplicated codes in Column A. Item Strawberries – 1 pack remains, but Strawberries – 2 pack has been removed since it came last in the list and had a duplicated code.

And that’s how easy Remove Duplicates is! This feature can save you a lot of time and headaches.

Recent Posts

Use Cases for Co-Managed IT Services

Use Cases for Co-Managed IT Services

Meeting the IT needs of a business using internal resources can be difficult and expensive. Meanwhile, outsourcing IT services may prompt questions about control and security. One of the ways the market has evolved is through Co-Managed IT Services, which provide a...

The Tempo of Cybersecurity

The Tempo of Cybersecurity

You are probably thinking to yourself, “oh great, another cybersecurity article”. I feel the same way sometimes, that I’ve become numb to the statistics, and I just want to go back to a time when we didn’t have to consider unscrupulous threat actors trying to take...

Co-Managed IT is NOT the Same as Managed IT

Co-Managed IT is NOT the Same as Managed IT

All too often, small business owners try to decide about IT investments without a complete understanding of the options available. One place where this confusion often shows up is when trying to understand the difference between co-managed IT services and managed IT...