Know the simplest way to remove duplicates from Excel sheets

Microsoft Excel is one of the most used software in the corporate world. You can use this spreadsheet software made by Microsoft for Windows, macOS, Android, and iOS. It has calculation and computation capabilities, graphic tools, pivot tables, and a macro programming language called Visual Basic for Applications (VBA).  It is highly preferred in the offices because it provides a system to identify trends. It helps in organizing and sorting the data into meaningful categories. Despite this, it still has some challenges.  If you are new to Excel, it can feel complicated. One of the queries people ask is how to remove duplicates from Excel?’ The process involves simple steps. We are going to learn how to remove duplicates in excel.

How to remove duplicates from excel

You can use several methods to remove duplicates from your excel sheet. We are going to look at the simplest method of all. Microsoft Excel provides a tool to delete repeated content. In this method, we will see how to use this tool. Let’s get started.

Step 1- You need to select the range from which you want to remove the duplicates. Excel has the option to select the range of cells by clicking and dragging across the rows and columns you want to include. 

Step 2- You need to locate the ‘remove duplicates’ option. Go to the Data tab and select the Data tools section. There you will see the option for deleting duplicates. Click on it.

Step 3- A dialog box will open. It will tell you to choose the column you want to check and remove duplicates. In the top-right corner, you will see a check box for ‘My data has headers.’ Click on it if your data contains headers. From the below list, select columns containing duplicates. Click on ‘Ok.’

Step 4- The Excel system will identify and delete duplicates. It will again open a dialog box showing the number of deleted duplicates. 

That’s it. That’s how you remove duplicates from an Excel sheet. Let’s see how we can use advanced filters to remove duplicates.

How to remove duplicates with advanced filters

The selection of duplicates has two methods. You can either filter the unique ones or look for duplicates directly. They have a slight difference. When you filter and select the unique value content, it temporarily hides the duplicate ones. The removing duplicates option will permanently delete them.

A slight change in name or phrase can change the identification of duplicates to unique. That’s why it is necessary to check thoroughly before removing duplicates permanently. Use the filter to identify duplicates in the sheet. 

Using a filter for the unique value

Let’s see how to use the filter to identify unique value content on the sheet.

Step 1– Select the range of cells from the table.

Step 2– Locate the advanced filter option in the Sort & Filter group. For this, you will have to go to the Data tab. Locate the Sort & Filter section and select advanced. 

Step 3- A pop-up box will appear. You will find a bunch of options there. If you want to filter the range of cells or tables, click on ‘Filter the list.’ If you want to move the content to another location, click on ‘Copy to another location.’ Enter the cell reference in the ‘Copy to’ section. Check the ‘Unique records only’ box and click on ‘OK.’

Deleting duplicates with formulas

There are a couple of formulas that you can use to remove duplicates. You must combine the column with these formulas and find the count. It will be easy to filter out the duplicates.

Let’s take an example for columns A, B, and C. If you have to combine these columns using the concatenation operator ‘&,’ then the formula would be 

=A2&B2&C2

Enter this formula in cell D and copy it throughout the rows from which you want to remove duplicates. Now, we need a new column called ‘count’ to identify the duplicates. Use the COUNTIF function on cell E2. Now the formula would be 

=COUNTIF($D$2:D2,D2)

This formula will filter and put the occurrence number duplicates in the count column. Now, put a filter on the Count column as mentioned above. Click on the filter at the top of Column E. From the drop box, select ‘1’ to keep the unique value and remove duplicates. 

Click on ‘Ok,’ and it will remove the duplicates on the sheet. That’s how you can remove duplicates from Excel sheets by using formulas.  

Key takeaways

• Removing duplicates from Excel may seem like the most complex thing, but it becomes easier once you understand the process.
• Always copy the content elsewhere before proceeding with the deletion of duplicates. You may mistakenly delete the important and required data as well. That’s why it is necessary to take a backup.
• Make sure to type the formulas right. 

Hope you find this information useful. Write us your comments. Click here to read more such articles. 

Liked this blog? Read next: Work-life balance | Insider tips on finding the perfect harmony!

FAQs

Q1. What is the shortcut to finding duplicates in Excel?

Answer: You can use a short key Alt + H + L to find the duplicates in the Excel sheet.

Q2. Why can’t Excel remove duplicates from multiple rows?

Answer: The remove duplicates command only works on a single row or column. 

Q3. What is the short key to duplicating the data?

Answer: You can use Ctrl + D to duplicate the data from the cell you have selected.