Have you ever found yourself in a position where you have many duplicates or copied data in Excel that you don’t recall copying? Well, this occurs to everyone at some point, especially to those who have newly started to work with Microsoft Excel. And locating these duplicated data and deleting them from the spreadsheet consumes a lot of time. However, there is always a solution to any difficulty, just as following these strategies might assist you in locating and removing duplicates. Are you wondering what is the solution to the question – how to remove duplicates in excel ? Then read through this post to get a stepwise solution.
Remove Duplicates
One of the most typical jobs we have to undertake while working on Excel projects is to eliminate duplicates. Before doing anything, we always clear the duplicate data more regularly and offer unique values as input to a variety of additional activities, sheets, drop-down lists of data validation, or even for building unique aggregates.
Remove Duplicates in Excel is a tool that helps you to get rid of the duplicate cells in one or more columns. It is a simple process to put into action. Utilizing the built-in tool of Excel, we may remove the duplicates easily. Also, the command of Remove Duplicate found in the Data Tab or a Formula to remove duplicates can be used. Even certain shortcut keys can help remove duplicates in Excel.
There are three ways to locate the duplicate files and then delete them:
- Utilizing Excel’s Conditional Formatting.
- Utilizing Excel’s Remove Duplicate function.
- Lastly, utilizing the COUNTIF function.
Instructions for Removing
These are the basic steps that one can follow while using an excel sheet. Users can use conditional formatting, Excel’s Remove Duplicate function, or the COUNTIF function to remove duplicate data.
Steps for using the conditional formatting:
- Take the table with duplicate data occurrences.
- Now select a set of cells or a table. In the Excel Ribbon, go to the Home Tab.
- Select Conditional Formatting from the drop-down menu. Then the highlight cells rules and the duplicate values are selected.
- The values dialogue box will be duplicated. Choose whether you require emphasizing the unique options or the Duplicate options. As removal duplicate is to be done, so will select Duplicate option in this case.
- From the drop-down list, now select the color and font of the highlighted cells in the values, and then press OK.
Steps for using Excel Remove Duplicate function:
- Choose the range of cells or a table that contains duplicate values. Then from the Excel Ribbon, go to the Data Tab. You can there find the option of Remove Duplicates from the Data tool group.
- The dialogue box to Remove Duplicate will now appear. Select more or one column that contains the duplicate data to remove them. For example, you can select the ‘name’ and ‘code no’ columns from the table as both these columns have duplicate information and then click OK.
- The duplicate number values that could be discovered and removed, as well as the number’s remaining unique values, are shown in a message box. It will display the message again if there is no duplicate value. And if there are no duplicate values left, then click OK.
Two main aspects that you must remember while using this remove duplicate function of Excel are that:
- It’s a built-in function of MS-Data Excel’s tool.
- It permanently eliminates Excel duplicate data.
Steps for using COUNTIF Function:
- The COUNTIF function in Excel is known as a simple way that removes duplicate data. There will be a small change in equation writing as it relies on whether you want to discover the duplicate values with the initial occurrences or without them. The formula to find the duplicates is =COUNTIF(range, criteria)>1.
- First, highlight the duplicates. For example, let’s presume you have a column with names that you highlight in your spreadsheet. Then use the Countif function in Column B of the spreadsheet to identify the duplicates in the names list. This function displays the number of times each name had appeared in the current row. Now as the formula is given the Countif function in cell B2 will be as =COUNTIF (A$2:A2, A2)
- Now that the duplicates have been highlighted using the Excel Countif function in Column A, we will delete the rows that have a value greater than one. If you have a lot of duplicates, though, you might find it easier to utilize Excel Autofilter to eliminate all of them at once.
Summing Up
Data cleansing skills involve removing duplicates from any Excel spreadsheet. While you are dealing with a large amount of data and don’t have time to locate and eliminate duplicate entries manually, then Excel’s built-in tool can be very useful. So now that you know the process of how to remove duplicates in excel, make sure to make a practice of it so that you can easily apply them at the time of need.
