How to remove duplicates in Excel?

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:

Example

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.