Microsoft Excel is a widely used application in almost every field. However, it is confusing for beginners or people who seldom use this application. Especially removing duplicates while working on massive databases. It is very usual to have copies when several people have access. Thus, the wider the horizon greater the possibility of problematic and unidentified data.
Following is an illustration of removing duplicates in Excel using an employee datasheet.
Unnecessary data that is duplicated complex things. For instance, if there are multiple entries of bonuses provided to the employees, it messes up the annual chart. Thus, it makes decision-making tricky. However, it is better to find it than highlight the duplicates. To tackle such a situation, Excel has built-in tools that can help.
Following a step-by-step process to remove duplicates from Excel:
Click on any cell or a range in the sheet. When you click on a single cell, Excel automatically determines the range. Hence you don’t have to do it manually.
Find the ‘REMOVE DUPLICATES’ option and select it.
DATA Tab –> Data Tools Section –> Remove Duplicates
As soon as you click on remove duplicates, a dialogue box appears. You can check the boxes for the columns you want comparison and removal of duplicate data.
Furthermore, if your data comprises column headers, select the ‘my data has headers’ option and press OK. Once you check the header option, the first row will not be evaluated.
You can also use filters to attain the objective to have unique values. However, many prefer to use only duplicates. Which eventually serves the same purpose. However, there is a slight yet critical difference. While you opt for the filtration, the duplicate values get hidden. Whereas if you delete duplicates, it gets rid of them instantly.
It is essential to understand the comparison briefly. Firstly, the value relies on what appears on the cell rather than the value contained. For instance, two cells have the exact date but in different formats. They are considered two unique values. Therefore, to get good results, check briefly and adjust cells manually.
Following is the process to filter unique values:
Data tab → Sort & Filter section → Advanced.
a) Click the option named ‘Copy to another location’.
b) In the “Copy to” box, enter a cell reference where the resultant values must be copied.
c) You can temporarily click the “Collapse Dialog” option ( ) to hide the popup window. Then, once you have selected a cell on the worksheet, click the “Expand” option ().
d) Check the ‘Unique records only’ option box and then click OK.
Opting for the Advanced Filter Option
This approach can be achieved by following a simple example below.
In this method, you will involve the columns using Excel formulas to get the exact count. The next step would be to exclude the duplicate values.
With the help of Power Query in Excel, you will be able to import data in Excel from different sources. It will also help in removing duplicates and transforming your data properly.