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

Cloud Use Cases for Small and Growing Businesses

Cloud Use Cases for Small and Growing Businesses

As you may have heard, the cloud is a network of remote servers hosted on the Internet. But for businesses the world over, the cloud represents the next step in technological evolution. The cloud unlocks speed, flexibility, scale, and cost savings that businesses of...

2023 Cybersecurity Trends for Small Business

2023 Cybersecurity Trends for Small Business

The threat of cybercrime continues to grow each year, and small businesses are particularly vulnerable. The latest cybersecurity trends indicate that organizations of all sizes must remain vigilant in order to protect their valuable data.  In this article, we'll cover...

Security in a Co-Managed IT Environment

Security in a Co-Managed IT Environment

Security in a co-managed IT environment looks a little different than in a traditional environment. Don’t be alarmed. When leveraging a partner to co-manage your environment, you’ll actually have better, more robust security in most cases than if you were to manage...

Five Signs you Need Better IT Support

Five Signs you Need Better IT Support

IT systems can make or break any business. They provide an avenue for communication and collaboration, help with organizational efficiency, and drive improved customer service. Without proper IT support, businesses are often left in despair and experience hindering...

Co-Managed IT Services for Financial Services

Co-Managed IT Services for Financial Services

Financial services companies are faced with the challenge of keeping up with technology because they cannot sacrifice data security or compliance. As a result, more organizations are turning to co-managed IT services as an option that provides a balance between cost...

Co-Managed IT Services for Oil and Gas

Co-Managed IT Services for Oil and Gas

The Oil and Gas industry is an ever-evolving sector that operates in a highly competitive global market. Companies must stay ahead of the competition to remain profitable, which means keeping up with the latest technologies and trends. One way to do this is with...