Remove Duplicates
The Remove Duplicates button is a quick and easy way to remove duplicates in a data series. Simply highlight all relevant cells and click the ‘Remove Duplicates’ button within the Data ribbon.
The keyboard shortcut to access the Remove Duplicates function on the Data tab in the Ribbon is Alt + A + M.
Another useful capability of this button is identifying 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
How to remove duplicates from a single column in Excel
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’, which will exclude the header from your data range.
For example, the duplicates within column A will be removed by ticking only the ‘Asset’ column. In the example below, rows 3, 4, 5, 7, and 9 will all be deleted because they are duplicates.
If you’ve just removed duplicates, you can immediately undo the action by pressing Ctrl + Z or by clicking the ‘Undo‘ button on the toolbar.
How to remove duplicates in 2 columns in Excel
In some instances, a duplicate may be defined over several columns. In the example above, although rows 2 to 5 are all BTC, there are 3 unique numbers next to each BTC.
Under this condition, the only duplicate above in both ‘Asset’ and ‘Number’ is BTC and 1.
To filter over more than one column, 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 deal with large data sets and want to remove duplicates quickly.
Video Guide
Frequently Asked Questions
Do I lose data when using the ‘Remove Duplicates’ feature?
Yes. When you remove duplicates, the extra duplicate entries will be deleted, leaving only one unique entry.
It’s always a good idea to backup or make a copy of your original data before using this feature.
After removing duplicates, how can I ensure that no duplicates remain in my data?
You can use conditional formatting to highlight duplicates.
Select the range, go to the ‘Home’ tab, choose ‘Conditional Formatting‘, then ‘Highlight Cells Rules‘, and then ‘Duplicate Values‘. Any duplicates will be highlighted.
Is there a formula or function to identify duplicates in Excel?
Yes, you can use the COUNTIF function.
For instance, you can use =COUNTIF($A$1:$A$10, A1) > 1 to find duplicates in column A. This formula will return TRUE if the value in A1 appears more than once in the range A1:A10.
