Remove Duplicate Value
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.
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.
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.
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.
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.