How To Remove Duplicates in MS Excel

Do you work with large MS Excel data files & have to check for duplicates? Do you often end up finding & removing duplicates in your MS Excel files manually? In that case, please read this entire article as we discuss how you can remove duplicates from MS Excel files smartly.

If you are reading this, to be honest, I am really not sure for which work line you are trying to find a solution a finance sheet, freight log, bank statement, performance sheet, expanses, etc. Hence, I have taken an example of a keyword research file.

If you are looking for a specific data set example, please feel free to drop me an email & I will try to create a new article page with that example.

Approach 1: Directly Removing Duplicates

It means that the file you are working on has some duplicate entries & your goal is to remove them directly without checking or verifying which entries are duplicates.

This approach enables you to remove duplicate entries from Excel in just a few clicks.

1: Selecting the data for removing duplicates

This is the most important part of the whole process, read carefully

-- If your data file has only one column it is a simple process to select the data

— However, if you have more that one columns it is important to select all the columns.(If you missed doing so the data will get stale, let’s take an example, Columns A, B & C have 100 entries each now if you select only column A and remove duplicates(say 20 duplicates were removed). Then column A would have less entries than B & C)

2: Select Data: From the Top Menu Bar

Referring to the below screenshot, go to the "Data" section in the top menu bar

-- Click on "Data"

— Click on “Remove Duplicates” in the Data section (as shown in the image below)

3: Selecting columns to check for duplicates

After clicking the "Remove Duplicate" option a popup will appear prompting you to select the columns that you'd like for Excel to check for duplicates.

-- Select (Check/uncheck) the columns on which you want to perform the action

4. Let's See the Result

-- Click on "Ok" in the last step & wait.

The duplicates get removed & a popup appears with the information of how many duplicates were removed.

5. Shortcut Keys

-- If you are a shortcut key lover like me use "Alt Key+A+M" after selecting the data for removing the duplicate by above method.

Approach 2: Removing Duplicates via Conditional Formatting

Unlike the above approach where we directly removed all the duplicates in one go. In this method, we highlight the duplicates first & then remove the duplicates.

This approach enables you to review duplicate entries & then remove them from excel file.