If you have duplicate values in your spreadsheet it is much faster to use conditional formatting to find them rather than scanning through the data yourself. To find and/or remove duplicates simply follow the steps outlined below.
Use Conditional Formatting to Find Duplicate Records
It is often useful to find duplicate values in a spreadsheet. It can help to improve our understanding of the data and is a great way to cross check data entry.
Conditional formatting can find and format duplicate values, allowing easy identification and analysis of the duplicate data.
- Select rows or cells you want to find duplicates values in
- Select Home (tab) on the ribbon
- Select Conditional Formatting (button)
- From the dropdown menu select Highlight Cells Rules | Duplicate Values
- In the Duplicate values window select how the duplicate values will appear from the dropdown menu
- Select OK (button)
- All duplicates within the selection will be formatted so they stand out
Tip: Use the Filter by Colour tool to view only duplicate records.
Removing Duplicate Records
An alternative to conditional formatting, the Remove Duplicates function will strip duplicate rows of data from your list, when it finds two or more complete rows of identical data. Where duplicate records have been found, the first of the duplicate records is retained, subsequent records are deleted.
- Select any cell in the data set
- Select Data(tab) on the ribbon
- Select Remove Duplicates (button)
- Remove ticks from any columns you don’t want to be included in the selection
- Select OK and all duplicate values are removed
Tip: Select Undo (Ctrl + Z)if the results seem unexpected! our list, when it finds two or more complete rows of identical data. Where duplicate records have been found, the first of the duplicate records is retained, subsequent records are deleted.