Duplicates in Excel are common issue. Removing duplicates in small spreadsheets is easy – it is just checking the data and removing unnecessary values. In case of large spreadsheets, such process will be very time consuming, but fortunately, Excel has a special option that allows you to do it with a few clicks.
Remove duplicates command
Command Remove duplicates can be found in the Data tab on the Ribbon:
The following list contains information about the cities and branch codes of some company. Unfortunately, some branches have been entered more than once:
To clean up the spreadsheet of unnecessary duplicates, I will use the Remove duplicates tool.
First, select the area from which you want to remove duplicates – in this case I want to remove duplicate cities and branch codes, so I select columns A and B:
Then, click Remove duplicates from the Data tab and you can see, that the dialog box opens:
In this window, you can choose if duplicates will be removed from both columns. For this example, I want to remove duplicates from columns A and B so I don’t make any changes and click OK.
After a while, Excel will remove duplicate values and display a message with confirmation how many duplicates have been found and removed:
However, what if I selected only one column in the example above, e.g. column A? After selecting column A and choosing Remove duplicates command, a dialog box would open, but the options to choose from will be slightly different than before:
Excel automatically recognized that there is column B, next to column A, which also contains data. The above options allow you to specify whether duplicate values in the B column also should be removed:
- expand the selection – after choosing this option, Excel will also remove duplicates from column B;
- continue with the current selection – duplicates will be removed only from the selected area – in this case column A.