Excel - Removing Duplicate Values

There are multiple ways of highlighting or removing duplicates from an Excel worksheet. 

Conditional Formatting

Conditional formatting can be used to highlight duplicates ready for manual removal. This will use any formatting you select to overlay the data and show you any duplicates. However, it's worth noting that individual data values may be duplicates without the whole row being a duplicate. For example two persons with the same first name, but a different surname would be highlighted by this option. But it still has various uses for removing duplicates out of a single column.  

Utilising conditional formatting

To make use of conditional formatting you must first select the range (area) of data you wish to apply the conditional formatting on. This can be a column or row, or an entire table of data. There are some selection techniques that will speed up the process of selecting the right area available here

Once the range has been selected, select 'conditional formatting' from the home tab in the ribbon, then select "create new rule": Illustrative image

Then you will be able to select "Format only unique or duplicate values" before choosing what "format" you would like to apply:  Illustrative image

At this point, you will want to choose what formatting you'd like to apply to your duplicates using the "Format" button:  Illustrative image

To remove the duplicate values once displayed will require you to manually delete each duplicated value, therefore for a large spreadsheet, the option below may be preferable.

Remove Duplicates

There are two ways to access the "Remove duplicates" button,

The first can be found in the data tab of the ribbon:  Illustrative image

This requires you to first select the area/range of data that you wish to apply this on, before clicking the button. You must then choose which columns you wish to check for duplicates. You may select multiple columns of data to cross-reference the duplicates between multiple columns of data. Therefore working around the issue of duplicate values in the same column that are not a duplicate row. In my example data, those with the same first name, but different surname would not be removed.

This will remove the duplicates immediately once you select the columns and press 'ok', and will only tell you how many it has removed and not which values have been removed:  Illustrative image

Remove Duplicates from the Table Tools menu

The second way to "Remove duplicates" is to turn your data array into a table. You can then access the "table tools" at the top of the ribbon if a cell within the table is selected and select the button from here: Illustrative image



Still need help?