Let’s come back to Microsoft Excel articles. We have previously written following articles in Microsoft Excel.
Introduction | Screen Components | Shortcuts | File Menu -1 | File Menu -2 | Keyboard Shortcuts | Text Functions -1 | Text Functions -2 | Text Functions -3 | Fixed Rows & Columns | Fixed Columns | Printing Headers | Formula Result Not showing | Remove Particular Word and this list is lot more long.
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 Values – Background
Highlighting Duplicate Records in Microsoft Excel – Conditional Formatting
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.
Steps for Applying Conditional Formatting to highlight Duplicate Values
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 – Remove Duplicates
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.
Remove Duplicates in Excel – Success Message
Steps are finished, your data is now junk free, you managed to clean duplicate records within few clicks.
Revision – What we done so far to remove Duplicates?
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 only 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. We share Microsoft Excel related videos on our YouTube channel. Do not forget to subscribe.
