Easily Removing Duplicates in Excel 2019

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.

Data

Conditions to Remove the Columns

  1. Remove the data if the First Name of both data records matches.
  2. Remove the data if the Last Name of both data records matches.
  3. Remove the data if the First Name, Last Name of both data records match.
  4. 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.
Advanced Filter
  • Remove Duplicates window will appear on the screen.
remove duplicates Window
  • 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.
removing duplicates in excel
  • 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.

removing duplicates in excel
removing duplicates in excel
  • 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.
removing duplicates in excel
  • You will see Advanced Filter window.
removing duplicates
  • 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.

removing duplicates
  • 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.

Leave A Reply

Your email address will not be published.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More