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":
Then you will be able to select "Format only unique or duplicate values" before choosing what "format" you would like to apply:
At this point, you will want to choose what formatting you'd like to apply to your duplicates using the "Format" button:
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:
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:
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:
