Removing Duplicates in Excel. Today we will talk about how to remove duplicates in the Microsoft Excel spreadsheet. The version of Microsoft Excel that I am going to use in this article is Micorosft Excel 2019. Let us start.
In Order to remove the duplicates from the Excel spreadsheet, you first need to decide on what condition you would like to remove it. You may have various columns in your data. Let’s say you have 4 columns. Look at the data table underneath.
Conditions to Remove the Columns
- Remove the data if the First Name of both data records matches.
- Remove the data if the Last Name of both data records matches.
- Remove the data if the First Name, Last Name of both data records match.
- Remove the data if the First Name, Last Name, and Email Address of both data records match.
I hope you have understood the above-mentioned conditions. Thes conditioned are for only the above-mentioned data. You will have more or less number of columns in your data so you will have to decide accordingly. When removing the data, we will have to give a condition to the function. Based on the condition the function will remove the data. Let us do it now.
Removing Duplicates in Excel
Follow the instructions to remove duplicates from the spreadsheet.
- Select the data by pressing the Control + Shift + End shortcut key. or you can also press Control + Shift + * if you don’t have blank cells in your data. It will select the whole data.
- Click on Remove Duplicates Button on Data Tab.
- Remove Duplicates window will appear on the screen.
- Now is the point where you will have you give a condition to the Excel Function. The Remove Duplicates window will show you all the columns just like the above screenshot.
- Every Checkbox is representing a match. If we keep them all checked, then the condition will be if all the columns, First Name, Last Name, Full Name, and Email Address match, then the data will be removed otherwise no action.
- Keep all the checkboxes checked and press Okay.
- 4 rows were completely identical so they are removed now.
If you want to remove all those records have first and last name identical, then keep first and last name checkboxes checked.
- 6 Records have identical first and last names so they are now removed.
Removing Duplicates Using Advanced Filter
- Select the data by pressing Control + Shift + End.
- Click on the Advanced Filter icon under Data Tab.
- You will see Advanced Filter window.
- You will have two options to choose from. Filter the list, in-place, and Copy to another location.
- Filter the list, in-place – This option is to filter the data in the same place where the original data is placed.
- Copy to another location – This option is to copy filtered data at a different place but in the same sheet.
- List Range will be selected if you have already selected the data, or else you can click on the icon next to the List Range field and select the data.
- Make Sure Unique Records Only is checked and then press okay.
Copying Filtered Data on Some Other Place
If you want to place the filtered data in a different place on the same sheet then you can choose Copy to Another Location.
- Press the icon next to the Copy to field and select the cell where you want to place the filtered data.
- Make sure Unique Records Only is checked. Press okay. You will see The filtered data is at the place where you wanted.
I hope the article was helpful. Feel free to comment if you have any confusion. Stay Safe Stay Healthy.
Take Care!
Article Suggestion – How to Delete Blanks from Spread Sheet.
