The Easiest Way to Remove Duplicate Data in Excel

Duplication in your data can lead to errors in calculations, statistics, or data visualization. Removing duplication is the first step in ensuring the integrity of your data.

Excel is one of the most popular and versatile spreadsheet applications in the world. Excel can be used for a variety of purposes, from data analysis and report generation to information management. However, one problem often faced by Excel users is duplicates in their data.

A duplicate is the same or similar data that appears more than once in one or more columns or rows. Duplicates can cause calculation errors, data inconsistencies, or decreased Excel performance.

To avoid those problems, you need to know how to remove duplicates in Excel easily and quickly. There are several ways you can use to remove duplicates in Excel, depending on your needs and preferences. In this article, we will discuss three main ways to remove duplicates in Excel, namely:

• Use the “Remove Duplicates” features available in Excel
• Use “COUNTIF” or “COUNTIFS” formulas to mark duplicates
• Use “Pivot Table” to filter out duplicates

Let’s look at each of them in more detail.

Using the Remove Duplicates Feature

The easiest and fastest way to remove duplicates in Excel is to use the “Remove Duplicates” feature available in Excel. This feature allows you to select the columns or rows that you want to remove duplicates, and then delete all the same or similar data in those columns or rows. The following are the steps to use the “Remove Duplicates” feature:

1. Select all the data you want to deduplicate. You can use the shortcut “Ctrl + A” to select all data in a worksheet, or click and drag the mouse to select a specific range of data.
2. Click the “Data” tab on the ribbon, then click the “Remove Duplicates” button in the “Data Tools” group.
3. In the “Remove Duplicates” dialog box, select the column or row that you want to remove duplicates. You can select more than one column or row by pressing the “Ctrl” key when clicking the column or row name. If you want to remove duplicates based on all columns or rows, check the “Select All” box.
4. Click the “OK” button to remove duplicates. Excel will display a message telling you how many duplicates have been removed and how much unique data is left.
5. Click the “OK” button again to close the dialog box.

For example, suppose you have the following data:

If you want to remove duplicates based on the Name column, then you can do as following steps:

1. Select all the data, and then click the “Remove Duplicates” button on the “Data” tab.
2. In the “Remove Duplicates” dialog box, check the Name column, then click the “OK” button.
3. Excel will display a message dialog box that there are 2 duplicates removed and 4 unique data remaining.
4. Click the “OK” button to close the dialog box.

Note that Excel only deletes rows that have the same value in the Name column, regardless of the value in the other columns. If you want to remove duplicates based on more than one column, for example, the Name and Age columns, then you can check both columns in the “Remove Duplicates” dialog box.

This feature “Remove Duplicates” is very useful if you want to permanently remove duplicates from your data. However, there are a few things you need to pay attention to when using this feature, namely:

• This feature will remove duplicates directly from your original data, without making copies or backups first. Therefore, we recommend that you make a copy of your data before using this feature, or use the “Undo (Ctrl + Z)” feature if you want to undelete duplicates.
• This feature can only remove duplicates that are the same, excluding duplicates that have differences in upper or lower case letters, spaces, or punctuation. For example, “Budi” and “budi” won’t be considered duplicates by this feature. If you want to remove duplicates that have these differences, you’ll need to take a few extra steps, such as using “UPPER”, “LOWER”, or “TRIM” formulas to equalize the format of your data before using “Remove Duplicates” feature.
• This feature can only remove duplicates in one worksheet, excluding duplicates existing in other worksheets in the same or different workbooks. If you want to remove duplicates existing in other worksheets, you need to copy or move those data to the same worksheet first or use other ways we will discuss below.