Microsoft Office Excel is one of the most widely used tools by businesses and organizations. It makes maintaining data simpler, increases productivity and saves a lot of time. If you’re someone who has to work with a lot of data, you should know how to remove duplicates in Excel.
You would have multiple entries in the spreadsheet if many people have access to the same document. It can get quite annoying sometimes. So, you need to remove duplicates routinely if you don’t want any confusion later. There are many ways to remove duplicates in Excel. Here are some of the ways to remove duplicates in Microsoft Office.
Also Read: How to Insert the Degree Symbol in MS Word
How to Remove Duplicates in Excel
Method 1: Using Remove Duplicates Option on Data Tab
The built-in tool in excel seamlessly allows users to delete repeated entries in the spreadsheet.
Firstly, click on the cell from where you want to remove the duplicates and drag it to where you want to remove them. Now, go to the Data tab, look for the ‘Remove Duplicates’ option and click on that.
A dialog box would pop up on the screen. You can check for the duplicates and remove them accordingly.
NOTE: If the data on the spreadsheet consists of column headers, then click on the ‘My data has headers’ option then press OK.
The duplicate rows will be removed from the spreadsheet and a dialog box would appear on the screen. The dialog box would confirm, how many duplicate values are found and removed from the data. You can refer to the picture for clarification.
Finally, you can rest assured as the duplicates are being successfully removed from the spreadsheet. Now, let’s check the next method.
Method 2: Using the Advanced Filter Option
Using the advanced option, users can churn out duplicate values as well as take notes of the unique values.
As we did before, click on the cell from where you want to remove the duplicates and drag it to where you want to remove them.
Look for the advanced filter option. Now, go to the Data tab and from then the ‘Sort & Filter’ section and click on Advanced. A dialog box would appear on the screen with a list of options for advanced filtering.
Next, click on the ‘Copy to another location’ option. That would copy the unique values to another location.
Now, check the range of your records in the ‘List Range’ field and see if it is the same range, you have specified earlier.
Make sure to enter the range within the ‘Copy to’ field using the unique values. After that don’t forget to check the ‘Unique records only’ box then click OK. You’re all done.
Now, removing duplicates would be much easier. You can follow any of the two methods to remove duplicates from excel.
Method 3: Using the Power Query Tool to Remove Duplicates in Excel
You can also use the power query tool to remove duplicates in Excel. It’s quite easier than you think.
Go to the Data Tab and from there go to the ‘Get and Transform data’ section. From there go to the ‘From Table/Range’ option
After that, you would see a dialog box on the screen where you have to create a power query table. Look at the image below. You need to specify the range of values correctly and press OK.
Now, the Power Query editor window would appear on the screen. You can either remove duplicates based on one or more columns or the entire table.
If you want to remove duplicates based on one or more columns then you need to right-click on the specific column header. Select the number of columns and remove duplicates accordingly.
For the entire table, click on the button present on the top left corner of the data preview and click on the ‘Remove Duplicates’ option. At last, click on the ‘Close & Load’ option, the dataset without any duplicate values would appear on the screen.
Method 4: Locate and Remove Duplicate Values Using a Formula
Lastly, there is yet another way in which you can remove duplicate values using formula. In this method, we will be combing the columns using a simple Excel formula in order to find their count. Later on, all the duplicate values will be removed whose count comes out to be greater than 1.
Combing columns A, B, and C using the concatenation operator ‘&,’ we get A2&B2&C2.
All the entries are made within cell D2 as the formula is entered in each row.
Bear in mind, we need another column namely ‘Count’ which is responsible for finding out the duplicates within Column D.
Here we will be using the COUNTIF function on cell E2. The formula is as follows: COUNTIF($D$2:D2,D2)
Using this formula, you can keep track of the total no. of occurrences for each value within Column D.
If the value of the count remains ‘1,’ this ensures that the data is unique. For values with 2 or more, it is important to use the Filter option on the count column.
Simply head over to the DATA tab > Sort & Filter selection > Filter
Choose filter at the top of Column E and select ‘1’ for all the unique values while removing the duplicates.
Click OK and all the duplicate values will be removed right from the table.
Also Read: 3 Best Laptops Under $500
Conclusion
Following the step-by-step guide, you can easily remove duplicates in Excel. Duplicate values in the dataset can be a problem sometimes. It can mess up the whole dataset and jeopardize your work. Tell us about your preferred method in the comment section.
