How to Remove Duplicates in Excel Quickly

When compiling mailing lists, merging databases, or importing data from online forms, duplicate entries are inevitable. Having the same customer listed three times can lead to embarrassing double-emails or inaccurate sales data. Fortunately, Microsoft Excel has a dedicated tool that hunts down and deletes duplicate rows in seconds.

Before You Begin: Backup Your Data

Removing duplicates permanently deletes data from your spreadsheet. Before running this tool, it is highly recommended to duplicate your worksheet (right-click the sheet tab at the bottom and select “Move or Copy”) just in case you make a mistake.

How to Use the “Remove Duplicates” Tool

Excel’s built-in tool allows you to specify exactly what counts as a duplicate.

1. Click anywhere inside your dataset. (If your data is formatted as a Table, it will automatically select the whole table. If not, press Ctrl + A to select all your data).
2. Go to the Data tab on the top ribbon.
3. In the “Data Tools” group, click the Remove Duplicates button.
4. A dialog box will appear showing a list of all your column headers (e.g., First Name, Last Name, Email, Phone Number).
5. Ensure the box My data has headers is checked in the top right corner.

Choosing Which Columns to Check

This is the most critical step. You must tell Excel how strict to be when looking for matches.

• Total Duplicates (All Checked): If you leave all columns checked, Excel will only delete a row if every single piece of data in that row is identical to another row. This is the safest option.
• Specific Duplicates (One Column Checked): Imagine you want to ensure no one is on your mailing list twice. If you only check the “Email Address” column, Excel will delete any row that has a matching email, even if the names or phone numbers are different.

Once you have checked the appropriate columns, click OK.

Reviewing the Results

After clicking OK, Excel will instantly delete the duplicate rows. A small pop-up box will appear giving you a summary, such as: “5 duplicate values found and removed; 150 unique values remain.”

If the number of deleted rows seems too high, press Ctrl + Z immediately to undo the action, and try again with different column settings.

Alternative: How to Highlight Duplicates Without Deleting Them

If you don’t want to delete the data automatically and would prefer to review the duplicates with your own eyes first, use Conditional Formatting.

1. Highlight the column you want to check (e.g., Column C for Email Addresses).
2. Go to the Home tab.
3. Click Conditional Formatting > Highlight Cells Rules > Duplicate Values.
4. Click OK.

Excel will instantly highlight all identical entries in red, allowing you to manually decide which ones to keep and which to delete.