Detect and Remove Duplicate values in Excel

Let’s come back to Microsoft Excel articles. We have previously written following articles in Microsoft Excel.
This time we are back with a minor but a important feature of Microsoft Excel. When we have hundreds of records in our sheet, there might be duplicate records too. Mainly when sheet is created using team work and merging multiple sheets.
Here we are going to simplify the process with two steps. First we will use a built-in tool to detect duplicate records and then take help of other tool that is useful to delete the duplicate records.
Lets have a look at below screenshot which is a file of master data of drivers on a transport company.

Remove Duplicate Value

Remove Duplicate Values
Detecting Duplicate Values

In above screen, we have highlighted few records which are duplicate, but while this file has more than 4000 rows, it will take lots of time to detect duplicate one by one. So, we will take help of Microsoft Excel’s tool called conditional formatting. This tool can format all records which have duplicate values with a separate fill and color which help us to filter or sort using color and identify separately.

Remove Duplicate Values in Excel
Detect Duplicate

In above screen, we have followed some steps.

  • First we selected the column to be matched for duplicate content. Here we have selected Name field.
  • Then from Home tab, we have selected Conditional Formatting command.
  • When complete list of Conditional Formatting command appeared, we hovered over Highlight Cells Rules and choose Duplicate Values from the bottom most option.

Now let’s see what we got here.

remove duplicate values in excel
Duplicate records highlighted with Live Preview –

After selecting the color combination from above dialogue, we have clicked OK in the dialogue screen which is not visible in above because of expanded dropdown values.

Now you can compare which was to be kept in case you want and rename that, else leave it as it is. If you sort your sheet with color, now you can see all duplicate values in one place. Now the another part of job, to delete the duplicate records.

Deleting Duplicate Records

Now, while we have identified and corrected the Duplicate records in sheet. Now the next step is to clear the unwanted duplicate records. Let’s have a look at below screen.

remove duplicate values in excel
Deleting Duplicate Records – Remove Duplicate Records in Excel

Now, in above screen, we clicked in Data Tab to list its command among which Remove Duplicates is one and required in this guide. We are already inside the sheet, so no need to select any other rows or columns. Now just click on Remove Duplicates button, this will produce a dialogue which will have all the column titles and few useful commands. One of them is My data has headers which will exclude header row from matching.

Now the most important and critical, select only required column which is to be validated and expected to be unique. I suggest, only one column which is set to be unique to check and click on OK button at the end of dialogue.

remove duplicate values in excel
Deleted confirmation with records count

Steps are finished, your data is now junk free, you managed to clean duplicate records within few clicks. First step mentioned in our article about detecting duplicate record is not related with removing duplicate values but we demonstrated here for better illustration and safety of your data. We also recommend you to delete data after copying it to a a separate sheet so that in case of future need, you can compare what is deleted and recover lost data.

Thanks for the reading. Do not forget to share your views.

You might also like More from author