Remove Duplicates
The Remove Duplicates button is a quick and easy way to remove duplicates in a data series. It is a simple matter of highlighting all relevant cells and pushing the ‘Remove Duplicates’ button within the Data ribbon.
Another useful capability of this button is to identify unique cells within a range of cells so the duplicate removal can be tailored to your requirements.
This is demonstrated below in two examples:
1. Removing duplicates in 1 column
2. Removing duplicates over 2 columns
Remove Duplicates Based On 1 Column
Select the data range and click on the Remove Duplicates button. The menu below will pop up that populates the column headings. Where your data has headings, click on ‘My data has headers’ and this will exclude the header from your data range.
For example, by ticking only the ‘Asset’ column, the duplicates within column A will be removed. In the example below, rows 3, 4, 5, 7 and 9 will all be deleted because they are duplicates.
Remove Duplicates Based On 2 (Or More) Columns
In some instances, a duplicate may be defined over a number of columns. In the example above, although rows 2 to 5 are all BTC, there are 3 different unique numbers next to each BTC. Under this condition, the only duplicate above in both ‘Asset’ and ‘Number’ is BTC and 1. In order to filter over more than one column, simply select more columns in the Remove Duplicate menu in the image above.
There you have it, a quick tip on removing duplicate data.
Try this for yourself next time you are dealing with large data sets and want to quickly remove duplicates.
You may also be interested in Why Is Trim Not Removing Spaces In Excel?
